Microsoft KB Archive/200797

= PRB: OPTIMIZER LOCK HINTS Error with OpenQuery =

Article ID: 200797

Article Last Modified on 2/24/2004

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition
 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q200797



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

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



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: kbbug kbdatabase kbprb KB200797

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.