PRB: OPTIMIZER LOCK HINTS Error with OpenQuery

ID: Q200797

The information in this article applies to:

Microsoft SQL Server version 7.0

SYMPTOMS

The following error may occur when you perform a distributed query with the OpenQuery() function:

Server: Msg 155, Level 15, State 1, Line 1
'<Linked-Server Name>' is not a recognized OPTIMIZER LOCK HINTS option.

CAUSE

The error occurs if you have changed the compatibility level of a SQL Server 7.0 database to 6.5 version.

RESOLUTION

Run sp_dbcmptlevel and set the compatibility level to 70.

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

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:

Server: Msg 170, Level 15, State 1, Line 2 Line 2: Incorrect syntax near 'Microsoft.Jet.OLEDB.4.0'.

Additional query words: kbDSupport

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