Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

four part name for DB2 linked server

Posted on 2006-10-19
7
Medium Priority
?
383 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 143

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 143

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
Veeam Task Manager for Hyper-V

Task Manager for Hyper-V provides critical information that allows you to monitor Hyper-V performance by displaying real-time views of CPU and memory at the individual VM-level, so you can quickly identify which VMs are using host resources.

 
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 143

Accepted Solution

by:
Guy Hengel [angelIII / a3] earned 2000 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 143

Expert Comment

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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this blog post, we’ll look at how ClickHouse performs in a general analytical workload using the star schema benchmark test.
In this article, we’ll look at how to deploy ProxySQL.
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…
In this video, Percona Director of Solution Engineering Jon Tobin discusses the function and features of Percona Server for MongoDB. How Percona can help Percona can help you determine if Percona Server for MongoDB is the right solution for …
Suggested Courses

876 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