1

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.stack.imgur.com/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.

edwio
  • 198
  • 3
  • 20
  • 2
    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 Mar 21 '18 at 15:33

1 Answers1

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
Jen R
  • 1,527
  • 18
  • 23
  • Hey Jen R, first thanks for your help in advance its very much appreciated. i will check and update – edwio Mar 21 '18 at 15:58
  • 1
    You could also use `objRS.Fields(0)` to check the first field without an alias. – user692942 Mar 22 '18 at 06:53
  • Hey Lankyart, thanks for the help. I tried this method before but i got the same second error – edwio Mar 22 '18 at 08:09
  • 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 Mar 22 '18 at 08:14
  • It seems that i need to declare the objec objAPI, but simply adding: `dim objAPI` didn't work.. – edwio Mar 22 '18 at 08:27
  • UPDATE: turns out, that the object name was not correct, and after changing the script to: `Call oAPI.Return(oBag)` everything works fine. and i get the following output: **BAD** But i can't see the output of the query. is there anyway for me to see the output of the query via the script? – edwio Mar 22 '18 at 08:43
  • In the script above, you are just getting a count. If you want to see what it is, just `WScript.Echo (objRS.Fields("countStuff"))` and it will print to the console, or add `Call oBag.AddValue("Count",objRS.Fields("countStuff"))` to send it to your oAPI call. If you want more information, you'll have to get more with your query. – Jen R Mar 22 '18 at 12:27
  • Hey Jen R, is there a way for me to target specific column and row from the SQL query output? like the arrays targeting in VBScript, for exsample: `if objRS.Fields(0,2)` Meaning: Column = 0 Row = 2 – edwio Mar 25 '18 at 14:01