Results 1 to 11 of 11
  1. #1
    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

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    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.

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

  4. #4
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    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.

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

  6. #6
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    Ah, now I see more of what you're trying to accomplish!

    I'd suggest you start at How to use VBScript to return data from SQL Server 2005 - Stack Overflow and come back if you have more questions.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

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

  8. #8
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    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.

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

  10. #10
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,922
    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.

  11. #11
    Join Date
    Jan 2013
    Posts
    6
    Thanks so much. That really helped.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •