Credit Where Due...Almost
So today I had the opportunity to discover how the new AT clause of the EXECUTE command in SQL Server 2005 works. The clause allows a statement to be executed 'at' a specified linked server. Pretty useful, because I couldn't connect to the server in question, but I could access it as a linked server.
There's a gotcha though, the AT clause only allows you to specify the name of a linked server, not the database in which the statement will run. As with most new features of SQL Server over the years, it's useful until you actually need to use it, then there's some exception to the rule that prevents you from using it in exactly the scenario that you need it most.
Of course, you can execute a 'use' statement remotely and this correctly sets the context, but if you need to recreate a stored procedure, no dice. A create procedure statement must be the first statement in a batch.
So I tried to actually set the Catalog of the linked server. After a painful stint of researching 'documentation' (which in the end consisted of using sp_helptext to read the relevant procs myself), I found that 1) in order to configure a linked server, you just add it again with the new properties (of course, why didn't I think of that), and 2) you can't set the property I wanted to set if your linked server happens to be a SQL Server anyway! Grrr....
So I wondered whether wrapping the whole thing in an explicit transaction would force the server to use the same connection and result in the effects of the database context switch from one execute statement to remain in effect for subsequent execute statements. Voila!
At the root of this problem is the almost unbelievable complexity of T-SQL as a language. And it's not because the problem space is actually that complicated, it's because the language has so many inconsistencies and special cases that no two features work together in the expected way. Over the years of my involvement with T-SQL the problem has only gotten worse as every new feature that is added brings its own set of special case scenarios, which is exactly what happened here.
So thanks for adding the ability to execute a statement on a linked server, but didn't anyone ask the question 'Which database is this going to run in?'
Bryn Rhodes
Alphora