Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

four part name for DB2 linked server

Posted on 2006-10-19
7
Medium Priority
?
381 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Recently, Microsoft released a best-practice guide for securing Active Directory. It's a whopping 300+ pages long. Those of us tasked with securing our company’s databases and systems would, ideally, have time to devote to learning the ins and outs…
Recently I was talking with Tim Sharp, one of my colleagues from our Technical Account Manager team about MongoDB’s scalability. While doing some quick training with some of the Percona team, Tim brought something to my attention...
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…

664 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