"ADODB.Recordset: Item cannot be found in the collection corresponding to the requested name or ordinal" Error for Existing Column

Question

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:

1. LogDate
2. ProccessINfo
3. Text

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:

Answer 1

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
...