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_ALI
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
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_ALI
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
momi_sabag
have you tried maybe to create a view that selects from the alias, and then try to access the view?
ASKER
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?
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?
did you try to query the alias locally and it worked?
ASKER
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.
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.
and did you try to wrap it with a user defined function or a stored procedure?
ASKER
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.
I will try to wrap it in a UDF and post the results here.
ASKER
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.
I hope you have any more suggestions for me, because I really don't know what else I can try.
ASKER
I couldn't find an answer to this question and apparently none of the experts either. Thanks momi_sabag for your suggestions.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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!
Have a nice weekend!
ASKER
Another possible solution was posted so I'd like to keep this question open a little while longer.
ASKER
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.
Thank you for your help.
ASKER
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.ta
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.ta
ASKER
Thank you for sharing your knowledge gardman!