Select into Temporary Table not recognized

[overvi]

I am using the latest Unidac, delphi 6 and Sql Server 2005
My Code looks like this.

procedure TForm1.Button1Click(Sender: TObject);
var aQu : TUniQuery;
begin
aQu:= TUniQuery.Create(nil);
with quexec, SQL do
begin
Connection:= uniconnection1;
Clear;
Add(' IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS ');
Add(' WHERE ID= OBJECT_ID (N''[TEMPDB].[dbo].[#Jl]'')) ');
Add(' DROP TABLE #Jl ');
ExecSQL;
Clear;
Add(' select customerid, CompanyName, contactname into #Jl ');
Add(' from customers where contacttitle = :ContactTitle ');
Params[0].AsString:= 'Sales Representative';
ExecSQL;
end;
Showmessage('Create table succesfully');
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
with qurefresh, sql do
Begin
close;clear;
add(' select * from #jl');
open;
end;
end;

When i select into table and the select it, the table #JL is not recognized.
Is there a way to solve this ?

[AndreyZ]

Hello,

You can solve this problem in two ways:
1) create temporary table and select from it in one query. Here is a code example:

Code: Select all

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  with quexec, SQL do begin
    Clear;
    Add(' IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS ');
    Add(' WHERE ID= OBJECT_ID (N''[TEMPDB].[dbo].[#Jl]'')) ');
    Add(' DROP TABLE #Jl ');
    ExecSQL;
    Clear;
  end;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
  with qurefresh, SQL do begin
    Close;
    Clear;
    Add(' select customerid, CompanyName, contactname into #Jl ');
    Add(' from customers where contacttitle = :ContactTitle ');
    Add(' select * from #Jl');
    Params[0].AsString:= 'Sales Representative';
    Open;
  end;
end;

2) use global temporary table.

[overvi]

Hello AndreyZ,

Thanks for your quick response.

For solution no 1, it seems impossible to change my like that. Because our old program using BDE and we have a lot of code like that. It kinda hard to look it one by one and change it.

For solution no 2, what do you mean by global temporary table ? can you give me a more detail about that ?

[AndreyZ]

Here are two links where you can find more information about global temporary tables:
http://msdn.microsoft.com/en-us/library/ms186986.aspx
http://msdn.microsoft.com/en-us/library/ms177399.aspx

Here is a code example of using global temporary table:

Code: Select all

procedure TForm1.BitBtn1Click(Sender: TObject);
begin
  with quexec, SQL do begin
    Clear;
    Add(' IF EXISTS (SELECT * FROM TEMPDB.DBO.SYSOBJECTS ');
    Add(' WHERE ID= OBJECT_ID (N''[TEMPDB].[dbo].[##Jl]'')) ');
    Add(' DROP TABLE ##Jl ');
    ExecSQL;
    Clear;
    Add(' select customerid, CompanyName, contactname into ##Jl ');
    Add(' from customers where contacttitle = :ContactTitle ');
    Params[0].AsString:= 'Sales Representative';
    ExecSQL;
  end;
end;

procedure TForm1.BitBtn2Click(Sender: TObject);
begin
  with qurefresh, SQL do begin
    Close;
    Clear;
    Add(' select * from ##Jl');
    Open;
  end;
end;

[overvi]

I am sorry but we can't use '##' temporary table because our program is multiuser program and using a lot of temporary table.
We develop this program using BDE and we only have like 1 month to convert all the BDE connection to something new.
If we change all the '#' to '##', it will take a lot of time and as far as i know, we have to create the '##+tablename+sessionname for each user because if we don't include the session. an error occured said temporary table already exists.
Do you have any other suggestion ?
Thanks so much for your attention.

[AndreyZ]

We have investigated this problem and discovered that it is connected with parameters usage. SQL Server doesn't keep temporary table alive if the query that creates temporary table passes parameters to server. You can solve this problem in two ways:
1) create temporary table using query without parameters, for example:

Code: Select all

Add('select customerid, CompanyName, contactname into #Jl '); 
Add('from customers where contacttitle = ''Sales Representative''');

2) using macros instead of parameters in the following way:

Code: Select all

Add(' select customerid, CompanyName, contactname into #Jl ');
Add(' from customers where contacttitle = &ContactTitle ');
Macros[0].AsString:= 'Sales Representative';

BDE doesn't pass parameters to SQL Server, it passes plain SQL text, and that's why it works.