Microsoft KB Archive/235053

= PRB: E_FAIL Returned from Prepare When SQL Statement Contains a Parameter in a Subquery =

Article ID: 235053

Article Last Modified on 11/3/2003

-

APPLIES TO

 Microsoft OLE DB Provider for SQL Server 7.01, when used with:  Microsoft Data Access Components 2.0

 Microsoft Data Access Components 2.1

 Microsoft Data Access Components 2.5

 Microsoft Data Access Components 2.6</li></ul>

 Microsoft Data Access Components 2.7</li></ul> </li> Microsoft OLE DB Provider for SQL Server 7.0, when used with:  Microsoft Data Access Components 2.0</li></ul>

 Microsoft Data Access Components 2.1</li></ul>

 Microsoft Data Access Components 2.5</li></ul>

 Microsoft Data Access Components 2.6</li></ul>

 Microsoft Data Access Components 2.7</li></ul> </li></ul>

-

<div class="notice_section">

This article was previously published under Q235053

<div class="symptoms_section">

SYMPTOMS
Using the OLE DB Provider for SQL Server and calling the ATL CCommand::Prepare function, or ICommandPrepare::Prepare, returns E_FAIL with the following error description:

Syntax error or access violation.

The SQL Server OLE DB Provider that ships with MDAC 2.7 returns the following improved error message:

Parameter Information cannot be derived from SQL statements with sub-select queries. Set parameter information before preparing command.

<div class="cause_section">

CAUSE
The SQL command text for the OLE DB Command object contains a parameter in a subquery. For example, the following SQL statement causes the error:

SELECT * FROM Table1 WHERE field2 IN <BR/> (SELECT field1 FROM Table2 where field1 > ?)

The Microsoft OLE DB provider for SQL Server cannot derive parameter information from subqueries.

<div class="resolution_section">

RESOLUTION
You must call CCommand::SetParameterInfo or ICommandWithParameters::SetParameterInfo before calling Prepare.

Please see the MORE INFORMATION section of this article for sample code that uses the ATL OLE DB consumer templates and demonstrates calling SetParameterInfo.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

Steps to Reproduce Behavior
<ol> Create two tables on a Microsoft SQL Server database: Table1 should have a character field and an integer field, Table2 should have an integer field only.</li>  Create an accessor class that resembles the following:

class CQuery1Accessor { public: TCHAR m_field1[11]; LONG m_field2; LONG m_field1param;

BEGIN_COLUMN_MAP(CQuery1Accessor) COLUMN_ENTRY(1, m_field1) COLUMN_ENTRY(2, m_field2)

END_COLUMN_MAP

BEGIN_PARAM_MAP(CQuery1Accessor) COLUMN_ENTRY(1,m_field1param) END_PARAM_MAP DEFINE_COMMAND(CQuery1Accessor, _T("SELECT * FROM Table1 WHERE field2 IN" "(SELECT field1 FROM Table2 where field1 > ?)"));

// You may want to call this function if you are inserting a record and want to   // initialize all the fields, if you are not going to explicitly set all of them. void ClearRecord {       memset(this, 0, sizeof(*this)); } };

</li>  Create a CCommand class that executes the command using the accessor class you just created. The CCommand class should resemble the following:

class CQuery1 : public CCommand<CAccessor<CQuery1Accessor> > { public: HRESULT Open {       HRESULT     hr;

hr = OpenDataSource; if (FAILED(hr)) return hr;

return OpenRowset; }   HRESULT OpenDataSource {       HRESULT     hr; CDataSource db; CDBPropSet dbinit(DBPROPSET_DBINIT);

dbinit.AddProperty(DBPROP_AUTH_PERSIST_SENSITIVE_AUTHINFO, false); dbinit.AddProperty(DBPROP_INIT_CATALOG, OLESTR("yourdatabase")); dbinit.AddProperty(DBPROP_INIT_DATASOURCE, OLESTR("yourserver")); dbinit.AddProperty(DBPROP_INIT_LCID, (long)1033); dbinit.AddProperty(DBPROP_INIT_PROMPT, (short)4); dbinit.AddProperty(DBPROP_AUTH_PASSWORD, ""); dbinit.AddProperty(DBPROP_AUTH_USERID, "sa");

hr = db.Open(_T("SQLOLEDB"), &dbinit); if (FAILED(hr)) return hr;

return m_session.Open(db); }   HRESULT OpenRowset {       // Set properties for open LPCTSTR szCommand = NULL; HRESULT hr = S_OK; hr = _CommandClass::GetDefaultCommand(&szCommand); if (FAILED(hr)) return hr; hr = CreateCommand(m_session); if (SUCCEEDED(hr)) {           CComPtr<ICommandText> spCommandText; hr = m_spCommand->QueryInterface(&spCommandText); USES_CONVERSION; if (SUCCEEDED(hr)) hr = spCommandText->SetCommandText(DBGUID_SQL, T2COLE(szCommand)); } /* -- Adding this code prevents the error: ULONG ulOrds[]={1}; DBPARAMBINDINFO ParamInfo[1];

ParamInfo[0].pwszDataSourceType = (unsigned short *) L"DBTYPE_I4"; ParamInfo[0].bPrecision = 10; ParamInfo[0].bScale = 0; ParamInfo[0].dwFlags = DBPARAMFLAGS_ISINPUT; ParamInfo[0].pwszName = NULL;

ParamInfo[0].ulParamSize = sizeof(LONG);

hr = SetParameterInfo(1, ulOrds, ParamInfo); hr = Prepare; //// hr = E_FAIL without SetParameterInfo call if (E_FAIL == hr) {           AtlTraceErrorRecords; return E_FAIL; }

m_field1param = 0; hr = CCommand<CAccessor<CQuery1Accessor> >::Open; return hr; }   CSession    m_session; };

In the code above, remove the comments around the code that calls SetParameterInfo to prevent Prepare from returning E_FAIL. </li> <li> Finally, add some code in your application that opens the query:

CoInitialize(NULL); CQuery1 rs; rs.Open; rs.MoveFirst; </li></ol>

Keywords: kbconsumer kbdatabase kbprb kbdtl KB235053

-

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

© Microsoft Corporation. All rights reserved.