asked
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
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'
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.
