How to run multiple queries in qtp in one shot using single object

I have a excel sheet with a column named sqlQueries. Every cell has a set of queries to run.

I am able to run sumple queries using qtp But the cells that have multiple statements like for e.g in cell(x,6) the below query is present: " Use LDatabase Exec sp_DropObjectIfExists '#tempTable'; Select col1 into #tempTable from maintable; Update #tempTable set colv=5 Select count(1) as totalCount from #tempTable "

The above is just an e.g. and not the exact sql query. This entire set is in a single excel sheet's cell. I want this to be executed using Qtp.

Currently, what I am doing in qtp is:

Set objconnection = CreateObject("ADODB.Connection")
objconnection.open"provider=blah blah blah"
 Set objrecordset= CreateObject("ADODB.Recordset")
 ws.cells(x,6).select ''''the above sql queries set is in this cell
Sqlquery1= ws.cells(x,6).value
objrecordset.Open Sqlquery1. objconnection
Value1=objrecordset.Fields.Item(0)

For the Last line above I am getting error saying "Items cannot be found in the collection corresponding to requestef name or ordinal"

I am assuming this is because there are multiple statements in a single cell which are to be excuted but only the first line that is "use LDatabase" is being executed. And not all the cell content.

Could you please help me execute the entire thing in a single shot.

Thanks!

Solution

Prefix your queries with SET NOCOUNT ON;. This will allow you to use temp tables and variables in your SQL statements.

The code below demonstates this. I've used early binding to make the code easier to read (Tools >> References >> Microsoft ActiveX Data Objects 2.8 Library).

Switch between these lines to test:

rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly

QueryA will fail. QueryB will return Jack.

' Demo of using SET NOCOUNT ON;.
' This option enabled the use of SQL vars and temp tables.
Sub Test()
    Dim cn As ADODB.Connection
    Dim rs As ADODB.Recordset

    Set cn = New ADODB.Connection
    Set rs = New ADODB.Recordset

    cn.Open "Driver={SQL Server};Server=YOUR-SEVER-NAME-HERE;Database=master;Trusted_Connection=Yes;"

    ' QueryA fails, while QueryB does not.
    ' Switch which line is commented out to test.
    rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
    'rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly

    ' This line will raise an error with QueryA.
    ' This line will work with QueryB.
    MsgBox rs.Fields(1).Value

    rs.Close
    cn.Close
End Sub

' Returns a sample query without NOCOUNT.
Public Function QueryA() As String

    QueryA = "              CREATE TABLE #ExampleA              "
    QueryA = QueryA & "     (                                   "
    QueryA = QueryA & "         Id      INT PRIMARY KEY,        "
    QueryA = QueryA & "         Name    VARCHAR(50) NOT NULL    "
    QueryA = QueryA & "     );                                  "
    QueryA = QueryA & ""
    QueryA = QueryA & "     INSERT INTO #ExampleA (Id, Name)    "
    QueryA = QueryA & "     VALUES                              "
    QueryA = QueryA & "         (1, 'Jack'),                    "
    QueryA = QueryA & "         (2, 'Jill')                     "
    QueryA = QueryA & "     ;                                   "
    QueryA = QueryA & ""
    QueryA = QueryA & "     SELECT * FROM #ExampleA             "
End Function

' Returns a sample query with NOCOUNT.
Public Function QueryB() As String

    QueryB = "              SET NOCOUNT ON;                     "
    QueryB = QueryB & ""
    QueryB = QueryB & "     CREATE TABLE #ExampleA              "
    QueryB = QueryB & "     (                                   "
    QueryB = QueryB & "         Id      INT PRIMARY KEY,        "
    QueryB = QueryB & "         Name    VARCHAR(50) NOT NULL    "
    QueryB = QueryB & "     );                                  "
    QueryB = QueryB & ""
    QueryB = QueryB & "     INSERT INTO #ExampleA (Id, Name)    "
    QueryB = QueryB & "     VALUES                              "
    QueryB = QueryB & "         (1, 'Jack'),                    "
    QueryB = QueryB & "         (2, 'Jill')                     "
    QueryB = QueryB & "     ;                                   "
    QueryB = QueryB & ""
    QueryB = QueryB & "     SELECT * FROM #ExampleA             "
End Function

I've embedded the two versions of my query in a couple of, ugly, functions. They're hard to read but easy to share. Below is a clean version of the working query. Remove the first line for the non-working variant.

SET NOCOUNT ON;

CREATE TABLE #ExampleA              
(                                   
    Id      INT PRIMARY KEY,        
    Name    VARCHAR(50) NOT NULL    
);                                  

INSERT INTO #ExampleA (Id, Name)    
VALUES                              
    (1, 'Jack'),                    
    (2, 'Jill')                     
;                                   

SELECT * FROM #ExampleA;