help with openquery update statement 1

[ddiamond]

I am attempting to update a table on our AS400 from a SQL Server 2008 stored proc using a linked server.

UPDATE OPENQUERY( ADM_AS400_LPAR, 
  'SELECT grbpqs AS RequestStatus,
    FROM xggndta.zagrdf00 a
    WHERE grbpqs = ''10''  
      AND grcrda = 1100809
      AND grcrti = 1514
      AND grbptr = ''CHIRO13343'' ')
SET RequestStatus = '20'

This generates the following error message:
OLE DB provider "IBMDA400" for linked server "ADM_AS400_LPAR" returned message "Key column information is insufficient or incorrect. Too many rows were affected by update.".

The strange thing is that the update completes successfully eventhough I'm receiving the error message. Now I don't get the error message when I'm only updating one record, but the select query will often return multiple rows. That is when I get the error message. But it is intential that the query will often return multiple rows because in that scenerio I am trying to update multiple rows. And like I mentioned before, it does successfully update multiple records on the AS400. I just cannot figure out how to make the error message to go-away. As a temporay work-around, I've placed the code in a try-catch block, but I'm looking for a better solution.

I've also tried the following version of the query, but the results are the same. I still get the error message.

UPDATE OPENQUERY( ADM_AS400_LPAR, 
  'SELECT 
      grbpqs AS RequestStatus,
      grcrda AS BatchCreateDate,
      grcrti AS BatchCreateTime,
      grbptr AS JobTypeReference
    FROM ddiamond.zagrdf00 a ')
SET RequestStatus      = '20' 
WHERE RequestStatus    = '10'  
  AND BatchCreateDate  = 1100809
  AND BatchCreateTime  = 1534
  AND JobTypeReference = 'CHIRO14350'

 

[Emtucifor]

Can you run any kind of trace on the remote system to see what actual queries are being submitted? I've found that extremely useful in diagnosing problems with linked-server queries.

Also, you should know that linked server queries do NOT operate how one might expect. You can get very strange stuff like pulling huge rowsets across the connection and joining them locally, even though that may not make sense. Also be aware that another way linked-server queries can be counterintuitive is that an update or select can end up being performed row-by-row with a separately submitted statement for each one, instead of set-based, with all of this occurring secretly behind the scenes.

The weird thing is that sometimes, switching to row-by-row is a huge performance boost with linked-server queries because of the very problems inherent in joining recordsets across the link.

Now in your case things are a little simpler because the one table involved is remote.

So, can you create a stored procedure and just execute that?

EXECUTE ADM_AS400_LPAR...SPName @BatchCreateDate, @BatchCreateTime, @JobTypeReference -- You'd have to look up proper database and schema/owner parts for the items between the dots. Experimentation can work.

You can also sometimes perform queries just using direct four-part naming as well:

UPDATE ADM_AS400_LPAR..ddiamond.zagrdf00
SET RequestStatus      = '20'
WHERE RequestStatus    = '10'  
  AND BatchCreateDate  = 1100809
  AND BatchCreateTime  = 1534
  AND JobTypeReference = 'CHIRO14350'

Last, have you tried using an alias instead:

UPDATE X
SET RequestStatus = '20'
FROM
  OPENQUERY( ADM_AS400_LPAR,
  'SELECT grbpqs AS RequestStatus,
    FROM xggndta.zagrdf00 a
    WHERE grbpqs = ''10''  
      AND grcrda = 1100809
      AND grcrti = 1514
      AND grbptr = ''CHIRO13343'' ') X

 

[ddiamond]

Emtucifor

Thanks for all of your suggestions. I'll try them out tomorrow. I have a feeling that I may ultimately go with your stored procedure idea. Since I don't need to join to sql server tables, that probably will give me my best performance.

- Dan

 

[ddiamond]

Emtucifor,

I first tried running trace on the remote system to see what it was actually executing. The original update statement with openquery resulted in the following 2 executions on our remote system:

SELECT grbpqs AS RequestStatus FROM xggndta.zagrdf00 WHERE grbpqs=? AND crcrda=? AND grcrti=? AND grbptr=?

UPDATE EMJ_TST.XGGNDTA.ZAGRDF00 SET GRBPQS=? WHERE GRBPQS=? AND CRCRDA=? AND GRCRTI=? AND GRBPTR=?

Now the select statement isn't really necessary, but I guess anytime you use an openquery statement, a select statement will be generated. And the 2nd update statement looks perfect. That would explain why the update was successful. Still no closer to understanding why an error message is generated which I need to suppress in a TRY Catch block.

Next I tried the alias approach you suggested.

UPDATE X
SET RequestStatus = '20'
FROM OPENQUERY( ADM_AS400_LPAR, 
   'SELECT grbpqs AS RequestStatus
    FROM xggndta.zagrdf00
    WHERE grbpqs = ''10''  
      AND grcrda = 1100810
      AND grcrti = 949
      AND grbptr = ''CHIRO12560'' ') X

This had the same results as my original update query. The update succeeded, but an error message was generated. And my remote trace looked the same.

Finally I doing the update directly using the 4 part notation.

UPDATE ADM_AS400_LPAR.emj_tst.xggndta.zagrdf00
  SET grbpqs = '20'
WHERE grbpqs = '10'  
  AND grcrda = 1100810
  AND grcrti = 949
  AND grbptr = 'CHIRO12560'

This actually worked, and no error message. My remote trace looked similar. Only difference is I no longer get the unwanted select statement generated by the openquery statement.