Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
scarpen
Starting Member
3 Posts |
 Posted - 2003-01-29 : 13:08:21
|
| I am trying to query an Oracle 8i linked server from SQL Server 7.0. I have Oracle client version 8.1.6 installed on the SQL server. When I try to retrieve any data from the Oracle server, I get the following message:Server: Msg 7317, Level 16, State 1, Line 1OLE DB provider 'MSDAORA' returned an invalid schema definition.It doesn't seem to matter which tables or fields I include in the query. I'm not querying numeric data, so that bug shouldn't affect me. If I expand the linked server in Enterprise Manager, I can see the tables listed. sp_tables_ex and sp_columns_ex also return accurate data. Does anyone have any idea what could be going wrong?Thanks,Sean Carpenter |
|
|
1fred
Posting Yak Master
158 Posts |
Posted - 2003-01-29 : 14:31:03
|
| When you query a table on MS SQL like Select * from BD.dbo.author there is 3 variables affected, the catalog, the shema and the table. The catalog is your database, the schema is the user and then there is the table. I had the same problem as you do when I tried connecting a linked server from MYSQL to MSSQL, it does not suport schema. I don't know about Oracle, if it doesn't work with a linked server, you can make a staging area by importing your data with a DTS. |
 |
|
|
scarpen
Starting Member
3 Posts |
Posted - 2003-01-29 : 14:57:35
|
| For Oracle, there is no catalog, but there is a schema so the query looks like this:SELECT Column1, Column2 FROM LinkedServer..Schema.Table WHERE ....This works for other linked Oracle servers, but not for this one. I can't really do a DTS import because access to the data is needed in real-time. I have a couple of work-arounds I can use, but I was hoping to get the linked server working correctly.Thanks,Sean |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-01-29 : 15:15:13
|
| Are your other linked servers to Oracle servers configured the same way as this one? Are they all running the same versions? In other words, what is different? |
|
|
|
scarpen
Starting Member
3 Posts |
Posted - 2003-01-29 : 16:06:26
|
| I wish I knew what was different. One of the other servers that works is the same version of Oracle and the linked server is set up the same way. The Oracle server that doesn't work is controlled by another department, so I don't have the ability to see any configuration parameters on it. It sounds like I should start coding the necessary work arounds. |
|
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2003-01-29 : 17:03:52
|
| It could be that the other linked servers included an "Initial Catalog" or "Database" option in their connection strings which would eliminate the need to specify it. If you can edit the properties for your linked server, trying adding or removing the Initial Catalog setting to the connection string and see if it makes a difference. |
|
|
|
|
|
|
|
|
Query Oracle Linked Server