Microsoft KB Archive/216885

= PRB: English Query Using SQL 7.0 Domain May Fail with SQL 6.5 =

Article ID: 216885

Article Last Modified on 7/18/2001

-

APPLIES TO


 * Microsoft English Query 7.0

-



This article was previously published under Q216885



SYMPTOMS
An English Query project designed with SQL 7.0 database may give a syntax error for some queries while using a SQL Server 6.5 database at run time.



CAUSE
English Query takes advantage of the new SQL 7.0 T-SQL features, and may use such new keywords as "TOP n". If you use this English Query application with SQL Server 6.5, the new keywords aren't recognized, and generate a syntax error.



WORKAROUND
There are two possible workarounds for this issue:  === Solution 1 ===

Set the version for the data connection and rebuild the EQ domain:

Set Global properties/data connection/ version for your EQ project and rebuild the EQ domain application. For example:

 Open the sample pubs EQ project. On the Edit menu, click the Gobal properties/data connection tab. Change the version to 6.5 (for example, 06.50.201). If you click Change on the data connection tab and choose a DSN for a SQL 6.5, the version field on the dialog box is updated. Rebuild the EQ domain application. </li> === Solution 2 ===

Use EQ session.SetDBMS method to generate code for SQL Server 6.5.

The SetDBMS Method sets the DBMS type and version for which the query is generated. If the server running Microsoft SQL Server is version 7.0 (or higher, when available), it allows the server to take advantage of the features in that particular version. <pre class="fixed_text">Syntax Session.SetDBMS(Type, Version) Part   Description Session An object variable representing an existing domain object. Type   A reserved integer  it must be 0 (zero). Version A string in the form of nn.nn.nnn For example: <pre class="fixed_text">objEQSession.SetDBMS(0, "06.50.201") You can use this method if you want to deploy on SQL Server 6.5 and can't change the model and rebuild.

To change the MFCEQUI sample to query in SQL Server 6.5, modify the following method in the Nlinter.cpp file by adding the following line: (*m_pSession)->SetDBMS((enum NlDBMSType)0,"0.6.50.201"); bool CNlpSession::Startup(const LPCTSTR szDomainFilename) {   IMPL_UNICODE_CONVERSION;

try { m_pSession= new _SessionPtr; m_pSession->CreateInstance(L"Mseq.Session.1"); }   catch(_com_error &e) { ReportError(e); return(false); }   try { (*m_pSession)->InitDomain(A2W(szDomainFilename)); (*m_pSession)->PutClarifySpellingErrors(true); // **** Add this line to the sample ****** (*m_pSession)->SetDBMS((enum NlDBMSType)0,"0.6.50.201"); // ***************************************       }    catch(_com_error &e) { delete(m_pSession); m_pSession = NULL; ReportError(e); return(false); }

return(true); }                       </li></ul>

<div class="moreinformation_section">

STEPS TO REPRODUCE THE BEHAVIOR:
 Run the Visual C++ MFCEQUI sample that ships with English Query.</li> Connect to a SQL 6.5 pubs database.</li>  Run the following query:

<pre class="fixed_text">"who wrote the most books ?" </li></ol>

RESULTS: The following error message results:

37000: .. Line 1: Incorrect syntax near '1'

This is the query it generates:

select top 1 with ties dbo.authors.au_fname as "First Name",

dbo.authors.au_lname as "Last Name", count(*) as "count" from

dbo.titleauthor, dbo.authors where

dbo.titleauthor.au_id=dbo.authors.au_id group by dbo.titleauthor.au_id,

dbo.authors.au_fname, dbo.authors.au_lname order by 3 desc The above query works fine for SQL Server 7.0 and fails for SQL Server 6.5 because "top 1 with ties" is a new SQL 7.0 T-SQL feature.

Keywords: kbdatabase kbprb kbfaq kbpending KB216885

-

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

© Microsoft Corporation. All rights reserved.