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.]
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.
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.
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.]
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=<username> and pwd=<strong password> 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=<username>;PWD=<strong password>;'
go
-- add login mappings
EXEC sp_addlinkedsrvlogin 'SQL60Servername', 'FALSE', NULL, '<username>', '<strong password>'
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.
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.