Update statement not working when referencing a linked server

Question

Update statement not working when referencing a linked server

Norene Malaney

SSCommitted

Points: 1896
More actions
November 4, 2005 at 11:45 am

#166877

I am trying to run an update statement that contains a join to a linked server table. Every time I run the query it hangs (processes are blocked).
Here is the specific information:
ServerA is a 2000 SQL server that contains database “DatabaseA”. ServerB is PC running MSDE that contains database “DatabaseB”. ServerA contains a linked server titled "ServerB" which points to ServerB (OLEDB/SQL Server).
Here is the update statement that I am trying to run from ServerA.DatabaseA:
update A_Table1
set AT1.Field1 = NULL
from A_Table1 AT1 INNER JOIN ServerB.DatabaseB.dbo.B_Table1 BT1 ON
AT1.Field2 = BT1.Field2
(Basically, I am trying to set AT1.Field1 = NULL for all occurrences of Field2 that exist in BT1.Field2)
I tested the linked server and I can run the following select statement without any problems (the appropriate data is returned):
select AT1.Field1, AT1.Field2
from A_Table1 ATI INNER JOIN ServerB.DatabaseB.dbo.B_Table1 BT1 ON
AT1.Field2 = BT1.Field2
Any help would be appreciated. This is the first time that I have worked with linked servers.
Have a good day,
Norene Malaney

Viewing 6 posts - 1 through 5 (of 5 total)

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

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 1

Is the column you are joining on a numeric or character column. If it is a varchar or nvarchar, you may want to ensure that you have the Linked Server Option "Collation Compatible" checked.

A.J.
DBA with an attitude

Norene Malaney SSCommitted Points: 1896 More actions · Answer 2

The field that I am using in my join is a varchar. I set the "Collation Compatible" option on the linked server properties and tried the update statement ... same result .. update statement hung.

Have a good day,

Norene Malaney

Adam Wilbur SSCertifiable Points: 7001 More actions · Answer 3

hung? how long did it run? How many rows in each table? Did you try to run a profiler trace to see if you can capture any additional troubleshooting information?

A.J.
DBA with an attitude

Jo Pattyn SSC-Dedicated Points: 32342 More actions · Answer 4

What is the estimated query plan?

Is there an index on AT1.Field2 and BT1.Field2?

Is it the only statement running on database B (since MSDE has quite some limitations)?

These would normally result in an error:

Having no primary key on the tables.

MSDTC needs reconfiguring (especially on windows 2003, windows xp sp2)

Norene Malaney SSCommitted Points: 1896 More actions · Answer 5

As I mentioned, I did try setting the "collation compatible" option. However, the query still resulted in blocked processes (even after running for a few minutes). The tables are not that big on either side (Server A has 21,000 records where Server B's table only has 200 records).

I did try running the same query against 2 servers running SQL Server (not MSDE) and the query worked fine (completed w/in seconds). I must be hitting some limitation due to Server B running MSDE.

Thanks for the help thus far. I am open to any other ideas.

Have a good day,

Norene Malaney