如何使用单个对象一次性在qtp中运行多个查询
问题描述:
我有一个名为sqlQueries的列的Excel工作表。每个单元都有一组运行的查询。如何使用单个对象一次性在qtp中运行多个查询
我能够使用QTP 运行sumple查询,但有多个语句像例如在细胞(X,6)下面的查询存在的细胞: “ 使用LDatabase Exec的sp_DropObjectIfExists‘#tempTable’; 选择COL1成#tempTable从maintable; 更新#tempTable设置COLV = 5 SELECT COUNT(1)如从TOTALCOUNT #tempTable “
上面只是一个例如而不是确切的SQL查询。 这整个集合在单个Excel表单元中。 我希望这是使用Qtp执行的。
目前,我在做什么在QTP是:
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)
最后一行上面我得到错误说 “的项目无法在集合中找到对应requestef的名称或序号”
我假设这是因为在单个单元中有多个要执行的语句,但只有第一行是“正在使用LDatabase”正在执行。而不是所有的细胞内容。
你能帮我一下子完成整个事情吗?
谢谢!
答
在SET NOCOUNT ON;
前缀查询。这将允许您在SQL语句中使用temp tables和变量。
下面的代码演示了这一点。我已使用early binding使代码更易于阅读(工具 >>参考文献 >>Microsoft ActiveX Data Objects 2.8 Library)。这些线之间
切换到测试:
rs.Open QueryA, cn, adOpenForwardOnly, adLockReadOnly
rs.Open QueryB, cn, adOpenForwardOnly, adLockReadOnly
QueryA将失败。 QueryB将返回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
我在几个丑陋的函数中嵌入了我的查询的两个版本。他们很难阅读,但很容易分享。以下是工作查询的干净版本。删除非工作变体的第一行。
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;
'Sqlquery1。 objconnection'应该读为'Sqlquery1,objconnection'。每个单元格中的查询是否总是返回一个表格?如果是这样,它总是一张桌子吗? –
是的。在单元格中设置的每个查询都会返回一个表。 – Neha
[Microsoft推荐](https://support.microsoft。com/en-us/help/235340/prb-error-messaging-referencing-temp-table-with-ado-sqloledb)您将SET NOCOUNT ON;'添加到包含临时表的查询中。这也可以让你包含声明语句(例如:'DECLARE @MyVar INT = 5;')。 –