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

MORE INFORMATION

The following Microsoft Visual Basic code demonstrates the problem:

Note You must change the User ID <username> value and the Password =<strong 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 "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<user name>; Password=<strong password>;Initial Catalog=pubs;Data Source=<INSERT YOUR SERVER NAME HERE!>"

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

Cmd.Execute

Cmd.CommandText = "select count(*) from #Pattern"
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:

1. Use a nonparameterized query to create the temporary table, such as the following:

   SELECT au_id INTO #Pattern FROM authors WHERE state = 'CA'
   					

2. 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 "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<user name>; Password=<strong password>; Initial Catalog=pubs;Data Source=reidwpri"
   
   cn.Execute "SET NOCOUNT ON"
   Cmd.CommandType = adCmdText
   Cmd.Parameters.Append Cmd.CreateParameter("Par", adVarChar, adParamInput, 2, "CA")
   Cmd.CommandText = "SELECT au_id INTO #Pattern FROM authors where state = ? " & _
       "select count(*) from #Pattern"
   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
   					

3. 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 "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=<user name>;Password=<strong password>;Initial Catalog=pubs;Data Source=reidwpri"
   
   cn.Execute "SET NOCOUNT ON"
   Cmd.CommandType = adCmdText
   Cmd.Parameters.Append Cmd.CreateParameter("Par", adVarChar, adParamInput, 2, "CA")
   Cmd.CommandText = "SELECT au_id INTO ##Pattern FROM authors where state = ? "
   Set Cmd.ActiveConnection = cn
   Cmd.Execute
   
   Cmd.CommandText = "SELECT count(*) from ##Pattern"
   Rs.Open Cmd, , adOpenStatic, adLockOptimistic      ' This line generates error.
   MsgBox Rs.Fields(0).Value
   
   Rs.Close
   cn.Close
   Set cn = Nothing