• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 2487
  • Last Modified:

How to query an alias in a DB2 linked server from SQL Server 2008

Hello everyone,

I created an table alias in DB2 and created a linked DB2 server in SQL Server 2008 with the IBMDASQL provider. Now I need to query the alias from SQL Server, but I receive the following error message:

Msg 7357, Level 16, State 2, Line 3
Cannot process the object "S650a370.PROBIS03.NAW_ALIAS". The OLE DB provider "IBMDASQL" for linked server "DB2SQL" indicates that either the object has no columns or the current user does not have permissions on that object.

However, the object has multiple columns and I have Owner permissions on that object.

I tried to create the linked server with the IBMDA400 and IBMDARLA providers without any success. I also tried to query some other tables and that works fine, but when i try to query an alias i get an error message. Unfortunately I need to use the alias, because the underlying table contains multiple members and I can't query a table member directly with an SQL command.

Any ideas? Thanks in advance.

Barry
0
barry_heinrichs
Asked:
barry_heinrichs
  • 10
  • 3
1 Solution
 
momi_sabagCommented:
have you tried maybe to create a view that selects from the alias, and then try to access the view?
0
 
barry_heinrichsAuthor Commented:
I tried what you suggested, but that doesn't seem to work either. I received the following error message:

SQL-status: 55042
Leverancierscode: -7030
Bericht: [SQL7030] Alias AGRLP00001 for table AGRLPF in AGDT0503 not valid for statement. Cause . . . . . :   The SQL statement cannot be performed on alias AGRLP00001 because the alias refers to a member of table AGRLPF in schema AGDT0503. Recovery  . . . :   Specify a valid table or an alias that does not refer to a member.

Any other suggestions maybe?
0
 
momi_sabagCommented:
did you try to query the alias locally and it worked?
0
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

 
barry_heinrichsAuthor Commented:
Yes, I tried that and it works fine. It returns all the rows and columns I expect it to.

It seems like the alias is not a valid data object (like a table is) for the providers I used. Even an alias which doesn't retrieve data from a member is not valid.
0
 
momi_sabagCommented:
and did you try to wrap it with a user defined function or a stored procedure?
0
 
barry_heinrichsAuthor Commented:
Yes I tried to wrap it in a stored procedure, but I got the same error message as I mentioned in my initial question.
I will try to wrap it in a UDF and post the results here.
0
 
barry_heinrichsAuthor Commented:
That didn't work either. I keep getting the same error message.
I hope you have any more suggestions for me, because I really don't know what else I can try.
0
 
barry_heinrichsAuthor Commented:
I couldn't find an answer to this question and apparently none of the experts either. Thanks momi_sabag for your suggestions.
0
 
gardmanITCommented:
Hi,

I think your problem relates to using the IBM drivers in creating your linked servers.
While our experience is MS SQL2000 based we always had to create the linked servers to an AS400 DB2 databases by specifying the MS ODBC drivers and creating an ODBC connection to the AS400

Please note one linked server will give access to all libraries on the AS400 so you don't need a seperate linked server for each library you want access to, we only discovered this later.

So we set linked server up having first created an ODBC connection to the AS400 on the MS SQL server

we only specified on the first tab for the linked server....

Product Name (The hostname of our AS400)
Data Source (Name of the ODBC datasource)

On the security tab we added a user / password that was valid with all object authority of the AS400

We then used the following openquery syntacx to create linked server views on the MS SQL server

CREATE VIEW dbo.vw_LS_xxxxxxxxx
AS
SELECT     *
FROM         OPENQUERY(Linked_Server_Name,
                      'select * from AS400_Machine_Name.LibraryName.Table_Name ')
                       Rowset_1

Hope this helps.

Cheers,



0
 
barry_heinrichsAuthor Commented:
Thank you for your suggestion. Unfortunately my manager knows how to keep me busy with all sorts of unimportant jobs, so I'll try it out next monday or tuesday and I'll post my results here.

Have a nice weekend!
0
 
barry_heinrichsAuthor Commented:
Another possible solution was posted so I'd like to keep this question open a little while longer.
0
 
barry_heinrichsAuthor Commented:
I finally found the time to try and use the MS ODBC drivers and create an ODBC connection to the AS400. Unfortunately this doesn't work for me either. Creating a linked DB2 server and retrieving data is not a problem. The only problem is retrieving the data when this data is stored in members.

Thank you for your help.
0
 
barry_heinrichsAuthor Commented:
Actually it does work! Being spoiled by Microsoft I didn't think case would matter, but when I put the table name part of the sql statement in all uppercase it did work.

I used the Microsoft OLE DB Provider for ODBC Drivers (MSDASQL) to create the linked DB2 server like gardmanIT said. Then I used a sql statement like this:

select <FIELD NAMES> from <LINKED SERVER>.<DB2 DATABASE>.<DB2 SCHEMA>.<DB2 TABLE>

I put the four-part table name (server.database.schema.table) in uppercase to make it work for me. The table name can be an actual table name (physical file) or an alias.
0
 
barry_heinrichsAuthor Commented:
Thank you for sharing your knowledge gardman!
0

Featured Post

Get your Disaster Recovery as a Service basics

Disaster Recovery as a Service is one go-to solution that revolutionizes DR planning. Implementing DRaaS could be an efficient process, easily accessible to non-DR experts. Learn about monitoring, testing, executing failovers and failbacks to ensure a "healthy" DR environment.

  • 10
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now