Linked server issue

• newmatc

  SSC Enthusiast

  Points: 104

  More actions

  March 29, 2006 at 9:33 am

  #89906

  I am trying to set-up a linked server in SQL Server to our AS400 using client access. After setting up the linked server I can select from a table on the 400 in query analyzer using the following syntax: 

   

      Select * from AS400.S1042A6M.QGPL.CTELAB_1

   

  This works fine. I receive the rows in the table on the AS400.

   

  When I try to insert records using this syntax it fails:

      Insert into AS400.S1042A6M.QGPL.CTELAB_1(PER_ID,ACCTNUM,JDEABNUM,USERID)

      Values ('00015096513511D696FB000103147803','LJS00000074460','90076833','CtelUser')

   

  Here is the error message that I receive:

      Server: Msg 7399, Level 16, State 1, Line 4

      OLE DB provider 'MSDASQL' reported an error.  

      [OLE/DB provider returned message: [IBM][Client Access Express ODBC Driver (32-bit)][DB2/400 SQL]SQL7008 - CTELAB_1 in QGPL not valid for operation.]

      OLE DB error trace [OLE/DB Provider 'MSDASQL' IOpenRowset:penRowset returned 0x80004005:   ]

  Has anyone been able to successfully set-up a linked server to an AS400 and if so, what am I doing wrong.

• paul backer

  SSC Rookie

  Points: 32

  More actions

  April 4, 2006 at 10:12 am

  #630728

  You might try changing the default library in your ODBC connection. You currently have QGPL as the default library.

  You may also want to verify that the AS/400 account you have established for the connections has the appropriate rights to perform inserts.

  Hope this helps.

• newmatc

  SSC Enthusiast

  Points: 104

  More actions

  April 4, 2006 at 10:23 am

  #630735

  The physical file that I am inserting into is located in the QGPL library. The account that I'm using has full rights.