October 27, 2008 at 9:56 am
We're having the same problem. When trying to connect to a server using ODBC Connection (Progress), we receive the same server NULL error message. It works FINE under sql 2000. I can however query the DB through the connection and pull data, the problem is knowing the table names, etc. I have to keep our 2000 server around just to view the schema.
has anyone found an answer to this?
I'm also trying to IMPORT data using ODBC although Microsoft, the morons that be, took away the easy IMPORT option using DTS.. Not there is an option to create a .NET ODBC connection, but I can't find anything ANYWHERE as to what needs to be in the confguration for this .net setup.
December 8, 2008 at 12:31 pm
Hi,
Have you solved the linked server issue you were having? If so, can you please let me know what was the issue as I am running into same error.
Thanks,
Lava
December 26, 2008 at 12:33 pm
hi i am having the exact same error, did you fix your problem?
March 11, 2009 at 10:25 pm
I have almost the same problem, though I can perhaps answer kwhite.
SS2000 let you do a fair bit with linked servers via ODBC, including replicate to "unsupported" databases.
We have IBM's DB2, and had used ODBC to link to it for Push replication in Sql Server.
Under SS2005, you can't do that with ODBC any more, presumably to make people like us buy Enterprise Edition or Host Integration Services. Well, we bought a single-app licence of Biztalk as the cheapest package that includes HIS and Microsoft's DB2 client.
My problem: even using that, I get the "cannot obtain schema rowset" error that everyone else in this thread has.
No-one's found an answer yet?
April 23, 2009 at 7:57 am
Surely someone has come across a solution to the original problem by now?
This thread has been alive for 2 years.... Anybody..?
August 5, 2009 at 10:03 am
I'm having the same error except my error # is 7311. I'm using an ODBC connection to a progress database.. On our 2000 SQL server, it works great no problems.. When I try to expand the catalog on our 2005 Server i get the dreaded cannot obtain the schema rowset...etc...
HELP!!!!!
July 29, 2010 at 11:47 am
Had a similar problem with running SQL Server 2008 SP1 32bit to Oracle9 and received the Error 7399 for "Cannot initialize the data source object of OLE DB provider 'OraOLEDB.Oracle' for linked server <servername>."
I could get the Microsoft OLE DB Provider for Oracle working but I read this is suppose to be discontinued in the future plus it doesn't support 64bit, which is what we really need.
Hope this info is useful to someone.
********************************
Found setup instructions on Oracle support:
The purpose of this document is to show the steps needed to create a linked server in Microsoft SQL Server to use either the Microsoft SQL Analyzer query tool (32-bit) or Microsoft SQL Server Management Studio with the Oracle Provider for OLE DB. This will enable the user to use the 4 part query naming convention in SQL Analyzer/Management Studio.
Instructions to Setup the 32-bit / 64-bit Linked Server Connection
in SQL Server 2005 or SQL Server 2008 Using The Oracle Provider for OLE DB:
Open SQL Server Management Studio by going to
START -> ALL PROGRAMS -> Microsoft SQL Server 2005 or Microsoft SQL Server 2008
Provide the Server Name, Authentication, Username and Password to connect to your SQL Server at the dialog box. Leave the Server Type as Database Engine. Click on Connect. This will authenticate you and log you into Management Studio.
Click on the + to expand the tree for the <Machine Name> SQL Server. Where <Machine Name> is the name of the machine where Microsoft SQL Server is installed on.
The first level of the tree is called <Machine Name> SQL Server,
The second level of the tree is called Linked Servers,
The third level of the tree is called Providers.
Now a list of Providers are shown. Right click on the OraOLEDB.Oracle, which is the Oracle Provider for OLEDB, and select Properties.
Check the check boxes for "Dynamic Parameters" and "Allow InProcess". Click on OK. The dialogue box will close.
Right click on the Linked Servers folder and choose New Linked Server.
In the New Linked Server Properties dialogue box, do the following:
Type in a Linked Server Name, for this example, we will call it Tiger.
For Server Type, select Other data source.
For Provider, select Oracle Provider for OLE DB.
In the Data Source field, type in the Oracle Service name (SQL Net Alias).
In the Product Name field, type in Oracle.
Leave the Provider String field blank
Click on the Security option in the left pane.
Select the radio button "Be made using this security context" at the bottom of the box.
Type in the User ID (your Oracle login), ie. Scott in the Remote login field and your Password, ie. Tiger in the With Password field.
Click on the Server Options in the left pane.
While leaving the default selections selected, click on "Collation Compatible". Select "True" from the drop down list.
Click OK.
This will create your Linked Server named Tiger.
Instructions to Setup the 32-bit / 64-bit Linked Server Connection
in SQL Server 2005 or SQL Server 2008 Using ODBC:
Open SQL Server Management Studio by going to
START -> ALL PROGRAMS -> Microsoft SQL Server 2005 or Microsoft SQL Server 2008
Provide the Server Name, Authentication, Username and Password to connect to your SQL Server at the dialog box. Leave the Server Type as Database Engine. Click on Connect. This will authenticate you and log you into Management Studio.
Click on the + to expand the tree for the <Machine Name> SQL Server. Where <Machine Name> is the name of the machine where Microsoft SQL Server is installed on.
The first level of the tree is called <Machine Name> SQL Server,
The second level of the tree is called Linked Servers,
The third level of the tree is called Providers.
Now a list of Providers are shown. Right cliek on the MSDASQL, which is the Microsoft OLE DB Provider for ODBC Drivers and select Properties.
Note: If the setup is being executed on Windows 2003 64-bit or Windows XP 64-bit with Sql Server 2005 MSDASQL (Microsoft OLE DB Providers for ODBC Drivers) is not automatically installed. It can be downloaded from Microsoft's website at this address:
Check the check boxes for "Dynamic Parameters" and "Allow InProcess". Click on OK. The dialogue box will close.
Right click on the Linked Servers folder and choose New Linked Server.
In the New Linked Server Properties dialogue box, do the following:
Type in a Linked Server Name, for this example, we will call it Tiger.
For Server Type, select Other data source.
For Provider, select Microsoft OLE DB Provider for ODBC Drivers.
In the Data Source field, type in the ODBC System DSN.
In the Product Name field, type in Oracle.
Leave the Provider String field blank
Click on the Security option in the left pane.
Select the radio button "Be made using this security context" at the bottom of the box.
Type in the User ID (your Oracle login), ie. Scott in the Remote login field and your Password, ie. Tiger in the With Password field.
Click on the Server Options in the left pane.
While leaving the default selections selected, click on "Collation Compatible". Select "True" from the drop down list.
Click OK.
This will create your Linked Server named Tiger.
Test the 32-bit / 64-Bit Linked Server Connection with SQL Server 2005 or SQL Server 2008
Click on New Query button in SQL Server Management Studio.
In the Query screen you can type a query. The query will follow this format:
SELECT * FROM <Linked Server Name>..<Oracle user name in all CAPS>.<Oracle
Table Name in all CAPS>
IE. SELECT ENAME FROM TIGER..SCOTT.EMP
Click on the ! Execute icon to run the query.
The results from the query will be displayed under the Results tab at the bottom of the screen.
May 31, 2011 at 2:32 pm
Thank you. This was very helpful.
Toni
Viewing 8 posts - 16 through 22 (of 22 total)
You must be logged in to reply to this topic. Login to reply