PRB: SQL Server 6.0 Does Not Work with SQLOLEDB Provider in a SQL Server 7.0 Distributed Query

ID: Q216519

The information in this article applies to:

Microsoft SQL Server version 7.0
Microsoft OLE DB Provider for SQL Server, version 7.0

SYMPTOMS

The SQLOLEDB provider returns the following error message when accessing a SQL Server 6.0 in a SQL Server 7.0 distributed query:

Server: Msg 7399, Level 16, State 1, Line 1 OLE DB provider 'SQLOLEDB' reported an error. [OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]

CAUSE

The SQLOLEDB provider was not designed to work with SQL Server version 6.0, and therefore distributed queries do not work through this provider.

The SQL Server 7.0 Books Online section on "System Requirements for the OLE DB Provider for SQL Server" states:

SQL Server Requirements: To use SQLOLEDB to access data in SQL Server databases, you must have SQL Server version 6.5 or later.

RESOLUTION

To work around the error you first must run the updated Instacat.sql script on the SQL Server 6.0 machine. The updated Instacat.sql script ships with SQL Server 7.0. After applying the script, instead of using the SQLOLEDB provider, you can use the OLEDB provider for ODBC with the SQL Server ODBC driver to query SQL Server 6.0.

STATUS

This is by design.

MORE INFORMATION

Microsoft SQL Server version 7.0 provides the ability to perform queries against OLE DB providers. This is done by using the OpenQuery or OpenRowset Transact-SQL functions or by using a query with four-part names including a linked-server name. For example:


sp_addlinkedserver 'mylinkedserver', 'product_name', 'myoledbprovider',
'data_source','location', 'provider_string', 'catalog'

SELECT * FROM OPENQUERY(mylinkedserver, 'select * from table1')

To reproduce the errors, run the following code sample, where "SQL60Servername" represents the name of your SQL 6.0 server:


EXEC sp_addlinkedserver 'SQL60Servername'
go
Select * from SQL60Servername.pubs.dbo.authors

This will give the following error:

Server: Msg 7399, Level 16, State 1, Line 1
OLE DB provider 'SQLOLEDB' reported an error.
[OLE/DB provider returned message: The data source can not be used, because it DBMS version is less than 6.5.0.]

NOTE: The same error message occurs before and after applying the Instcat.sql file, which ships with SQL 7.0, to the SQL 6.0 machine.

The following block of code shows how to use the OLEDB provider for ODBC to add the SQL Server 6.0 as a distributed query:


-- Add a SQL 60 server with the OLEDB provider for ODBC
EXEC sp_addlinkedserver 'SQL60Servername', ' ', 'MSDASQL', NULL,NULL,
	'Driver={SQL Server};Database=pubs;Server=SQL60Servername;UID=sa;PWD=;'
go
-- add login mappings
EXEC sp_addlinkedsrvlogin 'SQL60Servername', 'FALSE', NULL, 'sa', ''
go
Select * from SQL60Servername.pubs.dbo.authors

If you have not applied the SQL 7.0 Instcat.sql file to SQL 6.0 server, the above query will return the following error:

Server: Msg 7356, Level 16, State 1, Line 1 OLE DB provider 'MSDASQL' supplied inconsistent metadata for a column. Metadata information was changed at execution time.

REFERENCES

For more details on setting up and using Distributed Queries, search on the following sp_addlinkedserver, OpenQuery, OpenRowset and related topics in SQL 7.0 Books Online.

Additional query words: kbDSupport

Keywords : kbDatabase kbOLEDB kbSQLServ kbVC kbSQLServ600 kbSQLServ700
Version : WINDOWS:7.0; winnt:7.0
Platform : WINDOWS winnt
Issue type : kbprb