Knowledgebase
[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 an MS SQL-linked server with QRemote.

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

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

I am using the 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 an 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 an 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 returns the 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." Rows were updated, but still, it shows the error message. The OLE DB provider "MSDASQL" for linked server "QREMOTE" could not UPDATE table " [MSDASQL].".

I am using the 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 the message "Key column information is insufficient or incorrect. Too many rows were affected by the update.".Msg 7343, Level 16, State 4, Line 1The OLE DB provider "MSDASQL" for linked server "QREMOTE" could not UPDATE table "[MSDASQL]." 

 

Solutions 2:

To overcome this issue, you need to include an extra "where" clause outside the open query.

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 the where condition twice, inside the OpenQuery and outside.       

 

 

 

 

 

 

 

 

(1 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).