Devart Forums

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.

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.

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.

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.

Thanks Dimon,

I thought so, not to worry.

Regards, Paul.