Page 1 of 1
Invalid object name inserting into Tempory Table
Posted: Wed 07 May 2014 14:29
by keithnolan
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.
Re: Invalid object name inserting into Tempory Table
Posted: Thu 08 May 2014 11:17
by MariiaI
We couldn't reproduce this issue with the latest build of dotConnect for SQL Server 2.70.972 and this code:
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();
}
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.
Re: Invalid object name inserting into Tempory Table
Posted: Tue 07 Oct 2014 08:07
by keithnolan
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:
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
Posted: Tue 07 Oct 2014 12:09
by MariiaI
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.