Solved

four part name for DB2 linked server

Posted on 2006-10-19
7
375 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
Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

 
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 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 143

Expert Comment

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

Featured Post

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

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…
As technology users and professionals, we’re always learning. Our universal interest in advancing our knowledge of the trade is unmatched by most industries. It’s a curiosity that makes sense, given the climate of change. Within that, there lies a…
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…

710 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