four part name for DB2 linked server

Hello people

I have set up an AS400 DB2 (DB/2) database as a linked server to my local copy of SQL. The trouble is, I can't figure out how to specify a table name on the linked server in the form that the SQL Server Engine understands.

I have tried

select count(*) from server.libname.tabname

SELECT count(*) FROM [server].[server].[LIBNAME].[tabname]

select count(*) from server.libname..tabname

and many other variations, but I keep getting errors along the lines of.


"Error:
Invalid object name 'beans..tabname'.
Occurred on Line : 1"


How can a run a select against the DB2 data?

Thanks
LVL 2
molarAsked:
Who is Participating?

[Webinar] Streamline your web hosting managementRegister Today

x
 
Guy Hengel [angelIII / a3]Connect With a Mentor Billing EngineerCommented:
you might try this:
select count(*) from server.S654B5AB.libname.tabname

now, I am not sure if the userid/password settings are not overwritten implicitely
can you try to go in the linked server properties to the security tab ( after removing the userid/password from the provider string), and configure to choose in the "For a login not defined in the list abovce .."
to select "Be Made using this security context" and specify the db2 username and password there
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
did you try this:
select count(*) from server..libname.tabname

0
 
molarAuthor Commented:
I just tried it now.
I got the message



OLE DB error trace [Non-interface error:  OLE DB provider does not contain the table: ProviderName='srvname', TableName='"libname"."tabname"'].

Statement Executed.
No Results Available.

Error:
OLE DB provider 'srvname' does not contain table '"libname"."tabname"'.  The table either does not exist or the current user does not have permissions on that table.
Occurred on Line : 1





I know that userid I am using has permissions as I can query the table from an AS400 console session..
0
The new generation of project management tools

With monday.com’s project management tool, you can see what everyone on your team is working in a single glance. Its intuitive dashboards are customizable, so you can create systems that work for you.

 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
>I know that userid I am using has permissions
what userid?  how did you specify the userid in the linked server permissions?
0
 
molarAuthor Commented:
In the linked server properties I have specified a provider strng as below

MSDASQL.1;UserId=xxxx;Password=xxxx;Persist Security Info=True;Data Source=10.13.0.15;Initial Catalog=S654B5AB

(real username and password changed to x's)

I can click on the linked server and see the linked tables in Enterprise Manager
0
 
molarAuthor Commented:
I just tried the line below

>select count(*) from server.S654B5AB.libname.tabname


AND IT WORKED!!!!

I am sooo happy about that. I've been struggling with that for about a day now.

Thanks so much

Mike
0
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
Glad I could help
0
All Courses

From novice to tech pro — start learning today.