CF application to run SQL 2k JOB?

[tej_web]

Hello All,

I would like to build an application to run a MS SQL Job.

The Job is located in \\ server \ Management \ SQL Server Agent \ Jobs .

II need to connect from an users desktop, have them click a button and run the SQL JOB.

Has any one done this?  Any information would help.

Thank you

TJones

[JR__Bob__Dobbs-qSBHQ2 · Accepted solution]

You can use the sp_start_job stored procedure to start the job
http://msdn.microsoft.com/en-us/library/aa260289(SQL.80).aspx

To call a stored procedure in CF use the CFSTOREDPROC tag.

[tej_web]

Hi Bob,

Thanks for the response.

I have a few quick questions.

I have read the MS start_job info and receive an error when saving the Stored Procedure.

This is the sequence that I am using.

CFSTOREDPROC procedure="LTC" datasource="runstored"

/CFSTOREDPROC

MSSQL

CREATE PROCEDURE [owner].[LTC] AS

USE msdb
EXEC sp_start_job @job_name = 'LTC'

GO

This is the end result,

"error154:  a USE database statement is not allowed in a procedure or trigger".

Any working examples?  Have I missed something?

Job path  = console root\Microsoft Sql Servers\SQL Server Group\(local)(Windows NT)\Management\SQL Server Agent\Jobs

job name = 'LTC'

Thanks again for the response.

TJones

[JR__Bob__Dobbs-qSBHQ2]

Assuming that your datasource's default database is not 'msdb' you will need to use the qualified name of the stored procedure: database_name.schema_name.object_name.  Since 'dbo' is the default schema it can be left blank.

See T-SQL and ColdFusion examples below.  Be aware that I have not tested either of these.

T-SQL:

EXEC msdb.dbo.sp_start_job @job_name = 'LTC'

OR

EXEC msdb..sp_start_job @job_name = 'LTC'

In ColdFusion (You will need to add values or specify null="yes" for each cfprocparam )
<cfstoredproc procedure="msdb.dbo.sp_start_job" datasource="#mydsn#" returncode="yes" result="storedProc">
    <cfprocparam value="" cfsqltype="cf_sql_varchar" type="in" maxlength="128" />  <!--- @job_name  nvarchar(128) IN --->
    <cfprocparam value="" cfsqltype="cf_sql_char" type="in" />  <!--- @job_id  uniqueidentifier IN --->
    <cfprocparam value="" cfsqltype="cf_sql_integer" type="in" />  <!--- @error_flag  int IN --->
    <cfprocparam value="" cfsqltype="cf_sql_varchar" type="in" maxlength="128" />  <!--- @server_name  nvarchar(128) IN --->
    <cfprocparam value="" cfsqltype="cf_sql_varchar" type="in" maxlength="128" />  <!--- @step_name  nvarchar(128) IN --->
    <cfprocparam value="" cfsqltype="cf_sql_integer" type="in" />  <!--- @output_flag  int IN --->
</cfstoredproc>

You should review the SQL documentation and your datasource settings to verify that the user account associated with your datasource login has sufficient rights to start a job and to invoke procedures in the msdb database.

Info on object names in SQL Server:
http://msdn.microsoft.com/en-us/library/ms187879%28SQL.90%29.aspx