Invalid object name inserting into Tempory Table

[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.

[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.

[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();
            }

[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.