?
Solved

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

Posted on 2006-07-17
2
Medium Priority
?
337 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 50

Accepted Solution

by:
Lowfatspread earned 2000 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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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 shrink a transaction log file down to a reasonable size.
Suggested Courses

752 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