I'm trying to execute the following SQL Query:
DECLARE @Start DATETIME
SET @Start = DateAdd(Hour, -1,GetDate())
IF OBJECT_ID('tempdb..#logTable') IS NOT NULL
DROP TABLE #logTable
CREATE TABLE #logTable
(
LogDate DATETIME,
ProccessINfo SYSNAME,
[Text] NVARCHAR(MAX)
)
INSERT INTO #logTable exec xp_readerrorlog 0, 1, N'longer than 15 seconds to complete', N'', @Start, N'9999-12-31', N'desc'
SELECT * FROM tempdb..#logTable
Using a simple VBScript that I have wrote:
'MSSQLQuery.vbs
Option Explicit
'On Error Resume Next
Dim objCN, objRS, strConnection, strSQLQuery
Set objCN = CreateObject("ADODB.Connection")
Set objRS = CreateObject("ADODB.Recordset")
strConnection = "Driver={SQL Server};Trusted_Connection=TRUE;Server=WINDMSSQL01.dev;Database=master"
strSQLQuery = "DECLARE @Start DATETIME " & _
"SET @Start = DateAdd(Hour, -1,GetDate()) " & _
"IF OBJECT_ID('tempdb..#logTable') IS NOT NULL " & _
"DROP TABLE #logTable " & _
"CREATE TABLE #logTable " & _
"( " & _
"LogDate DATETIME, " & _
"ProccessINfo SYSNAME, " & _
"[Text] NVARCHAR(MAX) " & _
") " & _
"INSERT INTO #logTable exec xp_readerrorlog 0, 1, N'longer than 15 seconds to complete', N'', @Start, N'9999-12-31', N'desc' " & _
"SELECT * FROM tempdb..#logTable;"
objCN.Open strConnection
Set objRS = objCN.Execute(strSQLQuery)
If Err.Number = 0 Then
WScript.Echo "SQL Server Connection OK, SQL Query Output:" & vbCrLf
WScript.Echo objRS.Fields("LogDate")
Else
WScript.Echo "SQL Server Connection Not OK."
End If
The SQL Query above, returns three columns:
The VBScript is successfully executing the SQL Query, But when I'm trying to print one of three columns mentioned above (for example the "LogDate" column), I'm receiving the following error message:
MSSQLQuery.vbs(32, 2) ADODB.Recordset: Item cannot be found in the collection corresponding to the requested name or ordinal.
Which does not make sense, as I can see the column in the SQL Query results via the SSMS:
SQL Server returns a lot of ROWCOUNTs when you do other statements than SELECT, so it usually help to set SET NOCOUNT ON.
Also, you should loop your recordset, something like:
...
strSQLQuery = "SET NOCOUNT ON; DECLARE @Start DATETIME " & _
"SET @Start = DateAdd(Hour, -1,GetDate()) " & _
"IF OBJECT_ID('tempdb..#logTable') IS NOT NULL " & _
"DROP TABLE #logTable " & _
"CREATE TABLE #logTable " & _
"( " & _
"LogDate DATETIME, " & _
"ProccessINfo SYSNAME, " & _
"[Text] NVARCHAR(MAX) " & _
") " & _
"INSERT INTO #logTable exec xp_readerrorlog 0, 1, N'', N'', @Start, N'9999-12-31', N'desc' " & _
"SELECT * FROM tempdb..#logTable;"
objCN.Open strConnection
Set objRS = objCN.Execute(strSQLQuery)
If Err.Number = 0 Then
WScript.Echo "SQL Server Connection OK, SQL Query Output:" & vbCrLf
while Not objRS.EOF
WScript.Echo objRS.Fields("LogDate")
objRS.MoveNext
wend
Else
WScript.Echo "SQL Server Connection Not OK."
End If
...