StrataFrame Forum

ExecuteNonQuery issue with creating Temp Table

http://forum.strataframe.net/Topic25843.aspx

By Victor Sousa - 2/8/2010

I keep getting the following error whenever i try to access a temp table that was created using the ExecuteNonQuery method:



Invalid object name '#tList'.



I have the code within a data retrieval method of a BO.







this.FillDataTable(cmd);



if (this.MoveFirst())

{

this.ExecuteNonQuery("CREATE TABLE #tList (poitemsextended_pk int, po_no varchar(10), part_no varchar(30))");



SqlCommand tcmd = new SqlCommand();

do

{



tcmd.CommandText = "INSERT INTO #tList (poitemsextened_pk, po_no, part_no) VALUES (@poitemsextended_pk, @po_no, @part_no)";



int poiepk = this.CurrentRow["PoIEpk"] == DBNull.Value ? 0 : (int)this.CurrentRow["PoIEpk"];



tcmd.Parameters.AddWithValue("@poitemsextended_pk", poiepk).SqlDbType = SqlDbType.Int;

tcmd.Parameters.AddWithValue("@po_no", this.po_no).SqlDbType = SqlDbType.VarChar;

tcmd.Parameters.AddWithValue("@part_no", this.part_no).SqlDbType = SqlDbType.VarChar;



this.ExecuteNonQuery(tcmd);



} while (this.MoveNext());







The error is returned on this.ExecuteNonQuery(tcmd) at the end.



I've tried passing the insert as a string instead of an SqlCommad but i get the same error. I've also tried creating a global table prefixing tList with ## with same results.



Any help would be greatly appreciated.
By Trent L. Taylor - 2/8/2010

It looks as though you are trying to access a temp table between two calls.  A temp table only lasts as long as a connection is active.  As soon as a connection is killed, so is the temp table.  This is actually outside of the StrataFrame environment and pertains to how SQL Server treats a temp table and a live connection.

You are better off creating a stored procedure to encapsulate this logic so that you can do exactly what you want.  I use temp tables quite a bit and so this is sound logic.  A good example is when working within the SQL Server Management Studio.  When you create a temp table and do not close it, then run it again, you will get an error indicating that the table still exists.  However, if you close the session (which closes the connection), and then try again without having actually dropped the temp table, you will notice there is not an error.  This is because the temp tables are dropped automatically when a connection is closed.

Hope that makes sense.  Smile

By Victor Sousa - 2/8/2010

Thanks for the quick reply Trent. I thought the SQL temp tables persisted through the same SQL session ID. I have used the same sort of calls with success from VFP with SQL Passthrough. I viewed the Activity Monitor from within SQL management studio as my strataframe app ran, and it appears that the same session id is being used throughout my calls. Is that not the case? Is my understanding of the temp tables per session flawed?



thanks again.
By Trent L. Taylor - 2/9/2010

Victor,



Well, technically, a new session is created when a connection is opened. When a connection is closed, then that session ceases to exists and thus the temp tables with them. Now you could try a global temp table using the ## instead of the #, but you may run into the same issue and there is some danger of other users hitting the temp table. There are quit a few articles out on the web about this, but the one thing that I know and have proven time and time again is that if you close a connection, which a BO does after each data layer call, the temp tables will go with it.



The reason that you do not want to keep a connection open is actually very simple and easy to reproduce....performance! When you keep a connection open, you have a quick descent on performance as well as resources. So a I mentioned previously, you are better off creating a SPROC to handle this.



FYI, VFP handles temp tables and SQL passthroughs entirely differently than .NET (as does the Jet OLEDB provider if you are using Access).
By Victor Sousa - 2/9/2010

Thanks again Trent. I will follow the SPROC path.
By Trent L. Taylor - 2/9/2010

Sounds good, let us know if you have any questions as you move forward. Smile