Post reply

linked server to UDB

  • March 15, 2007 at 1:40 pm

    #61112

    I'm trying to set up a linked server from sql server 2000 to udb 8.1.14. My selects to the UDB table work fine. Inserts/updates deletes fail. The insert returns - OLE DB error trace [OLE/DB Provider 'IBMDADB2' IRowsetChange::InsertRow returned 0x80040e53: The provider does not support the necessary method.]. Here's the insert format - INSERT OPENQUERY(TESTUDB3, 'SELECT * FROM TEST.T1') VALUES( '1','1','1','1','1'). Is this the correct format? Does the IBM provider allows updates?

  • March 16, 2007 at 7:35 am

    #695452

    Hi Giorgio,

    I have this kind of thing up and running using the OLEDB provider (IBMDADB2) with fix pack 10.  Unfortunately, I'm not in a location that allows me to see the code that I'm using.

    So, from memory, I dont believe I ever got OPENQUERY to work, but you might.  I think that you could try explicitly naming the columns in your select.  I probably tried that to no avail.

    What I ended up using was the four part naming.  Assuming that your linked server is set up to specify the schema name (TEST) then I believe that you would specify only the first and fourth names like so:

    Insert TESTUDB3...T1 VALUES( '1','1','1','1','1')

    You might also need to use SET IMPLICIT_TRANSACTIONS OFF and SET XACT_ABORT ON. 

    Take these ideas for a spin and see what happens.  I spent many hours to figure all this out, so I hope you find your way through it easier than I did.

    good luck jg

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply