Getting a strange error when running an update statement with a subquery

Question

Post reply

Getting a strange error when running an update statement with a subquery

laker_42

SSCarpal Tunnel

Points: 4785
More actions
July 14, 2006 at 11:21 am

#70914

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
July 17, 2006 at 8:00 am

This was removed by the editor as SPAM

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

Steven Bras SSC Veteran Points: 248 More actions · Answer 1

This article on Microsoft's support site might shed some light on the error:

http://support.microsoft.com/kb/272358/en-us

Hope this helps!

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 2

Steven,

Thanks for the reply!

I did see that article already. I am not quite sure how that applies to mine mainly because it works in the statements before & after using pretty much the same syntax. It also works fine in SQL 2000 & the article you referenced is for SQL 2000. So I am still puzzled why this is occurring.

Thanks!

John

Ward Pond SSC Eights! Points: 999 More actions · Answer 3

Note that Profile is a reserved word. You might want to try [Profile] instead.

Also, your UPDATE syntax will instantiate the table twice. Consider the following (omits the FROM):

Update HeatCustomerDim

Set CurrentCustomerStatus = 'Active'

Where CurrentCustomerStatus = 'InActive' and

HeatCustomerKey IN (Select CustId From Rbcweb.heat.heat.[Profile])

Hope this helps!

Take care..

- Ward Pond
blogs.technet.com/wardpond

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 4

Hi Ward,

Thanks for the suggestions but I tried them & no luck. I still receive the same error when I execute:

Update

HeatCustomerDim

Set

CurrentCustomerStatus = 'Inactive'

Where

CurrentCustomerStatus = 'Active' and

HeatCustomerKey

NOT IN (Select CustId From Rbcweb.heat.heat.[Profile])

and no errors when I execute:

Update HeatCustomerDim

Set CurrentCustomerStatus = 'Active'

Where CurrentCustomerStatus = 'InActive' and

HeatCustomerKey IN (Select CustId From Rbcweb.heat.heat.[Profile])

Still confused!

John

Ward Pond SSC Eights! Points: 999 More actions · Answer 5

Hi John..

Two other idea occur to me at the moment. I'll keep puzzling on this one.

Has SET TRUSTWORTHY ON been set in both databases? Is the state of SET ANSI_NULLS the same in both environments?

Take care..

- Ward Pond
blogs.technet.com/wardpond

Harveysburger SSCommitted Points: 1562 More actions · Answer 6

I am getting the exact same problem right now... My link servers are working for the most part, the only problem i have is when using the IN operator as follow...

where ABC not in (select ABC from server.db.dbo.table)

I have been using link servers with 2000 for years and never encounter such issue... The problem is happening when running a query from the 2005 box to the 2000 one

any idea? thanks!

Harveysburger SSCommitted Points: 1562 More actions · Answer 7

I rewrote the query in a way that is not crashing... look at this strange thing... The first statement is returning the below error message while the second statement runs fine...

OLE DB provider "SQLNCLI" for linked server "server2" returned message "Cannot create new connection because in manual or distributed transaction mode.".

Msg 7320, Level 16, State 2, Line 2

Cannot execute the query "SELECT TOP 1 1 FROM "server2"."dbo"."TABLE_B" "Tbl1005"" against OLE DB provider "SQLNCLI" for linked server "server2".

Statement 1:

select

count(*)

from

TABLE_A A

where

A.ColumnX not in (select ColumnX from server2.DB.dbo.TABLE_B)

Statement 2:

select

count(*)

from

TABLE_A A

where

not exists (select 1 from server2.DB.dbo.TABLE_B B where A.ColumnX = B.ColumnX)

laker_42 SSCarpal Tunnel Points: 4785 More actions · Answer 8

Claude,

Thanks for the help. I rewrote my update statement using the not exists & it worked perfectly. Still not sure why it didn't work before but it works!

Thanks!

John