Temporary Table with TMSQuery

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
ladileal
Posts: 2
Joined: Tue 09 Sep 2008 11:49

Temporary Table with TMSQuery

Post by ladileal » Tue 09 Sep 2008 12:16

Hello dudes, :D

I'm using Delphi 7 and SDAC 4.50, the database server is running SQL Server 2000.

I'm converting a project from ADO to SDAC, everything was going very well, but my issue is about use of temporary tables inside query code, it doesn't worked with me.. I have one complex query that do that and works fine on ADO, but with TMSQuery it returns an error: "Query must return exactly one result set - use Execute".

I use execute but it doesn't returns anything..


SQL code:

DECLARE @CD_ORG INT,
@CD_ORG_FIN INT,
@DATA1 SMALLDATETIME,
@DATA2 SMALLDATETIME,
@NM_ARQ VARCHAR(15)

SET @CD_ORG = :CD_ORG
SET @DATA1 = :DATA1
SET @DATA2 = :DATA2

IF @CD_ORG = 0
SET @CD_ORG_FIN = 99999
ELSE SET @CD_ORG_FIN = @CD_ORG

SELECT IDENTITY(int, 1,1) AS NR_SEQ, DT_IMP, NM_ARQ, CD_INT, TP_ARQ, QT_REG_ACE, QT_REG_REJ, ORGAO, CD_ORG
INTO #TEMP FROM (SELECT DISTINCT ING_DT_IMP AS DT_IMP,
ING_NM_ARQ AS NM_ARQ,
ING_CD_INT AS CD_INT,
CASE ING_CD_INT WHEN 'VEI' THEN 'VEICULOS' WHEN 'LIM' THEN 'LIMITES' END AS TP_ARQ,
ING_QT_REG_ACE AS QT_REG_ACE,
ING_QT_REG_REJ AS QT_REG_REJ,
LTRIM((SELECT DISTINCT ORG_NM_ORGAO FROM ORGAO WHERE ORG_CD_ORG = CD_ORG)) AS ORGAO,
CD_ORG
FROM (SELECT DISTINCT VEI_NM_ARQ AS NM_ARQ, VEI_CD_ORG AS CD_ORG FROM VEICULOS
WHERE VEI_CD_ORG BETWEEN @CD_ORG AND @CD_ORG_FIN
AND VEI_NM_ARQ IS NOT NULL
UNION ALL
SELECT DISTINCT MLI_NM_ARQ AS NM_ARQ, MLI_CD_ORG AS CD_ORG FROM MOVIMENTO_LIMITE
WHERE MLI_CD_ORG BETWEEN @CD_ORG AND @CD_ORG_FIN
AND MLI_NM_ARQ IS NOT NULL
UNION ALL
SELECT DISTINCT INE_NM_ARQ AS NM_ARQ, INE_CD_ORG AS CD_ORG FROM INTERFACE_ERRO
WHERE INE_CD_ORG BETWEEN @CD_ORG AND @CD_ORG_FIN) A LEFT JOIN INTERFACE_GERADA B
ON A.NM_ARQ = B.ING_NM_ARQ
WHERE ING_DT_IMP BETWEEN @DATA1 AND @DATA2) C
SELECT * FROM #TEMP
DROP TABLE #TEMP


If someone could help, from now I give my thanks!

ladileal
Posts: 2
Joined: Tue 09 Sep 2008 11:49

Post by ladileal » Tue 09 Sep 2008 13:36

Hello again..

A workfriend helped me here, he found the error.. the property "CursorType" of TMSQuery was set 'csStatic' instead of 'ctDefaultResultSet'.

I have used the BDE/ADO Migration Wizard, so it have copied the property value from the old ADO component. After the change all works great..

Anyway, I hope this post could help someone.. 8)

Dimon
Devart Team
Posts: 2885
Joined: Mon 05 Mar 2007 16:32

Post by Dimon » Thu 11 Sep 2008 06:56

It is good to see that this problem has been solved. :-)

Post Reply