AS400 as Linked Server

• cbrammer1219

  SSCarpal Tunnel

  Points: 4476

  More actions

  March 12, 2020 at 6:27 pm

  #3733729

  I am getting this error when trying to do a select from the Linked server

  (((

  Msg 7356, Level 16, State 1, Line 1

  The OLE DB provider "IBMDASQL" for linked server "" supplied inconsistent metadata for a column. The column "" (compile-time ordinal 8) of object "" was reported to have a "DBTYPE" of 129 at compile time and 128 at run time.

  )))

  Anyone have an Idea to get around it, I used openquery but I get this error

  OLE DB provider "IBMDASQL" for linked server "AS400_MINDSERV" returned message "SQL0104: Token . was not valid. Valid tokens: FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET.

  Cause . . . . . : A syntax error was detected at token .. Token . is not a valid token. A partial list of valid tokens is FOR USE SKIP WAIT WITH FETCH LIMIT ORDER UNION EXCEPT OFFSET. This list assumes that the statement is correct up to the token. The error may be earlier in the statement, but the syntax of the statement appears to be valid up to this point. Recovery . . . : Do one or more of the following and try the request again: -- Verify the SQL statement in the area of the token .. Correct the statement. The error could be a missing comma or quotation mark, it could be a misspelled word, or it could be related to the order of clauses. -- If the error token is <END-OF-STATEMENT>, correct the SQL statement because it does not end with a valid clause.".

  Msg 7321, Level 16, State 2, Line 46

  An error occurred while preparing the query "SELECT * FROM AS400_MINDSERV.S78359E0.QS36F.INDETLWO" for execution against OLE DB provider "IBMDASQL" for linked server "AS400_MINDSERV".

  SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM AS400.S78359E0.QS36F.INDETLWO')

   

   

   

• Ken McKelvey

  SSCoach

  Points: 18842

  More actions

  March 13, 2020 at 2:44 pm

  #3734072

  SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM AS400.S78359E0.QS36F.INDETLWO')

  I do not know much about AS400/DB2 but I think three part naming would be more likely to work with OPENQUERY

  SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM S78359E0.QS36F.INDETLWO')

• Jeff Moden

  SSC Guru

  Points: 1004324

  More actions

  March 14, 2020 at 2:15 am

  #3734367

  Ken McKelvey wrote:

  SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM AS400.S78359E0.QS36F.INDETLWO')

  I do not know much about AS400/DB2 but I think three part naming would be more likely to work with OPENQUERY

  SELECT * FROM OPENQUERY([AS400], 'SELECT * FROM S78359E0.QS36F.INDETLWO')

  That's the ticket according to the way we do it where I work.  The Linked Server name is what the first operand of the OPENQUERY is used for.

  --Jeff Moden

  ---

  RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
  First step towards the paradigm shift of writing Set Based code:
  ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

  Change is inevitable... Change for the better is not.
  

  ---

  Helpful Links:
  How to post code problems
  How to Post Performance Problems
  Create a Tally Function (fnTally)