x

2005 SQL Server Linked Server Update - Runs Endlessly

THIS AS TAKEN UP MOST OF MY MONDAY --- I am attempting to run the following:

 UPDATE OPENQUERY(MBDATA, 'SELECT CUST_ID, CUST_NAME FROM CUSTDATA') SET CUSTNAME = 'TESTNAME' WHERE CUSTID = 'CUST123'

WHEN I RUN IT - IT SHOWS EXECUTING QUERY - BUT IT RUNS FOREVER (2 hrs, 36 min)AS OF THIS MESSAGE - NEVER TIME'S OUT OR GIVES AN ERROR

EVERYTHING WORKS FINE WHEN I RUN THE FOLLOWING:

 SELECT * FROM OPENQUERY(MBDATA, 'SELECT CUST_ID,CUST_NAME FROM CUSTDATA')a WHERE a.CUSTID = 'CUST123'


CAN SOMEWHERE EXPLAIN TO ME WHAT'S GOING ON - AND A POSSIBLE SOLUTION......

THANKS IN ADVANCE . . . . .

MB

more ▼

asked Jan 02, 2012 at 03:16 PM in Default

avatar image

MEB
11 1 1 1

(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

Way of performing Update statement on Linked Server-

(1)

Update LocalTable SET Field1 = B.Field1 FROM LinkedServer.dbname.dbo.RemoteTable B INNER JOIN LocalTable A ON B.Field2 = A.Field2 AND B.Field3 = A.Field3

(2)

UPDATE LocalTable SET Field1 = B.Field1 FROM OPENQUERY(LINKEDSERVER,'SELECT Field1, Field2, Field3 FROM dbname.dbo.RemoteTable) B INNER JOIN LocalTable A ON B.Field2 = A.Field2 AND B.Field3 = A.Field3

Please rewrite your query and check again.

  UPDATE OPENQUERY(MBDATA, 'SELECT

CUST_ID, CUST_NAME FROM CUSTDATA WHERE CUSTID = 'CUST123'') SET CUSTNAME = 'TESTNAME'

more ▼

answered Jan 02, 2012 at 08:58 PM

avatar image

Sharma
1.4k 90 93 96

Please check Estimate execution plan to get information of query waits.

Jan 03, 2012 at 08:52 PM Sharma
(comments are locked)
10|1200 characters needed characters left

Can you observe the linked server? Is your process blocked? How about the wait stats on the server you're on. What is that process waiting on? Check sys.dm_exec_requests as a starting point.

more ▼

answered Jan 03, 2012 at 08:25 AM

avatar image

Grant Fritchey ♦♦
137k 20 44 81

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.

Topics:

x445
x438
x174
x142

asked: Jan 02, 2012 at 03:16 PM

Seen: 1592 times

Last Updated: Jan 03, 2012 at 11:33 PM

Copyright 2017 Redgate Software. Privacy Policy