I've got Schrodinger's query here; you can run it once successfully, then 5 times in a row, and it works fine. Change anything in the query, even whitespace, run it again, and it might stop working the next 5 times. It seems something breaks when it's cached; it it doesn't run once, the same query won't run properly until it's changed. Sometimes the same exact query that run before won't run or vice versa.
The query is run against multiple remote servers (one at a time) so the problem seems to be related to that. They're linked servers and I can query them properly with a normal query. The servers are matching versions (SS 2005). If it matters I'm running this via SSMS 2008, though my automated script (PHP with SQLSRV driver) appears to be having the same problem.
Here's the simplest statement I could make that still gets the error:
UPDATE LocalServer.DB.dbo.table SET loc.[ChangeStatus] = rem.[ChangeStatus] FROM LocalServer.DB.dbo.table as loc, RemoteServer.DB.dbo.table as rem WHERE loc.LocationID = '1'
This basically updates data from the only row in the remote table with the matching row in my local table; it's a Cartesian join but technically I want that.
The error is:
The multi-part identifier "loc.ChangeStatus" could not be bound.
But I don't think the problem is in the SQL, something's getting cached wrong or something and I think it's forgetting my alias or some form of metadata necessary. But only sometimes.
Some of our remote servers are on satellite connects which go down or time out, but I can reproduce this on a DSL location that's 5 blocks away where it never times out. What's going wrong here and how can I fix it? What would cause the identifier to fail like that?