I must have spend about 2 hours on this one. I had a stored procedure that i was restructuring. I was changing from one database to another, but both on the same server. I got it to work in Data_1, but not Data_2. I THOUGHT it was the same code, but on Data_2 i kept getting an error that the sys.servers could not find Data_2. I could rebuild the process from the beginning, but as the logic got more complex, i would lost Data_2. Since i could make it work, it was not the problem, except that i wanted to find the cause. I tried to compare the two stored procedures in notepad++ with ‘move to other view’ on the second file tab, but i was not finding the problems. And with usp, you need to both EXECUTE the usp to save it, and then exec it to run it. Eventually, it would stop working and i was never confident of when it failed.
OK, so the answer is in the error. I can reference [myTable] or [dbo].[myTable] or [databaseName].[dbo].[myTable] and they all should work. What i can not do is this:
for a database on the same server, however i can do this:
Can you see where i am going? The logic was complex enough that i had resorted to some limited copy\paste at times, and i had accidentally moved code like this
which looks like a four-part name to the parser, which complained it could not find [databaseName] — as a SERVER.
I hope it doesn’t take me two hours to remember this next time.