CODEWITHSUNDEEP
sql-servervbscript
user3017424
user3017424

Reputation: 65

Check if SQL job is running with VBScript

We have a daily SQL job that runs in the early hours of the morning. The job runs for a different number of hours every day. So I need a VBScript that I can run to check if the job is currently running or not. I have a SQL query to do it but whenever I use it in a VBScript it returns nothing but when I use it in SQL it works perfectly and tells me that it is still running. I have written many other VBScripts with SQL queries and they all worked perfectly.

USE msdb
SELECT
    job.name AS [Job Name],
    activity.run_requested_date AS [Run Date And Time],
    DATEDIFF( SECOND, activity.run_requested_date, GETDATE() ) AS [Elapsed]
FROM sysjobs_view job WITH (NOLOCK)
LEFT JOIN sysjobactivity activity ON job.job_id = activity.job_id
LEFT JOIN syssessions sess ON sess.session_id = activity.session_id
LEFT JOIN (SELECT MAX( agent_start_date ) AS max_agent_start_date FROM syssessions WITH (NOLOCK)) sess_max
ON sess.agent_start_date = sess_max.max_agent_start_date
WHERE run_requested_date IS NOT NULL AND stop_execution_date IS NULL

I don't know that much about SQL but I assume this query is for a view instead of a table. I don't even know the difference between a view and a table. Can VBScript not query views?

Edit I found another SQL query that give me exactly what I want as well.

SELECT sysjobs.name, sysjobactivity.run_requested_date
FROM sysjobactivity WITH (NOLOCK)
INNER JOIN sysjobs ON sysjobactivity.job_id = sysjobs.job_id
WHERE sysjobactivity.start_execution_date IS NOT NULL AND sysjobactivity.stop_execution_date IS NULL

But now I get a different error which makes no sense to me. VBScript code:

strSQLQuery = ""
strSQLQuery = strSQLQuery = "SELECT sysjobs.name, sysjobactivity.run_requested_date"
strSQLQuery = strSQLQuery = " FROM sysjobactivity WITH (NOLOCK)"
strSQLQuery = strSQLQuery = " INNER JOIN sysjobs ON sysjobactivity.job_id = sysjobs.job_id"
strSQLQuery = strSQLQuery = " WHERE sysjobactivity.start_execution_date IS NOT NULL AND sysjobactivity.stop_execution_date IS NULL"

' SQL connection string
strSQLConn = "Driver={SQL Server};" & _
"Server=10.144.175.142;" & _
"Address=10.144.175.142,1433;" & _
"Database=msdb;" & _
"UID=username;" & _
"PWD=password;"

' Create database connection object
Set objConn = CreateObject( "ADODB.Connection" )

' Connection properties
objConn.ConnectionTimeout = 800
objConn.CommandTimeout = 800
objConn.Provider = "SQLOLEDB"

' Open Connection to Database
objConn.Open strSQLConn

' Clear variables
strSQLConn = Empty

' Run the SQL query
Set objRS = objConn.Execute( strSQLQuery )

' Clear variables
strSQLQuery = Empty

' Check if any data was returned
If Not objRS.EOF Then
    MsgBox objRS.Fields( "name" ).Value
Else
    MsgBox "Not Running"
End If

The error I get now is incorrect syntax near "0" on line 30. Line 30 is:

Set objRS = objConn.Execute( strSQLQuery )

So, that to me means there is something wrong with the SQL query but it runs perfectly fine in SQL itself.

Upvotes: 0

Views: 1128

Answers (2)

Lukasz Szozda
Lukasz Szozda

Reputation: 176124

You should not use USE msdb in your query when you execute it in VB instead:

1) In your connection string set msdb as default database

2) In your select use full names like: msdb..sysjobs_view instead of sysjobs_view

EDIT:

Also you have error when concatenating string:

strSQLQuery = strSQLQuery = "SELECT sysjobs.name, sysjobactivity.run_requested_date"

shoud be

strSQLQuery = strSQLQuery & "SELECT sysjobs.name, sysjobactivity.run_requested_date"

Otherwise you will get only last part of query and get syntax error during execution.

Upvotes: 1

user3017424
user3017424

Reputation: 65

Thanks to user lad2025 I found the problem. My second bit of code / SQL query does the trick. I just had a syntax error.

On a separate note, there seems to be a problem when reading views from VBScript.

Anyway solution is that I was using the following:

 strSQLQuery = strSQLQuery = "..."

That is supposed to be:

strSQLQuery = strSQLQuery & "..."

Upvotes: 0

Related Questions