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!
