Skip to main content
tej_web
tej_web
Inspiring
September 17, 2009
Answered

CF application to run SQL 2k JOB?

  • September 17, 2009
  • 1 reply
  • 2066 views

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

This topic has been closed for replies.
Correct answer JR__Bob__Dobbs-qSBHQ2

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.

1 reply

J
JR__Bob__Dobbs-qSBHQ2Correct answer
Inspiring
September 23, 2009

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
tej_webAuthor
Inspiring
September 30, 2009

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

J
JR__Bob__Dobbs-qSBHQ2
Inspiring
September 30, 2009

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

Terms & ConditionsAccessibility statement
Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices