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.

The ADODB error indicating that the item connect be found means that you DID connect to the DB, and it can't find the column. I'm guessing this is what is can't find: objRS.Fields("No column name") — Jen R
– Jen R, Commented Mar 21, 2018 at 15:33

Jen R · Accepted Answer · 2018-03-21 15:40:42Z

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

answered Mar 21, 2018 at 15:40

Jen R

1,53318 silver badges23 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

edwio Over a year ago

Hey Jen R, first thanks for your help in advance its very much appreciated. i will check and update

user692942 Over a year ago

You could also use objRS.Fields(0) to check the first field without an alias.

edwio Over a year ago

Hey Lankyart, thanks for the help. I tried this method before but i got the same second error

edwio Over a year ago

Jen R, i update the script following your instructions, and im getting the following error: (26, 33) Microsoft VBScript runtime error: Object required: 'objAPi'

edwio Over a year ago

It seems that i need to declare the objec objAPI, but simply adding: dim objAPI didn't work..

|

Collectives™ on Stack Overflow

Can't Open Connection String to SQL Server in VBSscript

1 Answer 1

8 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Your Answer

Sign up or log in

Post as a guest

Related