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.

 
Go Back  dBforums > Data Access, Manipulation & Batch Languages > Visual Basic > SQL Server data read using vbs

Reply
 
LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old
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
Reply With Quote
  #2 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,421
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.
Reply With Quote
  #3 (permalink)  
Old
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
Reply With Quote
  #4 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,421
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.
Reply With Quote
  #5 (permalink)  
Old
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.
Reply With Quote
  #6 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,421
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.
Reply With Quote
  #7 (permalink)  
Old
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.
Reply With Quote
  #8 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,421
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.
Reply With Quote
  #9 (permalink)  
Old
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
Reply With Quote
  #10 (permalink)  
Old
Resident Curmudgeon
 
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,421
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.
Reply With Quote
  #11 (permalink)  
Old
Registered User
 
Join Date: Jan 2013
Posts: 6
Thanks so much. That really helped.
Reply With Quote
Reply

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On