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

  • Thread starter Thread starter Guest
  • Start date Start date
G

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
 
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
Click to expand...

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.
 
Nevermind. I had a typo in my actual code:
cmd.CommandType = "Select top 10 * Into #temp1 From tbl1"
Click to expand...

apparently ado.net does fine with #temp tables. That is great!
 
Sericinus said:
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.
Click to expand...

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