UniLoader and temporary tables

hughespa · Post by **hughespa** » Wed 23 Jun 2010 09:41

Hi,

Using UniDAC 3.00.0.9, D2010, SQL Svr 2008

Are there any issues using uniloader with temporary tables?

If I create a temp table (#wr_temp, local scope) and attempt UniLoader1.LoadFromDatasSet, I get the message 'Invalid object name '#wr_temp'.

If I create a global scope temp table, (##wr_temp) it works OK.

The same code sequence works fine with a PostgreSQL local scope temp table (although the tablename is just wr_temp for this).

Any help appreciated.

Regards, Paul.

Dimon · Post by **Dimon** » Wed 23 Jun 2010 11:09

To use UniLoader with temporary tables, you should use a global temporary table.

I can not reproduce the problem with hanging on global temporary table. Please try to compose a small sample to demonstrate the problem and send it to dmitryg*devart*com.

hughespa · Post by **hughespa** » Wed 23 Jun 2010 11:45

Hi Dimon,

Thanks for your reply.

I actually amended the post a little later to say that it was OK with the global temp table. In the first case, where the application hung, I think it was because I was doing it within an active transaction already started on the connection. The steps were:

Connection.StartTransaction
Read some data via a UniQuery
VirtualTable.Assign (from query);
Close Query
UniConnection.ExecSQL(create temp table)
Loop through virtual table updating records
UniLoader.LoadFromDataSet(virtual table)
Connection.ExecSQL(SQL to update static table from temp table)
Connection.ExecSQL(drop temp table)
Connection.Commit;

Removing the transaction allowed it to run with the global temp table.

I'd like to wrap it in at transaction if possible, can that be done?

This may seem a little verbose but for an update of around 20,000 records in the the static table, traditional updates via query took between 20..30 seconds (sql server was a little bit quicker then postgres) whilst this method is usually less than 2 seconds on both DBs.

Regards, Paul.

Dimon · Post by **Dimon** » Wed 23 Jun 2010 15:29

hughespa wrote:Removing the transaction allowed it to run with the global temp table.
I'd like to wrap it in at transaction if possible, can that be done?

This behaviour is connected with the specificity of the SQL Server work and we can't influence it.

hughespa · Post by **hughespa** » Thu 24 Jun 2010 01:40

Thanks Dimon,

I thought so, not to worry.

Regards, Paul.