SQL to update linked server table with a where clause

Question

I have to update a linked server table jj_rec based on two columns, how can i do that?

I am able to update with below SQL with just one column condition

UPDATE central.carstrain.informix.jj_rec SET
    LINE1 = 'Barcode' where id=340059 

But it fails when I include an extra condition aa='PERM'. Any help is appreciated.

UPDATE central.carstrain.informix.jj_rec SET
    LINE1 = 'Barcode' where id=340059 and aa='PERM'

I am sure jj_rec table has the column 'aa'. This happens to me always for other tables also when i have more than one condition in where clause. 

Also column aa is of char(4) and linkedserver database is informix (IBM).

Error is ... OLE DB provider "MSDASQL" for linked server "central" returned message " [Informix][Informix ODBC Driver]Error in row.". OLE DB provider "MSDASQL" for linked server "central" returned message "[Informix][Informix ODBC Driver]Unspecified System Error = -21005.". Msg 7330, Level 16, State 2, Line 1 Cannot fetch a row from OLE DB provider "MSDASQL" for linked server "central". –

Please advise other alternatives.

Answer 1

UPDATE OPENQUERY(Server, 'SELECT col1, col3 FROM tbl..')
   SET col1 = @param1
 WHERE col2 = param2

-----------------------------


update openquery()
set...
from openquery() join abc on ...

---

Best Regards, Uri Dimant SQL Server MVP http://dimantdatabasesolutions.blogspot.com/ http://sqlblog.com/blogs/uri_dimant/

Answer 2

Thank you but that doesn't work.

Answer 3

A list of thoughts and ideas:

• Just out of curiosity:  Are you sure you sure INFORMIX_JJ_REC is a table and not a view?  If it's a view and AA is a column from some other table, then there may be other subtle nuances involved.
• Look for newer ODBC drivers for Informix:  If you're using an outdated one, that could be your problem.
• Consider searching for your answer in an Informix folder too.
• Just to prove/disprove a theory:  Can you do updates/queries just against column AA? 
• And finally, if those fail, see if something like this might work: (Pseudo-SQL)

Update central.carstrain.informix.jj_rec
   Set Line1 = 'Barcode' where <key-column> in
    (
      Select <key-column> from central.carstrain.informix.jj_rec
       where ID = 340059 and aa='PERM'
    )

Answer 4

Actually, there are many pages of search results, if you were to do a Bing search on Informix "-21005"

The answer may more lie there than in my previous suggestions, although generally, good things happen when you make sure you're using an up-to-date driver, so make sure about that anyway!

Answer 5

@john, thank-you. Here the my table has a composite key which is id and aa. So the problem. 

Answer 6

Any progress?

SSIS works better with inter-server programming than linked server.

---

Kalman Toth, SQL Server & Business Intelligence Training; SQL SERVER BEST PRACTICES