can ado.net query a #temp table in sql server?

[Guest]

Hello,

If I say this:
Dim cmd As New SqlCommand("select top 10 * from tbl1, conn)
conn.Open()
Dim reader As SqlDataReader = cmd.ExecuteReader()

everything is OK. But if I try this:
Dim cmd As New SqlCommand
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select top 10 * Into #temp1 From tbl1") <---complains here
cmd.ExecuteNonQuery()
cmd.CommandText = "Select * From #temp1"
Dim reader As SqlDataReader = cmd.ExecuteReader()

..Net complains. Is there a way to use #temp tables with ado.net? How to do
this?

Thanks,
Rich

 

[Sericinus hunter]

Rich wrote:
....

Dim cmd As New SqlCommand
conn.Open()
cmd.Connection = conn
cmd.CommandType = CommandType.Text
cmd.CommandText = "Select top 10 * Into #temp1 From tbl1") <---complains here
cmd.ExecuteNonQuery()
cmd.CommandText = "Select * From #temp1"
Dim reader As SqlDataReader = cmd.ExecuteReader()

.Net complains. Is there a way to use #temp tables with ado.net? How to do
this?Rich

The table does not exist. Try to create it first, do your operation and
then drop it, all in one command string:

cmd.CommandText = "create table #temp1 (col1 int, col2 int);"
cmd.CommandText += "select top 10 col1, col2 into #temp1 from tbl1;"
cmd.CommandText += "select * from #temp1;"
cmd.CommandText += "drop table #temp1;"

This is a guess, I haven't tried it.

 

[Guest]

Nevermind. I had a typo in my actual code:

cmd.CommandType = "Select top 10 * Into #temp1 From tbl1"

apparently ado.net does fine with #temp tables. That is great!

 

[Sericinus hunter]

Rich wrote:
...

The table does not exist. Try to create it first, do your operation and
then drop it, all in one command string:

cmd.CommandText = "create table #temp1 (col1 int, col2 int);"
cmd.CommandText += "select top 10 col1, col2 into #temp1 from tbl1;"
cmd.CommandText += "select * from #temp1;"
cmd.CommandText += "drop table #temp1;"

This is a guess, I haven't tried it.

Sorry, I was wrong, the temp table does not need to exist, SELECT ... INTO
command will create it.