Can't Open Connection String to SQL Server in VBSscript

Question

I have a .vbs script that runs the following sql query: Select COUNT (*) from sys.objects Which count the rows, from the sql query output: https://i.sstatic.net/wduXW.png[1]

And if there is any rows found (> 0). genereate an alert in SCOM using the PropertyBag scripting runtime in SCOM.

Problem is, When debugging the script (using cscript), i get the following error messeage:

(11,1) Microsoft OLE DB Provider for ODBC Drivers: [Microsoft][ODBC SQL Server Driver][Shared Memory]SQL Server does not exist or access denied.

Although the Connection string seems to be correct:

strConnection = "Driver={SQL Server};Server=SCOMSRVDB01;Database=DBABee;Trusted_Connection=TRUE"

Here is the Full VBScript:

Dim objCN, strConnection
Dim oAPI, oBag

Set objCN = CreateObject("ADODB.Connection")
Set oAPI = CreateObject("MOM.ScriptAPI")
Set oBag = oAPI.CreatePropertyBag()



strConnection = "Driver={SQL Server};Server=SCOMSRVDB01;Database=DBABee;Trusted_Connection=TRUE"
objCN.Open strConnection

Dim strSQLQuery
strSQLQuery = "Select COUNT (*) from sys.objects"

Dim objRS
Set objRS=CreateObject("ADODB.Recordset")

Set objRS = objCN.Execute(strSQLQuery)

Do Until objRS.EOF
                'WScript.Echo objRS.Fields("No column name")
                if objRS.Fields("No column name") > 0 then
                                                                'WScript.Echo "evaluated as bad"
                                Call oBag.AddValue("State","BAD")
                                Call objAPI.Return(oBag)
                else
                                Call oBag.AddValue("State","GOOD")
                                Call objAPI.Return(oBag)
                end if
                objRS.MoveNext
Loop

objRS.Close

It worth mentioning, That in our company you can't connect to an sql server without mention Port Number.

But when i tried to add it (Port: 2880) in the connection string:

strConnection = "Driver={SQL Server};Server=SCOMSRVDB01,2880;Database=DBABee;Trusted_Connection=TRUE"

The script returen the following error:

(23,17) ADODB.Recordset: Item cannot be found in the collection corresponding to the requested name or ordinal.

Answer 1

The ADODB error indicating that the item connect be found means that you successfully connected to the DB, and it can't find the column you requested. This is what is can't find: objRS.Fields("No column name")

Change your query and name the column:

strSQLQuery = "Select COUNT (*) as countStuff from sys.objects"

Then change what you are looking for:

if objRS.Fields("countStuff") > 0 then