[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
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
?
342 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

Tech or Treat!

Submit an article about your scariest tech experience—and the solution—and you’ll be automatically entered to win one of 4 fantastic tech gadgets.

Question has a verified solution.

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

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…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
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.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

649 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