UFT: ADODB state closed while executing SQL

Question

Using UFT12.51. Need to retrieve data from SQL Server. The query I need to run is the following:

DROP TABLE IF EXISTS dbo.TempTableOutgoing, TempTableOutgoingLeft, TempTableOutgoingRight;
SELECT * INTO TempTableOutgoingRight FROM(
SELECT vi.rownumber, vi.outgoing, vi.oamount FROM [eBalance].[dbo].[AMEX_OUTGOING] AS V1
      CROSS APPLY (Values ('1','Sales', Sales),
                    ('2','Cash Advances', CashAdvances),
                    ('3','Returns', Credits)
            ) as vi (rownumber, outgoing, oamount)
WHERE v1.FileDate = ''"&FileDate&"'')R;

SELECT RowNum, Present, Amount, Outgoing, OAmount INTO TempTableOutgoing FROM(
SELECT * FROM(SELECT * FROM TempTableOutgoingLeft
UNION ALL
SELECT '4','Total',  CONVERT(VARCHAR, SUM(CAST(ISNULL(Amount,'0') AS decimal(15,2)))) from TempTableOutgoingLeft) AS Table1
LEFT OUTER JOIN (SELECT * FROM TempTableOutgoingRight
UNION ALL
SELECT '4','Outgoing Settlement Total',  CONVERT(VARCHAR, SUM(CAST(ISNULL(oamount,'0') AS decimal(15,2)))) from TempTableOutgoingRight) AS Table2
ON Table1.RowNum = Table2.rownumber) Z;

DROP TABLE TempTableOutgoingLeft;
DROP TABLE TempTableOutgoingRight;

SELECT present, Amount, Outgoing, OAmount, Variance FROM(
SELECT * FROM dbo.TempTableOutgoing z1
INNER JOIN (Select RowN, Variance from (
SELECT DISTINCT RowN, Variance FROM TempTableOutgoing AS TTO
     CROSS APPLY (Values 
        ('1',''),('2', (SELECT CONVERT(VARCHAR, SUM(CAST(ISNULL(Amount,'0') AS decimal(15,2)))-SUM(CAST(ISNULL(OAmount,'0') AS decimal(15,2))))
        FROM TempTableOutgoing where RowNum IN (1,2,3))), ('3','')) 
        as z (RowN,Variance)    where TTO.RowNum IN (1,2,3)
UNION ALL
SELECT DISTINCT z.* FROM TempTableOutgoing AS TTO
     CROSS APPLY (Values 
        ('4', (SELECT CONVERT(VARCHAR, SUM(CAST(ISNULL(Amount,'0') AS decimal(15,2)))-SUM(CAST(ISNULL(OAmount,'0') AS decimal(15,2))))
        FROM TempTableOutgoing where RowNum IN (4)))
        ) as z (RowN,Variance)  where TTO.RowNum IN (4)
) V Group by RowN, Variance) V1
on z1.RowNum = V1.RowN) T;

When I run it on the Microsoft SQL Management Studio, it runs correctly and gives proper result. But when trying to run it from UFT, I get error. The code is:

Dim objConnection : Set objConnection = CreateObject("ADODB.Connection")
Set objRS = objConnection.Execute(query)

When running in SQL Management Studio, it works well and gives proper result:

present     |Amount     |Outgoing                 |OAmount    |Variance
============+===========+=========================+===========+========
1st Present |5028510.18 |Sales                    |5110968.89
------------+-----------+-------------------------+-----------+--------
1P 1-Sided  |0.00       |Cash Advances            |0.00       |0.00
------------+-----------+-------------------------+-----------+--------
            |NULL       |Returns                  |-82458.71  |
------------+-----------+-------------------------+-----------+--------
Total       |5028510.18 |Outgoing Settlement Total|5028510.18 |0.00

Trying to get data from the query. While connection state in True, the Recordset remains closed:

print objRS.State
print objConnection.State

Result:

0

0

Also tried next, but it gace same results:

Dim objRS : Set objRS = CreateObject("ADODB.Recordset")
objRS.open query, objConnection

In both cases, print objRS.Fields(1) gives following error:

Item cannot be found in the collection corresponding to the requested name or ordinal

The connection settings are 100% good, and even a simple query like SELECT * from [database].[schema].[table] and print recordset.fields(0) or print recordset.state returns a valid/true result.

As far as I researched, problem might cause when the query has more than one column with same name, but I have all different column names.

What can be the problem and what might be a solution?

Similar questions are asked by many people, but researching several posts, could not find any answer.

Answer 1

Some (especially older) drivers get confused with the ROWCOUNT messages that multi-statement batches will send by default. You can suppress these by including SET NOCOUNT ON at the beginning of the batch.

Also SQL Server has temp tables. Use them instead of creating and dropping regular tables.

EG

SET NOCOUNT ON;
DROP TABLE IF EXISTS dbo.#Outgoing, #OutgoingLeft, #OutgoingRight;

SELECT * INTO #OutgoingRight FROM(
SELECT vi.rownumber, vi.outgoing, vi.oamount FROM [eBalance].[dbo].[AMEX_OUTGOING] AS V1
      CROSS APPLY (Values ('1','Sales', Sales),
                    ('2','Cash Advances', CashAdvances),
                    ('3','Returns', Credits)
            ) as vi (rownumber, outgoing, oamount)
WHERE v1.FileDate = ''"&FileDate&"'')R;

SELECT RowNum, Present, Amount, Outgoing, OAmount INTO #Outgoing FROM(
SELECT * FROM(SELECT * FROM #OutgoingLeft
UNION ALL
SELECT '4','Total',  CONVERT(VARCHAR, SUM(CAST(ISNULL(Amount,'0') AS decimal(15,2)))) from #OutgoingLeft) AS Table1
LEFT OUTER JOIN (SELECT * FROM #OutgoingRight
UNION ALL
SELECT '4','Outgoing Settlement Total',  CONVERT(VARCHAR, SUM(CAST(ISNULL(oamount,'0') AS decimal(15,2)))) from #OutgoingRight) AS Table2
ON Table1.RowNum = Table2.rownumber) Z;

DROP TABLE #OutgoingLeft;
DROP TABLE #OutgoingRight;

SELECT present, Amount, Outgoing, OAmount, Variance FROM(
SELECT * FROM dbo.#Outgoing z1
INNER JOIN (Select RowN, Variance from (
SELECT DISTINCT RowN, Variance FROM #Outgoing AS TTO
     CROSS APPLY (Values 
        ('1',''),('2', (SELECT CONVERT(VARCHAR, SUM(CAST(ISNULL(Amount,'0') AS decimal(15,2)))-SUM(CAST(ISNULL(OAmount,'0') AS decimal(15,2))))
        FROM #Outgoing where RowNum IN (1,2,3))), ('3','')) 
        as z (RowN,Variance)    where TTO.RowNum IN (1,2,3)
UNION ALL
SELECT DISTINCT z.* FROM #Outgoing AS TTO
     CROSS APPLY (Values 
        ('4', (SELECT CONVERT(VARCHAR, SUM(CAST(ISNULL(Amount,'0') AS decimal(15,2)))-SUM(CAST(ISNULL(OAmount,'0') AS decimal(15,2))))
        FROM #Outgoing where RowNum IN (4)))
        ) as z (RowN,Variance)  where TTO.RowNum IN (4)
) V Group by RowN, Variance) V1
on z1.RowNum = V1.RowN) T;