| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
01-15-13, 16:36
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 6
|
|
|
SQL Server data read using vbs
|
|
Hello all. I'm quite inexperienced with vbs and I'm trying to write a VBScript function that will take the following arguments and return success if all returned fields from the query are less than 'Max Age' hours old.
Accept arguments:
SQL Server or Server\Instance
SQL Account Username
SQL Account Password
SQL Query
Max age (in minutes)
Return 0 for failed
Return 1 for succeeded
Example Query:
SELECT [last_execution_date]
FROM [DB].[dbo].[task_tbl]
examples of existing data (SQL data type is datetime):
2013-01-13 22:00:11.323
2013-01-13 23:00:11.933
so basically a compare between the provided Max Age and the dates retrieved by the select. In other words: Tell me everything is fine if last date in DB is 10 mins ago...if the last date is older than that, I want to be alerted. Could anyone give me a hint on how I go about achieving this?
Thank you
|
|
01-15-13, 17:12
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,573
|
|
Code:
SELECT
CASE WHEN 0 < Count(*)
THEN 1 -- Fine
ELSE 0 -- Not so fine
END AS status
FROM [DB].[dbo].[task_tbl]
WHERE DateAdd(minute, - @max_age, GetDate()) < [last_execution_date]
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
01-16-13, 15:12
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 6
|
|
|
|
Thank you
Can you shed some light on how the function gets buit to accept the arguments listed above?
I dont understand the case...what is it checking for. Why are we verifying if 0 < count(*)
I'm a bash dude. Don't understand vbs much at all.
Thanks again
|
|
01-16-13, 16:22
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,573
|
|
Every environment is unique, so I need to fingure out where to start and how to help you.
Have you ever built a successful VB function to query anything at all from this particular SQL Server or group of servers? Are you using ADO or DAO?
You specified that you needed to check to see whether rows were returned for a user-specified number of minutes. The CASE expression combined with the WHERE clause accomplishes the test that you requested.
If you're comfortable with bash, then think of VB as a more generic version of awk or a pared down version of Perl. The concepts are the same, even if the syntax is a bit wonky compared to what you're accustomed to using!
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
01-16-13, 17:34
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 6
|
|
this function will work as an extension to a GFI monitor, no - never built one before.
So an application in my environment keeps updating this table. This is how I know batch jobs are done. There's nothing to alert me tho when my app is not updating this table
So this is why i want GFI to take the arguments as provided, run the function and alert me if the last timestamp is older than 30 minutes.
|
|
01-16-13, 19:13
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,573
|
|
|
|
01-17-13, 10:14
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 6
|
|
Yes, I've seen that. I know the syntax to connect to MS SQL and retrieve records. How do I make that work in a function using the arguments supplied, and then, once i retrieve the data, how do I do my compare with current time.
|
|
01-17-13, 11:41
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,573
|
|
My query posted above doesn't return rows for you to manage, it just returns a 0 or a 1 as you requested. Plug it in, watch it go. If you replace the @max_age with a constant, that query does all of the "heavy lifting" for you!
If you want to display the "go/no go" use the first code snippet from Stack Overflow. If you want to return to the value for code to manipulate then use the second code snippet.
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
01-20-13, 06:56
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 6
|
|
This is what I have so far. Keeps printing "bad" even for timestamps that are 2 minutes less than current time. I'm not sure if that RecordCount returns properly
Code:
Dim myStream, myConnection, myCommand, adoRec, servername, databasename, user, password, mystring, laststamp, maxage
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
servername = "########"
databasename = "########"
user = "########"
password = "########"
maxage = 10 'minutes
myConnection.Open "Provider=SQLOLEDB;server=" & servername & ";database=" & databasename & "; uid=" & user & ";pwd=" & password & ";"
myCommand.ActiveConnection=myConnection
myCommand.CommandText="SELECT * FROM task_tbl WHERE last_execution_date < DATEADD(minute,-"& mxage &",GetDate())"
SET adoRec = myCommand.Execute()
IF adoRec.RecordCount > 0 then
msgbox "good"
else
msgbox "bad"
end if
|
|
01-20-13, 22:03
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 13,573
|
|
Run this SQL to set up your test environment:
Code:
CREATE LOGIN demoLogin
WITH PASSWORD = 'demo'
GO
CREATE DATABASE demoDb
GO
ALTER AUTHORIZATION
ON DATABASE::demoDb
TO demoLogin
USE demodb
GO
CREATE TABLE demoTable (
demoColumn DATETIME NOT NULL
DEFAULT GetDate()
)
INSERT INTO demoTable
SELECT GETDATE()
SELECT GETDATE(), demoColumn
FROM demoTable
WHERE DATEADD(minute, -10, GetDate()) <= demoColumn
Then run this VBScript to test the functionality:
Code:
Dim myStream, myConnection, myCommand, adoRec, servername _
, databasename, user, password, mystring, laststamp, maxage
Set myStream = CreateObject("ADODB.Stream")
Set myConnection = CreateObject("ADODB.Connection")
Set myCommand = CreateObject("ADODB.Command")
servername = ".\SQL2008R2"
databasename = "demoDb"
user = "demoLogin"
password = "demo"
maxage = 10 'minutes
myConnection.Open "Provider=SQLOLEDB;server=" & servername _
& ";database=" & databasename & "; uid=" & user & ";pwd=" _
& password & ";"
set myCommand.ActiveConnection=myConnection
myCommand.CommandText="SELECT 1 FROM demoTable WHERE DATEADD(minute,-" _
& maxage &",GetDate()) <= demoColumn"
set adoRec = myCommand.Execute()
IF adoRec.EOF then
msgbox "BAD"
else
msgbox "good"
end if
set adoRec = nothing
set myCommand = nothing
set myConnection = nothing
After you confirm that it works, run this SQL script to clean up:
Code:
USE master
GO
DROP DATABASE demodb
GO
DROP LOGIN demoLogin
GO
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
01-22-13, 01:33
|
|
Registered User
|
|
Join Date: Jan 2013
Posts: 6
|
|
Thanks so much. That really helped.
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
SQL Server data read using vbs
