Entity Framework connection not recognizing Temp Table created

Question

I've got a process that takes records from a daily feed in an Access database and adds the records to a sql server.

The load occationally fails, so I'm trying to add via a SQL server Global Temp Table and then a much quicker Delete/Insert from that temp table.

I've got the following code that works in TSQL:

IF OBJECT_ID ( 'tempdb.dbo.####TempCountry' ) IS NOT NULL DROP TABLE ##TempCountry; 
SELECT * INTO ##TempCountry  FROM dbo.Country WHERE 1 = 2;
INSERT INTO ##TempCountry (CountryCode, CountryName) VALUES ('AF','AFGHANISTAN')
SELECT * FROM  ##TempCountry

And I've translated this to ExecuteStoreCommand's using an EntityFramework connection:

using (MyEntities _ent = new MyEntities ()) {
  //Create a temp table
  try {
    _ent.ExecuteStoreCommand("IF OBJECT_ID ( 'tempdb.dbo.##TempCountry' ) IS NOT NULL DROP TABLE ##TempCountry");
    _ent.ExecuteStoreCommand("SELECT * INTO ##TempCountry FROM dbo.Country  WHERE 1 = 2");
    _ent.ExecuteStoreCommand("INSERT INTO ##TempCountry (CountryCode, CountryName, RegionCode) VALUES ('AF','AFGHANISTAN')");
  } catch (Exception ex) {
    //Generic error 
    Console.writeline("  Unknown error inserting Country: {0} - {1} ", sCountryCode, ex.Message);
  }
} //EF

When I hit the final ExecuteStoreCommand, the code throws a SqlException: "Invalid object name '##TempCountry'."

But I can see the table when I execute a "Select * from tempdb.dbo.sysobjects"

Oded · Accepted Answer · 2011-09-19 18:44:15Z

4

Each ExecuteStoreCommand will execute on a different sessions. The table will be dropped as soon as the first command finishes executing.

From MSDN - CREATE TABLE (under the Remarks section, in Temporary Tables):

Global temporary tables are automatically dropped when the session that created the table ends and all other tasks have stopped referencing them.

One solution is to execute all of the commands in one SQL batch, not separate commands.

answered Sep 19, 2011 at 18:44

Oded

501k102 gold badges900 silver badges1k bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Christopher G. Lewis Over a year ago

So basically, there's no way to do any work with ExecuteStoreCommand and temp tables in a loop...

Oded Over a year ago

@Christopher_G_Lewis - If you execute all of the different SQL commands in one ExecuteStoreCommand, I believe you will be OK. Try it.

Christopher G. Lewis Over a year ago

Yes, but the idea of this was to read in from an Access database and insert into a SQL temp table, verify it and write it out to the final SQL table.

Oded Over a year ago

@Christopher_G_Lewis - Perhaps using a stored procedure and passing in a table valued parameter would be a better solution.

Collectives™ on Stack Overflow

Entity Framework connection not recognizing Temp Table created

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related