Update with subquery runs in one statement but not in another on SQL 2005 64 bit EE
Posted on 2006-07-17
We are migrating to SQL 2005 64 bit edition. I ran one of our SPs & got the following error message:
OLE DB provider "SQLNCLI" for linked server "Rbcweb" returned message "Cannot create new connection because in manual or distributed transaction mode.".
Msg 7320, Level 16, State 2, Line 1
Cannot execute the query "SELECT TOP 1 1 FROM "heat"."heat"."Profile" "Tbl1005" WHERE CONVERT(int,"Tbl1005"."CustID",0) IS NULL" against OLE DB provider "SQLNCLI" for linked server "Rbcweb".
Here is the statement that is generating the error:
print '*** Update Inactive entries in the HeatCustomerDim table ***'
Set CurrentCustomerStatus = 'Inactive'
Where CurrentCustomerStatus = 'Active' and
HeatCustomerKey NOT IN (Select CustId From Rbcweb.heat.heat.Profile)
The linked server is setup correctly. The weird thing is that I do almost the exact same thing a few lines above this statement within the same SP & it works. I then do the same thing after this SP. Here is a statement that works:
print '*** Reactivate customer entries in the HeatCustomerDim table ***'
Set CurrentCustomerStatus = 'Active'
Where CurrentCustomerStatus = 'InActive' and
HeatCustomerKey IN (Select CustId From Rbcweb.heat.heat.Profile)
The only difference is this statment doesn't contain the NOT keyword. I haven't been able to find anything that has changed with the NOT keyword in SQL 2005. Has anyone else run into this problem so can see what I might be doing wrong?