1

My Microsoft SQL Server has a linked server to another SQL Server. I have a simple query that runs on mine, deleting a remote table (truncate doesn't work) and inserting data to it.

This query works fine if it's run in SSMS. But I need it to be run on a schedule, so I add it in a dtsx as an EST. The problem is that when I run this dtsx, be it manually on SSDT or automatically scheduled on SQL Server Agent, it fails with the message:

could not find server '-----' in sys.servers`.

This is very odd. Of course the server is in sys.servers and the linked server is working. As I said, the exact same query works when run on SSMS. I can't even start to imagine what may be causing this inconsistency. Other linked servers work fine.

Can somebody guess what may be causing it and what I could look for a clue? Googling this error only points me to explaining how to config linked server, but it's already there!

Paul White
  • 94,921
  • 30
  • 437
  • 687
Hikari
  • 1,603
  • 12
  • 28
  • 42

2 Answers2

1

Could you share the linked object name along with the query? Sometimes the problem is on the FROM part. There are a lot of situations where you can get this error, and many of them are covered on StackOverflow. However, one case which might especially be encountered on shared servers is simply that the database name has a period (dot) in it. For example, if its name is mysite.com_DB. This will automatically cause the problem.

The solution, if you can't rename the database, is to encapsulate the DB name in square brackets, so for example:

mysite.com_DB.Table_name

Will turn into: 

[mysite.com_DB].Table_name
Rafael Cardoso
  • 11
  • 2
0

I wasn't able to find the cause of this issue. I tried with another MSSQL instance, and from there it worked.

Hikari
  • 1,603
  • 12
  • 28
  • 42