Post reply

Getting error during INSERT records into AS400 table from SQL server 2005 table

  • June 8, 2012 at 2:56 am

    #262071

    Hi All,

    SQL Server 2005 connecting to AS400.

    I have a linked server set up from SQL Server to AS400.

    I can use openquery() to select data from the remote server:

    Like:

    select * from openquery(InterfaceClaimData,'select * from jdidpssc.F0311Z1') -- works fine

    If I try to INSERT I run into difficulty.

    INSERT OPENQUERY (InterfaceClaimData, 'SELECT * FROM jdidpssc.F0311Z1')

    SELECT * from ar -- fails

    Structure and fields of the both tables are same

    The error message i am getting like :

    OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]Syntax error or access violation. SQLSTATE = 55019I : Table is in invalid state for operation. SQLCODE = -7008 : F0311Z1 in JDIDPSSC not valid for operation.".

    Msg 7343, Level 16, State 2, Line 1

    The OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" could not INSERT INTO table "[MSDASQL]".

    please help me.

    Regards,

    Sachin

    9891573054

  • June 8, 2012 at 4:20 am

    #1498615

    F0311Z1 in JDIDPSSC not valid for operation

    This suggests the target table is not writable.

    I would check:

    1) permissions

    2) journaling (check this with your DB2 DBA)

    3) isolation level (you usually set this on the provider itself)

    Hope this helps

    Gianluca

    -- Gianluca Sartori

  • June 8, 2012 at 6:45 am

    #1498689

    Gianluca Sartori (6/8/2012)

    F0311Z1 in JDIDPSSC not valid for operation

    This suggests the target table is not writable.

    I would check:

    1) permissions

    2) journaling (check this with your DB2 DBA)

    3) isolation level (you usually set this on the provider itself)

    Hope this helps

    Gianluca

    Hi,

    Now it's working. Now data going to inserting...........but sometime data inserting and sometime fail.......i don't know why

    when insertion fail then error message coming like :

    OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "Neither the isolation level nor a strengthening of it is supported.".

    OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]General error.".

    OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]Driver not capable. SQLGetInfo".

    OLE DB provider "MSDASQL" for linked server "InterfaceClaimData" returned message "[NetManage][RUMBA Data Access][AS400JDE]Driver not capable. Attributes not valid.".

    Msg 7392, Level 16, State 2, Line 1

    Cannot start a transaction for OLE DB provider "MSDASQL" for linked server "InterfaceClaimData".

    Regards,

    Sachin.

  • June 11, 2012 at 2:28 pm

    #1499513

    Seems to be a provider specific error.

    I'm afraid I can't offer more help on this subject. Which provider are you using?

    -- Gianluca Sartori

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

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