execute msdb..sp_start_job

[Former Member]

I like to to start a sql server 2000 job from my DI job (script) but having no luck so far .... any idea what is wrong?

The sql job name is in a parameter:

$Job_Record_Count = 'msdb.dbo.Record Count';

The command in the script is:

sql('DatastoreName', 'execute sp_start_job [$Job_Record_Count]');

It complains about the space in the job name but when I fix that, the message is that it can't find the sql server job ....

Any help would be great !!

[Former Member · Accepted solution]

is sp_start_job a user define stored procedure, instead of diretly calling if using sql function, have by tried importing the stored procedure in the datastore and calling it ?

if you use sql(), you need to pass the sql statment same as you would do from the SQL Query Analyzer or any command line tool.

Check from the SQL Server if you are able to execute the following command without any errors

execute sp_start_job msdb.dbo.Record Count

[Former Member]

It is indeed related to security and such .... I changed the owner of the job 'Record Count' to be the same as the userid in the datastore and then it works fine .... then I changed the owner of the job 'Record Count' back to sa but changed the server roles and database access of that userid in the datastore and it is working as well.

The error message in DI "The specified @job_name ('Record Count') does not exist.>.>." is a little confusing since it really is more not being authorized to execute it .... thanks a lot for all your help and suggestions !!

[Former Member]

Copied your code into the script and still get the error message:

Job name: test_script_2

(11.7) 01-26-09 11:37:11 (E) (6148:3056) DBS-070401: |Session test_script_2

ODBC data source <CORPBILLINGDATA> error message for operation <SQLFetch>: <[Microsoft][ODBC SQL Server Driver][SQL Server]The

specified @job_name ('Record Count') does not exist.>.

(11.7) 01-26-09 11:37:11 (E) (6148:3056) RUN-050304: |Session test_script_2

Function call <sql ( RM_Finance_Staging, BEGIN DECLARE @ret_val INT SELECT @ret_val execute msdb..sp_start_job 'Record Count'

END ) > failed, due to error <70401>: <ODBC data source <CORPBILLINGDATA> error message for operation <SQLFetch>:

<[Microsoft][ODBC SQL Server Driver][SQL Server]The specified @job_name ('Record Count') does not exist.>.>.

(11.7) 01-26-09 11:37:11 (E) (6148:3056) RUN-053008: |Session test_script_2

INFO: The above error occurs in the context <|Session test_script_2|sql(...) Function Body|>.

[Former Member]

Both of the statements below run fine in SQL Query Analyzer:

execute msdb..sp_start_job @job_name = 'Record Count'

execute msdb..sp_start_job 'Record Count'

Script in Data Integrator below does not run:

$Job_Record_Count = 'Record Count';

sql('RM_Finance_Staging', 'execute sp_start_job \'' || $Job_Record_Count || '\'');

Job Error Log: Job name: test_script_2

(11.7) 01-26-09 07:41:41 (E) (7800:1508) DBS-070401: |Session test_script_2

ODBC data source <CORPBILLINGDATA> error message for operation <SQLExecute>: <[Microsoft][ODBC SQL Server Driver][SQL

Server]Could not find stored procedure 'sp_start_job'.>.

(11.7) 01-26-09 07:41:41 (E) (7800:1508) RUN-050304: |Session test_script_2

Function call <sql ( RM_Finance_Staging, execute sp_start_job 'Record Count' ) > failed, due to error <70401>: <ODBC data

source <CORPBILLINGDATA> error message for operation <SQLExecute>: <[Microsoft][ODBC SQL Server Driver][SQL Server]Could not

find stored procedure 'sp_start_job'.>.>.

(11.7) 01-26-09 07:41:41 (E) (7800:1508) RUN-053008: |Session test_script_2

INFO: The above error occurs in the context <|Session test_script_2|sql(...) Function Body|>.

So I have added msdb.. to the statement in the script:

$Job_Record_Count = 'Record Count';

sql('RM_Finance_Staging', 'execute msdb..sp_start_job \'' || $Job_Record_Count || '\'');

It still fails - Job Error Log: Job name: test_script_2

(11.7) 01-26-09 07:46:49 (E) (4312:0528) DBS-070401: |Session test_script_2

ODBC data source <CORPBILLINGDATA> error message for operation <SQLExecute>: <[Microsoft][ODBC SQL Server Driver][SQL

Server]The specified @job_name ('Record Count') does not exist.>.

(11.7) 01-26-09 07:46:50 (E) (4312:0528) RUN-050304: |Session test_script_2

Function call <sql ( RM_Finance_Staging, execute msdb..sp_start_job 'Record Count' ) > failed, due to error <70401>: <ODBC data

source <CORPBILLINGDATA> error message for operation <SQLExecute>: <[Microsoft][ODBC SQL Server Driver][SQL Server]The

specified @job_name ('Record Count') does not exist.>.>.

(11.7) 01-26-09 07:46:50 (E) (4312:0528) RUN-053008: |Session test_script_2

INFO: The above error occurs in the context <|Session test_script_2|sql(...) Function Body|>.

I have tried all the other recommended suggestions as well but they all come back with the same issue .... ????

[werner_daehn]

The last version seems to be able to call the stored procedure as it returns the error

The specified @job_name ('Record Count') does not exist

This is an error raised by the stored procedure. Is it possible you are logged in as user sa in the SQL Query Analyzer and have chosen a specific database whereas the datastore is either using a different user or database??

[Former Member]

try with following syntax, this works fine

sql('DatastoreName', 'BEGIN DECLARE @ret_val INT SELECT @ret_val execute msdb..sp_start_job \'Record Count\' END');

The previous syntax is not working for me either, may be some issue with calling the procedure using ODBC call, have you tried creating a Datastore for msdb and importing this procedure in DI and executing it?

[werner_daehn]

This line here

sql('DatastoreName', 'execute sp_start_job $Job_Record_Count');

will send the string

execute sp_start_job $Job_Record_Count

to the database. But that's not what you want, you want to use the variable value.

sql('DatastoreName', 'execute sp_start_job ' || $Job_Record_Count);

or what is nicer to read is Manoj's version where he uses the parameter substitution syntax.

sql('DatastoreName', 'execute sp_start_job [$Job_Record_Count]');

But even that is not your goal. When you execute that procedure in SQL Server you added quotes, didn't you?

execute sp_start_job 'Record Count'

Hence your statement has to include those either by constructing the string or parameter substition with {}

sql('DatastoreName', 'execute sp_start_job \'' || $Job_Record_Count || '\'');

sql('DatastoreName', 'execute sp_start_job {$Job_Record_Count}');

[Former Member]

sp_start_job is a system stored procedure on msdb and i can execute the exact command ( execute sp_start_job msdb.dbo.Record Count ) in SQL Query Analyzer and everything works fine.

i am thinking maybe the syntax with a space in the job name is causing a problem?

[Former Member]

sql('DatastoreName', 'execute sp_start_job {$Job_Record_Count}');

or

sql('DatastoreName', 'execute sp_start_job \'Record Count\'');