I have to retrieve some data from a MSSQL 2014 server through a propriatery application which then uses an odbc data source. The only thing I can modify is the query the application uses. I cannot modify the application or how the application handles the results.
The following query is doing what I want if I execute it directly e.g. in Heidi.
USE MY_DB;
BEGIN TRANSACTION
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
DECLARE @myvar1 INT = 2;
DECLARE @myvar2 INT = 2;
PRINT @myvar1;
SELECT TOP 20 [vwPGIA].[OrNum],[vwPGIA].[DBM],[vwPGIA].[MBM],[vwPGIA].[MN],[NOMID],[Priority],SUBSTRING([Comment],0,254) AS Comment,[TLSAP],[Box],[SequenceNumber]
INTO #tmp_tbl
FROM [MY_DB].[dbo].[vwPGIA]
INNER JOIN [MY_DB].[dbo].[tblDLA] ON [dbo].[tblDLA].[OrNum]=[dbo].[vwPGIA].[OrNum]
INNER JOIN [dbo].[tblMDM] ON [vwPGIA].[MBM]=[tblMDM].[MBM]
WHERE ([TLSAP] = @myvar1)
AND [vwPGIA].[MBM] NOT IN (SELECT [MBM] FROM [MY_DB].[dbo].[vwDPS])
AND [vwPGIA].[OrNum] NOT IN (SELECT [OrNum] FROM [MY_DB].[dbo].[vwDPS] WHERE [MY_DB].[dbo].[vwDPS].[TLR] <> @myvar1)
ORDER BY [SequenceNumber];
SELECT TOP 1 [OrNum],[DBM],[MBM],[MN],[NOMID],[Priority],[Comment],[TLSAP],[Box],[WTT],[SequenceNumber]
FROM #tmp_tbl
INNER JOIN [dbo].[tblTBN] ON [Box]=[BoxN]
WHERE ([WTT]=@myvar2)
ORDER BY [SequenceNumber];
INSERT INTO [dbo].[tblDPS]
(OrNum,DBM,MBM,State,StateStartTime,Info,TLR)
SELECT TOP 1 [OrNum],[DBM],[MBM],'1',GETDATE(),'info',@myvar1
FROM #tmp_tbl
INNER JOIN [dbo].[tblTBN] ON [Box]=[BoxN]
WHERE ([WTT]=@myvar2)
ORDER BY [SequenceNumber]
;
DROP TABLE #tmp_tbl;
COMMIT TRANSACTION
Running this through the ODBC interface results in an empty result. The problem seems to be, that I am doing a batch request which results in multiple result sets. The application probably only handles the first result set or maybe cannot handle more than one result set.
Finally the question: Is there a way or workaround to reduce the result sets to only the one returned by the SELECT TOP 1 ... part?
