Microsoft KB Archive/295743

= PRB: SQL Server Local Temp Tables Aren't Created When Parameterized ADO Query Is Used =

Article ID: 295743

Article Last Modified on 11/30/2007

-

APPLIES TO

 Microsoft ActiveX Data Objects 2.0, when used with:  Microsoft SQL Server 2000 Standard Edition

 Microsoft SQL Server 7.0 Enterprise Edition

 Microsoft SQL Server 7.0 Standard Edition  Microsoft ActiveX Data Objects 2.1, when used with:  Microsoft SQL Server 2000 Standard Edition</li></ul>

 Microsoft SQL Server 7.0 Enterprise Edition</li></ul>

 Microsoft SQL Server 7.0 Standard Edition</li></ul> </li> Microsoft ActiveX Data Objects 2.5, when used with:  Microsoft SQL Server 2000 Standard Edition</li></ul>

 Microsoft SQL Server 7.0 Standard Edition</li></ul> </li> Microsoft ActiveX Data Objects 2.6, when used with:  Microsoft SQL Server 2000 Standard Edition</li></ul>

 Microsoft SQL Server 7.0 Enterprise Edition</li></ul>

<ul> <li>Microsoft SQL Server 7.0 Standard Edition</li></ul> </li> <li>Microsoft OLE DB Provider for SQL Server 7.0</li> <li>Microsoft OLE DB Provider for SQL Server 7.01</li> <li>Microsoft ODBC Driver for Microsoft SQL Server 3.7</li> <li>Microsoft ODBC Driver for Microsoft SQL Server 3.6</li> <li>Microsoft ODBC Driver for Microsoft SQL Server 3.7</li> <li>Microsoft OLE DB Provider for ODBC 1.0</li> <li>Microsoft OLE DB Provider for ODBC 1.1</li> <li>Microsoft OLE DB Provider for ODBC 1.5</li> <li>Microsoft OLE DB Provider for ODBC 2.0</li></ul>

-

<div class="notice_section">

This article was previously published under Q295743

<div class="symptoms_section">

SYMPTOMS
When you run a query against a temporary table created by a parameterized ActiveX Data Objects (ADO) query, the following error may result:

Error -2147217865 &quot;Invalid Object name '# '

This problem occurs with both the Microsoft OLEDB Provider for SQL Server (SQLOLEDB) and the Microsoft OLEDB Provider for ODBC Driver (MSDASQL) with the SQL Server driver.

<div class="cause_section">

CAUSE
ADO is designed to provide high performance with parameterized queries. The architecture it uses, however, prevents temporary tables that are created within a parameterized query from persisting outside of that query.

<div class="resolution_section">

RESOLUTION
Several workarounds are presented in the &quot;More Information&quot; section of this article.

<div class="status_section">

STATUS
This behavior is by design.

<div class="moreinformation_section">

MORE INFORMATION
The following Microsoft Visual Basic code demonstrates the problem:

Note You must change the User ID value and the Password = value to the correct values before you run this code. Make sure that User ID has the appropriate permissions to perform this operation on the database. Dim cn As New ADODB.Connection Dim Cmd As New ADODB.Command Dim Rs As New ADODB.Recordset

cn.Open &quot;Provider=SQLOLEDB.1;Persist Security Info=False;User ID= ; Password= ;Initial Catalog=pubs;Data Source=<INSERT YOUR SERVER NAME HERE!>&quot;

cn.Execute &quot;SET NOCOUNT ON&quot; Cmd.CommandType = adCmdText Cmd.Parameters.Append Cmd.CreateParameter(&quot;Par&quot;, adVarChar, adParamInput, 2, &quot;CA&quot;) Cmd.CommandText = &quot;SELECT au_id INTO #Pattern FROM authors where state = ? &quot; Set Cmd.ActiveConnection = cn

Cmd.Execute

Cmd.CommandText = &quot;select count(*) from #Pattern&quot; Rs.Open Cmd,, adOpenStatic, adLockOptimistic     ' This line generates error. MsgBox Rs.Fields(0).Value

Rs.Close cn.Close Set cn = Nothing To work around the problem, you can do the following things: <ol> <li> Use a nonparameterized query to create the temporary table, such as the following: SELECT au_id INTO #Pattern FROM authors WHERE state = 'CA' </li> <li> Use the temporary table in the same batch in which the table is created; for example: Dim cn As New ADODB.Connection Dim Cmd As New ADODB.Command Dim Rs As New ADODB.Recordset

cn.Open &quot;Provider=SQLOLEDB.1;Persist Security Info=False;User ID= ; Password= ; Initial Catalog=pubs;Data Source=reidwpri&quot;

cn.Execute &quot;SET NOCOUNT ON&quot; Cmd.CommandType = adCmdText Cmd.Parameters.Append Cmd.CreateParameter(&quot;Par&quot;, adVarChar, adParamInput, 2, &quot;CA&quot;) Cmd.CommandText = &quot;SELECT au_id INTO #Pattern FROM authors where state = ? &quot; & _ &quot;select count(*) from #Pattern&quot; Set Cmd.ActiveConnection = cn

Rs.Open Cmd,, adOpenStatic, adLockOptimistic     ' This line generates error. MsgBox Rs.Fields(0).Value

Rs.Close cn.Close Set cn = Nothing </li> <li> Use global temporary tables instead of local temporary tables: Dim cn As New ADODB.Connection Dim Cmd As New ADODB.Command Dim Rs As New ADODB.Recordset

cn.Open &quot;Provider=SQLOLEDB.1;Persist Security Info=False;User ID= ;Password= ;Initial Catalog=pubs;Data Source=reidwpri&quot;

cn.Execute &quot;SET NOCOUNT ON&quot; Cmd.CommandType = adCmdText Cmd.Parameters.Append Cmd.CreateParameter(&quot;Par&quot;, adVarChar, adParamInput, 2, &quot;CA&quot;) Cmd.CommandText = &quot;SELECT au_id INTO ##Pattern FROM authors where state = ? &quot; Set Cmd.ActiveConnection = cn Cmd.Execute

Cmd.CommandText = &quot;SELECT count(*) from ##Pattern&quot; Rs.Open Cmd,, adOpenStatic, adLockOptimistic     ' This line generates error. MsgBox Rs.Fields(0).Value

Rs.Close cn.Close Set cn = Nothing </li></ol>

Keywords: kbprb KB295743

-

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

© Microsoft Corporation. All rights reserved.