Microsoft KB Archive/216519

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

Article ID: 216519

Article Last Modified on 10/31/2003

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft OLE DB Provider for SQL Server 7.0
 * Microsoft Data Access Components 2.5

-



This article was previously published under Q216519



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:

Note You must change uid= and pwd= to the correct values before you run this code. Make sure that uid has the appropriate permissions to perform this operation on the database. -- 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= ;PWD= ;' go -- add login mappings EXEC sp_addlinkedsrvlogin 'SQL60Servername', 'FALSE', NULL, ' ', ' ' 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.

