Congratulations to Carlos Eduardo Rojas of Planeta Networks in Miami and Fawad Mohammad of The Bank of Khyber in Peshawar, Pakistan. Carlos won first prize of $100 for the best solution to the August Reader Challenge, "The Missing Link." Fawad won second prize of $50. Watch SQL Server Magazine UPDATE for next month's Reader Challenge. In the meantime, here's the solution to August's Reader Challenge.
Problem
Debra has some data in a Jet (Microsoft Access) database that she wants to compare regularly with some SQL Server data. She thought about importing the data regularly into SQL Server, but she prefers up-to-date access through SQL Server 7.0's linked servers functionality. She established a link to the Jet database and has had no problem accessing the table though queries that use four-part naming, as with the following query (which uses "jet" as the link name and "acc_authors" as the table name):
SELECT au_id, age FROM jet...acc_authors
The link, security context, and so on work fine. However, SQL Server isn't processing some queries in the most efficient manner, so Debra would like to use the OPENQUERY function to force the Jet database engine to process the entire query. But when she executes the following query:
SELECT au_lname, au_fname, age FROM OPENQUERY(jet, 'SELECT * FROM acc_authors') AS ja JOIN authors AS sa ON ja.au_id = sa.au_id
SQL Server returns the error message
Server: Msg 155, Level 15, State 1, Line 2 'jet' is not a recognized OPTIMIZER LOCK HINTS option.
Why can Debra access the linked server through four-part naming but not through the OPENQUERY function?
Solution
SQL Server's error message isn't very helpful in this case. Some of you might have discovered that your SQL Server doesn't accept the TOP keyword and issues the following error message:
Server: Msg 170, Level 15, State 1, Line 1 Line 1: Incorrect syntax near '5'.
The configuration problem that disallows the Top keyword is the same problem that keeps Debra's OPENQUERY query from working. The problem in both cases is that the database has been configured with a compatibility level of 60 (SQL Server 6.0) or 65 (SQL Server 6.5) instead of 70 (SQL Server 7.0).
SQL Server 7.0 introduced new language elements (keywords); so, older applications have problems running under SQL Server 7.0 if they use what are now SQL Server 7.0 keywords as object names in their SQL Server 6.5 applications. For example, a SQL Server 6.5 application with a table named TOP might have problems accessing this table under SQL Server 7.0, in which TOP is a keyword. To improve compatibility between SQL Server 7.0 and older applications, Microsoft introduced the compatibility level setting. If you set a database's compatibility level lower than 70, SQL Server won't recognize the new language elements and you'll be able to refer to pre-SQL Server 7.0 objects with their original names. But if you set the compatibility level lower than 70, your applications won't be able to use SQL Server 7.0's new language elements, such as TOP and OPENQUERY.
Thus, to use OPENQUERY, Debra needs to change her SQL Server's compatibility level to 70:
EXEC sp_dbcmptlevel pubs, 70
Before making the change, though, Debra needs to thoroughly test her applications against SQL Server 7.0 to make sure the new compatibility level doesn't break them.
Alternatively, Debra can make sure that the applications that use the OPENQUERY function invoke it while connected to a database with a compatibility level of 70 (for example, master, which is always in 70 mode) because SQL Server determines compatibility level based on a connection's current database.
