I can not open table #tmp after execute "SELECT into #tmp "with parameters

[Joao Henriques]

Hi, i´m getting a error in MSQuery1.Open
after a "select into #tmp" with parameters.

I´m using SDAC version 3.00.2.8 with Delphi 6

code :

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('SELECT Campo1 into #Tmp from TXXX (nolock)');
MSQuery1.SQL.Add('where Campo1=:Campo1');
MSQuery1.ParamByName('Campo1').AsInteger:=59;
MSQuery1.Execute;

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('SELECT * from #Tmp');
MSQuery1.Open;

best rgds

João Henriques

[Ikar]

Do you use FetchAll = False?

[Joao Henriques]

FetchAll:=true
and
FetchAll:=false;

don´t work

[Ikar]

The reason of the problem is in using in the query temporary tables and parameters. MS SQL executes queries with parameters through StoredProc sp_executesql but it conflicts with temporary tables. You can read details, for example, in MSDN.

To solve the problem you can move it to VIEW or STORED PROC, or use macros instead of parameters.

[Joao Henriques]

Thank´s

best rgds

João Henriques

[AntunesN]

Try placing everything inside a stored proc, like this for example:

>> In SQL Server
CREATE PROC spQualquerCoisa
@Campo1 INT
AS
SELECT Campo1
INTO #Tmp
FROM TXXX (nolock)
WHERE Campo1 = @Campo1

SELECT *
FROM #Tmp
go

>> In Delphi place a TMSStoredProc object or a TMSQuery since SDAC allows you to run a StoredProc as it was a Query object (returning datasets):

MSStoredProc1.StoredProcName := 'spQualquerCoisa';
MSStoredProc1.Params.ParamByName('Campo1').AsInteger := 59;
MSStoredProc1.Open;

Hi, i´m getting a error in MSQuery1.Open
after a "select into #tmp" with parameters.

I´m using SDAC version 3.00.2.8 with Delphi 6

code :

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('SELECT Campo1 into #Tmp from TXXX (nolock)');
MSQuery1.SQL.Add('where Campo1=:Campo1');
MSQuery1.ParamByName('Campo1').AsInteger:=59;
MSQuery1.Execute;

MSQuery1.SQL.Clear;
MSQuery1.SQL.Add('SELECT * from #Tmp');
MSQuery1.Open;

best rgds

João Henriques