Error in open Query in DB2 linked server

• sam.jhonny15

  SSC Enthusiast

  Points: 188

  More actions

  February 1, 2011 at 9:26 am

  #234376

  Hello,

  We have set up a linked server for DB2 with name BID, when we try to run a open query script

  against this linked server both in ssms and ssis/oledb source its throwing an error

  the syntax is

  SELECT * FROM

  OpenQuery(BID, 'SELECT NAME FROM ''[BID].[BID].[SAPBI].[/BIC/OHZCUST]'' ')

  and the error is

  OLE DB provider "DB2OLEDB" for linked server "BID" returned message "An unexpected token "FROM" was found following "SELECT NAME ". Expected tokens may include: "<table_expr>". SQLSTATE: 42601, SQLCODE: -104".

  Msg 7321, Level 16, State 2, Line 1

  An error occurred while preparing the query "SELECT NAME FROM '[BID].[BID].[SAPBI].[/BIC/OHZCUST]' "

  thanks

  Sam.

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  February 2, 2011 at 2:32 am

  #1281297

  Is it an option to go to the DB2 database and execute the source query there, just to see if it works?

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• ssills

  Old Hand

  Points: 375

  More actions

  February 4, 2011 at 8:41 am

  #1282516

  I would try it without the four-part name. My queries against the AS/400 only used two part naming, but the data may have always been just on the IFS rather than in DB/2. It's been a long time. I don't remember using the double quotes, either.

• corey lawson

  Hall of Fame

  Points: 3773

  More actions

  January 8, 2014 at 5:38 pm

  #1678639

  Let's say the name of the linked server to the DB2 database is "IBM", and the DB2 database it's connected to is "TheData".

  You can run this query to look at all the tables that you access to in the database you set up the linked server to:

  select * from OpenQuery(IBM,'select * from SYSIBM.SYSTABLES')

  This should get you the right TABLE_CAT, TABLE_SCHEM and TABLE_NAME for the DB2 tables.

  Then, you can run queries on the linked server either by:

  select * from OpenQuery(IBM,'select * from TABLE_CAT.TABLE_SCHEM.TABLE_NAME')

  or:

  select * from IBM.TABLE_CAT.TABLE_SCHEM.TABLE_NAME

• Koen Verbeeck

  SSC Guru

  Points: 259105

  More actions

  January 9, 2014 at 12:05 am

  #1678680

  corey lawson (1/8/2014)

  ---

  Let's say the name of the linked server to the DB2 database is "IBM", and the DB2 database it's connected to is "TheData".

  You can run this query to look at all the tables that you access to in the database you set up the linked server to:

  select * from OpenQuery(IBM,'select * from SYSIBM.SYSTABLES')

  This should get you the right TABLE_CAT, TABLE_SCHEM and TABLE_NAME for the DB2 tables.

  Then, you can run queries on the linked server either by:

  select * from OpenQuery(IBM,'select * from TABLE_CAT.TABLE_SCHEM.TABLE_NAME')

  or:

  select * from IBM.TABLE_CAT.TABLE_SCHEM.TABLE_NAME

  I hope the OP solved his issue 3 years ago 😉

  Need an answer? No, you need a question
  My blog at https://sqlkover.com.
  MCSE Business Intelligence - Microsoft Data Platform MVP

• corey lawson

  Hall of Fame

  Points: 3773

  More actions

  January 9, 2014 at 12:57 am

  #1678687

  Yeah...but it is in here now, so just another place for the Google bots to pick it up..:w00t:

  Now if there's somewhere handy with all the options for IBMDASQL... from a tsql perspective...