Solved

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

Posted on 2006-07-17
2
333 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 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

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

705 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