PRB: OPTIMIZER LOCK HINTS Error with OpenQuery

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')
				

Steps to Reproduce the Error

You can run the following script in SQL Query Analyzer to see the error. You may want to change the path to a Microsoft Access Database (.mdb) file as appropriate on your computer that is running SQL Server.

Use Pubs
go
-- Set up and use a Linked Server
EXEC sp_addlinkedserver
   'Access1',
   '',
   'Microsoft.Jet.OLEDB.4.0',
   'd:\program files\microsoft office\office\samples\northwind.mdb',
   NULL,
   NULL
go
sp_addlinkedsrvlogin 'Access1', 'FALSE', NULL, 'Admin', Null
go

sp_dbcmptlevel 'pubs', 65
go
Select * from OpenQuery (Access1, 'Select * from orders' )
go
-- The preceding query should give an error.
-- 'Access1' is not a recognized OPTIMIZER LOCK HINTS option.

sp_dbcmptlevel 'pubs', 70
go
-- The following query should work fine.
Select * from OpenQuery (Access1, 'Select * from orders' )
				

Note that a distributed query with a four part name works in 6.x and 7.0 mode. A distributed query with OpenRowset fails with a different error when run in 6.x mode. For example:

SELECT *
From OpenRowset('Microsoft.Jet.OLEDB.4.0',
'd:\program files\microsoft office\office\samples\northwind.mdb';'admin';'', orders)
				

Gives this error: