Query intermittently fails with "The multi-part identifier could not be bound"

Question

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?

Answer 1

If it's just one row, how about:

UPDATE LocalServer.DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
) 
WHERE LocationID = '1';

Now you've eliminated the join and any aliases that have to be bound. Also why are you referencing both with 4-part names? If the local server is really the same instance, why not reference the local as DB.dbo.table instead of LocalServer.DB.dbo.table? If you take out the four-part name on the outer table, you can also do this for multi-row updates. It's not as pretty to do the aliasing this way, though:

UPDATE DB.dbo.table 
SET [ChangeStatus] = 
(
  SELECT [ChangeStatus] 
  FROM RemoteServer.DB.dbo.table
  WHERE key_col = DB.dbo.table.key_col
) 
WHERE LocationID = '1';

And as we discussed offline, if it's multiple rows and multiple columns, you should use something like this:

UPDATE t
SET [ChangeStatus] = r.[ChangeStatus],
    [OtherColumn]  = r.[OtherColumn]
FROM DB.dbo.table AS t
INNER JOIN RemoteServer.DB.dbo.table AS r
  ON t.key_col = r.key_col
WHERE t.LocationID = '1';

I believe that proper aliasing and eliminating the 4-part name should cause less work for the relevant parts of the execution process.

Answer 2

There is a bug in SSMS 2008 R2, when it is intermittently executing your script against a wrong database as discussed here. This bug caused us pain in the past. If this is the cause of your problem, it can be fixed by explicitly adding USE YourDatabase, or by using a more stable tool to execute important scripts.