Error concatenating string

[Jason]

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@@SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Import OCC Series Data',
@command = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME,
@database_name = N'',
@server = N'',
@database_user_name = N'',
@subsystem = N'CmdExec',
@cmdexec_success_code = 0,
@flags = 2,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME, everything works fine. I even tried declaring a local
variable named @command and setting it in the select statement, but no
dice.

[Simon Hayes]

"Jason" <Ja*******@hotmail.com> wrote in message
news:f0**************************@posting.google.c om...

I am trying to create a job that, as one of its steps, will kick off a
DTS package. As part of the command parameter, I need to concat a
system variable (@@SERVERNAME) to a constant string. I am receiving an
error about incorrect syntax near the +.

Here is the code for the job step.

-- Add the job steps
EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID,
@step_id = 1,
@step_name = N'Import OCC Series Data',
@command = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME,
@database_name = N'',
@server = N'',
@database_user_name = N'',
@subsystem = N'CmdExec',
@cmdexec_success_code = 0,
@flags = 2,
@retry_attempts = 0,
@retry_interval = 1,
@output_file_name = N'',
@on_success_step_id = 0,
@on_success_action = 3,
@on_fail_step_id = 0,
@on_fail_action = 3
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback

If I just try SELECT N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME, everything works fine. I even tried declaring a local
variable named @command and setting it in the select statement, but no
dice.

You can't build a parameter value 'dynamically' like that, but assigning the
entire string to a variable first should work:

declare @mycommand nvarchar(1000)
set @mycommand = N'DTSRun /F
D:\Databases\Scripts\DTS\ImportOCCSeriesData.dts /A DbName:8=' +
@@SERVERNAME

EXECUTE msdb.dbo.sp_add_jobstep
....
@command = @mycommand
....

What error did you get when you tried this?

Simon

[Jason Callas]

I ended up figuring that out and doing exactly what you suggested.
Something I did not know about passing parameters to stored
procedures...

The error I had gotten was
Incorrect syntax at '+'

Thanks for the help.

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!