Error concatenating string

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."Jason" <JayCallas@.hotmail.com> wrote in message
news:f01a7c89.0402051028.4aa7015e@.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|||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!

error after renaming server hosting SQL Server 2005

STEPS LEADING UP TO THE ERROR

1. renamed server
2. rebooted
3. ran the following script:

-- delete the server name currently stored in SQL Server
EXEC sp_dropserver @.@.servername
GO

-- set the server name stored in SQL Server to match the server name
DECLARE @.srvr sysname
SET @.srvr = CAST(SERVERPROPERTY('ServerName') AS sysname)
EXEC sp_addserver @.srvr, local
GO

-- Enable a loopback linked server after renaming the server.
DECLARE @.srvr sysname
SET @.srvr = CAST(SERVERPROPERTY('ServerName') AS sysname)
EXEC sp_serveroption @.srvr, 'data access', true
GO

4. rebooted

ERROR DESCRIPTION

Upon starting SSMS, the following error dialog appeared (Shift+Ctrl-C was used to capture the dialog box as text):

Microsoft SQL Server Management Studio

OK

As you can see the error dialog box is empty. It appears over what appears to be a blank SSMS. After clicking OK, SSMS appeared as it should.

I actually have the same problem and I haven't renamed anything. When I start SQL Server Managemnt Studio I get the blank message with the OK button as well.

Any suggestions?

error after renaming server hosting SQL Server 2005

STEPS LEADING UP TO THE ERROR

1. renamed server
2. rebooted
3. ran the following script:

-- delete the server name currently stored in SQL Server
EXEC sp_dropserver @.@.servername
GO

-- set the server name stored in SQL Server to match the server name
DECLARE @.srvr sysname
SET @.srvr = CAST(SERVERPROPERTY('ServerName') AS sysname)
EXEC sp_addserver @.srvr, local
GO

-- Enable a loopback linked server after renaming the server.
DECLARE @.srvr sysname
SET @.srvr = CAST(SERVERPROPERTY('ServerName') AS sysname)
EXEC sp_serveroption @.srvr, 'data access', true
GO

4. rebooted

ERROR DESCRIPTION

Upon starting SSMS, the following error dialog appeared (Shift+Ctrl-C was used to capture the dialog box as text):

Microsoft SQL Server Management Studio

OK

As you can see the error dialog box is empty. It appears over what appears to be a blank SSMS. After clicking OK, SSMS appeared as it should.

I actually have the same problem and I haven't renamed anything. When I start SQL Server Managemnt Studio I get the blank message with the OK button as well.

Any suggestions?