Syntax doesn't work 2

[thamms]

Help, I can't get this to work:

declare @JobName nvarchar(100)
SET @JobName = 'XXX'
sp_add_jobstep
@job_name = @JobName,
@step_name = N'Run job step',
@subsystem = N'TSQL',
@command = N'EXEC Staging.dbo.test'

Error:
Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'sp_add_jobstep'.

THANKS

 

[gmmastros]

[!]exec[!] sp_add_jobstep

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[PatriciaObreja]

Try:
exec sp_add_jobstep

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[thamms]

Thanks...

Now I am having this problem. Here's my code:

alter PROCEDURE [dbo].[CreateAndRunDynamicJob] (@JobName NVARCHAR(400), @DBName NVARCHAR(400), @SPName NVARCHAR(400), @Params NVARCHAR(400))
AS
BEGIN

DECLARE
@SQL NVARCHAR(400)

SET @SQL = 'sp_add_job @job_name = ' + @JobName
EXECUTE SP_EXECUTESQL @SQL

SET @SQL = 'EXEC sp_add_jobstep
			@job_name = ' + @JobName +',
			@step_name = N''Run job step'',
			@database_name = ' + @DBName + ',
			@subsystem = N''TSQL'',
			@command = ' + @SPName + ' ' + @Params
EXECUTE SP_EXECUTESQL @SQL
print @SQL

SET @SQL = 'sp_add_jobserver @job_name = ' + @JobName
EXECUTE SP_EXECUTESQL @SQL

END

testsp:
alter procedure testsp (@input int)
as 
begin
  insert into test values(@input)
end

CreateAndRunDynamicJob @JobName = 'XXX', @DBName = 'Staging', @SPName = 'testsp', @Params = '300'
OR
scsp_CreateAndRunDynamicJob @JobName = 'XXX', @DBName = 'Staging', @SPName = 'testsp', @Params = 300

(yield the same error, see below)


ERROR:
Msg 102, Level 15, State 1, Line 6
Incorrect syntax near '300'.

Help, thanks!

 

[PatriciaObreja]

You mean you get this error when you try to execute each of the 2 stored procedures?

First put exec before calling each stored procedure.

And check this parameter "@Params = 300" if it's a number of character type.

-------------------------------------------------------------------------------------------------------------------------
"Now I can look at you in peace; I don't eat you any more." Franz Kafka, while admiring fish in an aquarium

 

[thamms]

I figured it out:

@command = ' + 'N'''+ '' + @SPName + ' ' + @Params + ''''