CODEWITHSUNDEEP
sql-servervisual-studio
SeaDrive
SeaDrive

Reputation: 4292

Temp table trouble in SQL Server

I've used temporary tables before without any trouble, but today, they are not working for me. This returns

. #MyTemp not found

from the last line.

scBld.CommandText = "select top 10 * into #MyTemp from elig_feeds";
scBld.ExecuteNonQuery();
scBld.CommandText = "select count(*) from  #MyTemp";
int p = (int) scBld.ExecuteScalar();

If I remove the "#"s, it works fine.

The only thing that has changed recently is version compatibility of the database, but I don't see that would be a factor. The db is 2005 developer edition.

Thx.

Upvotes: 0

Views: 237

Answers (2)

Oleg Sesov
Oleg Sesov

Reputation: 31

I had similar issue today with 2005 Express both using ODBC and OLE DB. As explained in this article this behavior might be due to using prepared statements, which are wrapped into temporal stored procedures when prepared.

In SQL Server 2005, SQL Server 2000, and SQL Server 7.0, the prepared statements cannot be used to create temporary objects and cannot reference system stored procedures that create temporary objects, such as temporary tables. These procedures must be executed directly.

Supplying statements directly using SQLExecDirect did help to fix the application. Not sure how that should be applied to ADO.NET though.

Upvotes: 2

ImplexOne
ImplexOne

Reputation: 549

Check if connection is getting closed automatically. You are executing two different commands, depending on connection settings, it may get reset after you call ExecuteNonQuery().

[Temp tables are destroyed when connection is closed.]

Upvotes: 1

Related Questions