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!

  • Did you perhaps specify NULL to the locallogin property of the linked server so it is not viewable to the public role? Are you using the same user to connect to SSMS and SSDT? msdn.microsoft.com/en-us/library/ms178530(v=sql.130).aspx – Ali Razeghi Jun 2 '16 at 15:59
  • Where can I see the local login property? And yes, both SSMS and SSDT connect using an Active Directory account. Linked Server uses a MSSQL account. – Hikari Jun 3 '16 at 13:25
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
  • Thanks. That's not the case, that DB doesn't require [] to work, it doesn't have any dot, slash, space, etc. It's like AA987456GHT123 – Hikari Jun 3 '16 at 13:27
  • As I said, it's the same query on SSMS and SSDT. I suppose that, if it was anything in the query string, it would fail on SSMS too, as the case you pointed. I can't really find what it might be, because it's the same username logging in MSSQL, same linked server, everything is the same. Only thing different is SSMS and SSDT. Both opening dtsx on SSDT and executing from MSSQL Agent fail. – Hikari Jun 3 '16 at 13:29
  • Did you tried to use SQL Profiler to get the query? Please be aware that Visual Studio uses "sp_executesql" to execute the queries and that T-SQL contains some limitations related to "date". The best option is, using SQL Profiler, extract the query generated. You get exactly the code used on SQL engine. That is how I troubleshoot and solve my issues. – Rafael Cardoso Jun 27 '16 at 14:53
0

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

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.