For some reason, if I create a temporary table in a SqlCommand which has
parameters, it doesn't get created properly, although no exception is
thrown and ExecuteNonQuery returns the correct number of rows.

So

cmd.CommandText = "create table #temp (col int); insert into #temp values
(1)";
cmd.Connection.Open();
cmd.ExecuteNonQuery(); // returns 1, temp table has been created in tempdb

works fine, but

cmd.CommandText = "create table #temp (col int); insert into #temp values
(@param)";
cmd.Parameters.Add("@param", 1);
cmd.Connection.Open();
cmd.ExecuteNonQuery(); // returns 1, temp table has not been created.

fails utterly.

Is there a proper reason for this or is it just another one of those things?

Intriguing question. I can't get it to work either. I'm posting a bug
against Whidbey and I'll mention that it fails in Everett too.

--
____________________________________
William (Bill) Vaughn
Author, Mentor, Consultant
Microsoft MVP
www.betav.com/blog/billva
www.betav.com
Please reply only to the newsgroup so that others can benefit.
This posting is provided "AS IS" with no warranties, and confers no rights.
__________________________________

"Mark Rendle" <markdotrendleatcomputersoftwaredotcom> wrote in message
news:(E-Mail Removed)...
> For some reason, if I create a temporary table in a SqlCommand which has
> parameters, it doesn't get created properly, although no exception is
> thrown and ExecuteNonQuery returns the correct number of rows.
>
> So
>
> cmd.CommandText = "create table #temp (col int); insert into #temp values
> (1)";
> cmd.Connection.Open();
> cmd.ExecuteNonQuery(); // returns 1, temp table has been created in tempdb
>
> works fine, but
>
> cmd.CommandText = "create table #temp (col int); insert into #temp values
> (@param)";
> cmd.Parameters.Add("@param", 1);
> cmd.Connection.Open();
> cmd.ExecuteNonQuery(); // returns 1, temp table has not been created.
>
> fails utterly.
>
> Is there a proper reason for this or is it just another one of those
> things?
>