Knowledgebase: TroubleShooting
[QODBC-Desktop] Update query scanning all records in Linked Server
Posted by Jack - QODBC Support on 19 August 2014 11:55 AM

Troubleshooting: Update query scanning all records in Linked Server

Problem Description 1:

I am using MS SQL linked server with QRemote.

I am updating a single EstimateItemLine, passing Ref Number and ItemFullName (also tried itemtxnlineid). Every time the update statement, with OpenQuery or without openquery on the linked server, it's searching all the records of EstimteLine and then is updating the record. This process takes 3.5 to 4 minutes.

Ideally, it should not scan all the records as Ref Number is provided.

I am using below query:

Update openquery(qremote, 'select ClassRefListId from EstimateLine where RefNumber=''501'' and EstimateLineTxnLineID=''8DB9-1071524326''') SET ClassRefListId = '40000-933272658'

Solutions 1:

The performance of update query with OpenQuery can be improved by including the "where" criteria outside the open query.

For Example:

The Query

Update openquery(qremote, 'select ClassRefListId from EstimateLine where RefNumber=''501'' and EstimateLineTxnLineID=''8DB9-1071524326''') SET ClassRefListId = '40000-933272658'

will perform faster when executing it as

Update openquery(qremote, 'select ClassRefListId,EstimateLineTxnLineID,RefNumber from EstimateLine where RefNumber=''501'' and EstimateLineTxnLineID=''8DB9-1071524326''') SET ClassRefListId = '40000-933272658' where RefNumber='501' and EstimateLineTxnLineID='8DB9-1071524326'

So yes, you need to include [ RefNumber='501' and EstimateLineTxnLineID='8DB9-1071524326' ] twice, inside the OpenQuery and outside too.       

Problem Description 2:

I am using MS SQL linked server with QRemote.

In SQL Server when we use update openquery, without including fields that identify a single row that needs to be updated then SQL is returning error "OLE DB provider "MSDASQL" for linked server "QREMOTE" returned the message "Key column information is insufficient or incorrect. Too many rows were affected by the update."The OLE DB provider "MSDASQL" for linked server "QREMOTE" could not UPDATE table " [MSDASQL]". Actually rows were updated, but still, it shows the error message.

I am using below query:

Update openquery(QREMOTE,'select customfieldtransportaddress,CustomFieldOther from salesorderwhere txnDate >= {d''2013-12-29''} and refNumber = ''8801'' ') set CustomFieldOther='1514999',customfieldtransportaddress = 'Conexão333'

Error message:

OLE DB provider "MSDASQL" for linked server "QREMOTE" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".Msg 7343, Level 16, State 4, Line 1The OLE DB provider "MSDASQL" for linked server "QREMOTE" could not UPDATE table "[MSDASQL]". 

 

Solutions 2:

I would like to inform you that you need to include extra "where" clause outside the open query to overcome this issue.

For Example:

Update openquery(QREMOTE,'select refNumber,customfieldtransportaddress,CustomFieldOther from salesorderwhere txnDate >= {d''2013-12-29''} and refNumber = ''8801'' ') set CustomFieldOther='1514999',customfieldtransportaddress = 'Conexão333' where refNumber = '8801'


So, you need to include where condition twice, inside the OpenQuery and outside too.       

 

 

 

 

 

 

 

 

(0 vote(s))
Helpful
Not helpful

Comments (0)
Post a new comment
 
 
Full Name:
Email:
Comments:
CAPTCHA Verification 
 
Please complete the captcha below (we use this to prevent automated submissions).