Devart Forums

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

Do you use FetchAll = False?

FetchAll:=true
and
FetchAll:=false;

don´t work

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.

Thank´s

best rgds

João Henriques

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;

Joao Henriques wrote: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