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?

  • 1
    Do you want this to be a cartesian? – JNK Jun 7 '12 at 16:55
  • @JNK The result's always going to technically be a Cartesian, but it's always one row to one row due to the database design. It's more of a config file than a table... – Ben Brocka Jun 7 '12 at 17:01

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:

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.

  • I'm trying to keep it open to multiple rows, eventually I do have some other tables I need to catch data from. As for the 4 part names, I used to be running this via the live server to send to the dev server (the links aren't on the dev server). Must have forgot I don't need them anymore – Ben Brocka Jun 7 '12 at 18:19

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.