Solved

Update with subquery runs in one statement but not in another on SQL 2005  64 bit EE

Posted on 2006-07-17
2
329 Views
Last Modified: 2008-01-09
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 ***'
Update HeatCustomerDim
Set CurrentCustomerStatus = 'Inactive'
From HeatCustomerDim
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 ***'
Update HeatCustomerDim
Set CurrentCustomerStatus = 'Active'
From HeatCustomerDim
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?

THanks!
John
0
Comment
Question by:Soccerman55
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 500 total points
ID: 17122924
is profile a view?
can you post its definition?

have you tried rewriting as

Update HeatCustomerDim
Set CurrentCustomerStatus = 'Inactive'
From HeatCustomerDim as H
Where CurrentCustomerStatus = 'Active'
 and not exists (Select x.CustId
                          From Rbcweb.heat.heat.Profile as  x
                        where x.custid=h.heatcustomerkey
                       )

0
 
LVL 1

Author Comment

by:Soccerman55
ID: 17235223
LowFatSpread,

Rewriting the update statement the way you suggested fixed the issue.  It is running successfully now.  I am still not sure why my did not work but as long as this works, it doesn't bother me too much.

Thanks again!
John
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

831 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question