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!