Solved

four part name for DB2 linked server

Posted on 2006-10-19
7
364 Views
Last Modified: 2012-06-21
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
0
Comment
Question by:molar
  • 4
  • 3
7 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17764854
did you try this:
select count(*) from server..libname.tabname

0
 
LVL 2

Author Comment

by:molar
ID: 17764888
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17764914
>I know that userid I am using has permissions
what userid?  how did you specify the userid in the linked server permissions?
0
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

 
LVL 2

Author Comment

by:molar
ID: 17765085
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
 
LVL 142

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 500 total points
ID: 17765241
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
 
LVL 2

Author Comment

by:molar
ID: 17765302
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
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 17765410
Glad I could help
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

SQL Command Tool comes with APEX under SQL Workshop. It helps us to make changes on the database directly using a graphical user interface. This helps us writing any SQL/ PLSQL queries and execute it on the database and we can create any database ob…
Shadow IT is coming out of the shadows as more businesses are choosing cloud-based applications. It is now a multi-cloud world for most organizations. Simultaneously, most businesses have yet to consolidate with one cloud provider or define an offic…
Video by: Steve
Using examples as well as descriptions, step through each of the common simple join types, explaining differences in syntax, differences in expected outputs and showing how the queries run along with the actual outputs based upon a simple set of dem…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

17 Experts available now in Live!

Get 1:1 Help Now