I'm trying to execute the following query:
select *
into #TempTable
from mytable
select * from #TempTable
drop table #TempTable
My code is like:
var com = new SqlCommand();
com.CommandType = CommandType.Text;
DbDataReader dbRed = com.ExecuteReader();
com.CommandText = queryString;
I've set MultipleActiveResultSets to true on the connection. The query works fine using a standard .net SQL server driver just not the Devart one
I get an error stating Invalid object name #TempTable.
Invalid object name inserting into Tempory Table
-
- Posts: 10
- Joined: Fri 25 Mar 2011 15:17
- Location: Please select
Re: Invalid object name inserting into Tempory Table
We couldn't reproduce this issue with the latest build of dotConnect for SQL Server 2.70.972 and this code:
Please try this code and tell us about the results. If this doesn't help, please send us your sample project so that we are able to investigate this issue in more details.
Code: Select all
SqlConnection connection = new SqlConnection(@"Data Source=DBMSSQL\MSSQL2008R2;Integrated Security=False;User ID=user_id;database=test_db;MultipleActiveResultSets=true");
connection.Open();
string queryString = "select * into #TempTable from Cars;select * from #TempTable; drop table #TempTable";
var com = new SqlCommand();
com.CommandType = CommandType.Text;
com.Connection = connection;
com.CommandText = queryString;
DbDataReader dbRed = com.ExecuteReader();
while (dbRed.Read())
{
for (int i = 0; i < dbRed.FieldCount; i++)
Console.Write(dbRed[i] + "\t");
Console.WriteLine();
}
-
- Posts: 10
- Joined: Fri 25 Mar 2011 15:17
- Location: Please select
Re: Invalid object name inserting into Tempory Table
This issue has come up again for me but only when I'm using schema mode. I'm using version 2.50.483.0.
The following code will return an error:
The following code will return an error:
Code: Select all
SqlConnection connection = new SqlConnection(@"Data Source=DBMSSQL\MSSQL2008R2;Integrated Security=False;User ID=user_id;database=test_db;MultipleActiveResultSets=true");
connection.Open();
string queryString = "select * into #TempTable from Cars;select * from #TempTable; drop table #TempTable";
var com = new SqlCommand();
com.CommandType = CommandType.Text;
com.Connection = connection;
com.CommandText = queryString;
DbDataReader dbRed = com.ExecuteReader(CommandBehavior.SchemaOnly);
while (dbRed.Read())
{
for (int i = 0; i < dbRed.FieldCount; i++)
Console.Write(dbRed[i] + "\t");
Console.WriteLine();
}
Re: Invalid object name inserting into Tempory Table
This issue has been discussed here.
We have reproduced the issue with the CommandBehavior.SchemaOnly parameter of the ExecuteReader method. However this issue is reproducible with System.Data.SqlClient provider as well.
dotConnect for SQL Server is based on System.Data.SqlClient, and if the issue is reproducible with the System.Data.SqlClient provider, it will be reproduced with dotConnect for SQL Server too, so please contact the Microsoft support on this question.
We have reproduced the issue with the CommandBehavior.SchemaOnly parameter of the ExecuteReader method. However this issue is reproducible with System.Data.SqlClient provider as well.
dotConnect for SQL Server is based on System.Data.SqlClient, and if the issue is reproducible with the System.Data.SqlClient provider, it will be reproduced with dotConnect for SQL Server too, so please contact the Microsoft support on this question.