Post reply

INSERT in AS400/DB2

  • February 24, 2005 at 5:59 pm

    #102404

    Hi

    I am trying to INSERT into AS400 DB2 thru Linked Server on SQL Server connected using ODBC DSN (with Commit *NONE). My VB program (ADO) fails to even SELECT data from the linked server.

    I run the queries from VB code suing ADO connection to the same ODBC DSN. I get the following errors when I pass these for querystrings to adoConnection.Execute :-

    1) SELECT * FROM LINKEDSERVER.CATALOG.SCHEMA.TABLENAME

    [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE

    2) select * from openquery(LINKEDSERVER, 'select * from  MYTABLE1')

    [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token ( was not valid. Valid tokens: FOR WITH FETCH ORDER UNION EXCEPT OPTIMIZE

    3) INSERT INTO LINKEDSERVER.CATALOG.SCHEMA.TABLENAME1 SELECT * FROM LINKEDSERVER.CATALOG.SCHEMA.TABLENAME2

    [IBM][iSeries Access ODBC Driver][DB2 UDB]SQL0104 - Token . was not valid. Valid tokens: ( ? : WITH SELECT VALUES <INTEGER> <IDENTIFIER>

    4) When I run these queries from SQL Query Analyzer, all SELECT statements work but INSERT says "SQL7008 - not valid for operation"

    The files are not journaled.

    Any answers..

    Thanks.

  • February 24, 2005 at 8:24 pm

    #544062

    AS400's don't use owners, catalogs, DB's etc (well, not like MS SQL anyway).  Typically you would SELECT * FROM Library.Table (aka Library.FILE)

    Therefore, I'm not sure about your syntax here with the path to the tablename.  However, I will link to our AS400 tomorrow morning and test that syntax.

    Until then, I would check on the ODBC DSN.  Typically I have seen "Commit *None" associated with "Read-Only" Client Access DSN's.  That would make the "SQL7008 - not valid for operation" error valid until you change the DSN to "Read/Write"

    I will follow up...

    Ryan

  • February 25, 2005 at 7:14 am

    #544147

    OK, now I see the whole SELECT * FROM LINKEDSERVER.CATALOG.SCHEMA.TABLENAME syntax.  This is actually based on an interpretation that SQL is making regarding native AS400 objects.

    Anyway, I tried both of your queries and they did work in Query Analyzer (as you stated).

    Sorry - not sure what to tell you.  You could create a DSN using Client Access that goes directly to the AS400 using ADO and VB.  I know for a fact that that works.  Although, I am assuming that at some point you are making table comparison or joins between SQL and AS400 were a single DSN and linked server simplify things.

    If you are joining AS400 tables to SQL via a linked server, can you do all of that work in a stored procedure and simply use ADO to call the results of the SQL procedure?

  • February 25, 2005 at 12:53 pm

    #544250

    Thanks. I really did not have to use Linked Server thru VB code. A straight query to AS400 through ADO works fine

    Thankf for ur help

Viewing 4 posts - 1 through 3 (of 3 total)

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