Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 346
  • Last Modified:

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

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
Soccerman55
Asked:
Soccerman55
1 Solution
 
LowfatspreadCommented:
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
 
Soccerman55Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now