sql server reporting services: Hi!
We are try to use oracle from MS SQL Server 2005.WeI have 2 problem:
1.I need to execute next oracle statement before each select:
begin dbms_application_info.set_client_info('87');end;
How to do it from ms sql syntax?
2.I tried to define linked server to oracle and every time that I
execute select statement i got error:
returned an invalid column definition for table ...
I can not view tables under my linked server.
Thanks

[quoted text, click to view]

Hi

1. Have you tried just to run the command as a normal SQL query? I'm not
that familiar with ORACLE commands so I don't know what the
dbms_application_info.set_client_info('87') does, but it sounds like
it's just a setting that are being changed?

2.You might have to check it you've spelled the tablenemes correctly. I
don't know if it's true for all ORACLE servers, but the one I'm
occasionally working with is case sensitive and if memory seres me, I
think I get the same error message as you do when I forget about the
case sensitivity.
If you have problems viewing the tables under your linked server, the
following might help you a little bit.


1. Run sp_tables_ex 'YourOracleLinkedserverName'. The result will be a
number of rows showing TABLE_CAT, TABLE_SCHEM, TABLE_NAME, TABLE_TYPE,
REMARKS. Here you can find the table/view you want to get the info for.
In my case the TABLE_CAT column are NULL in all cases, but I have values
in the TABLE_SCHEM,TABLE_NAME and TABLE_TYPE column.

2. If I want to get the details of a specific table, I look up the table
by using sp_tables_ex as above, and then find the TABLE_SCHEM and
TABLE_NAME in the result. In my case, I have a table called ATTRIBUTE$
belonging to the TABLE_SHEM "SYS".
If I want to see the details of this table, I run:

sp_columns_ex 'YourOracleLinkedServerName', 'ATTRIBUTE$', 'SYS'

This will then show you the fields in the table.


Regards