Help with concatenate -

Question

Help with concatenate -

Susan-322874

SSCrazy

Points: 2300
More actions
March 15, 2011 at 12:42 pm

#235163

I need to modify a job that is on multiple servers and includes the servername in the job.
I have this (below), but it's not working, what am I doing wrong?
declare @servername nvarchar(255)
set @servername = (select @@servername)
EXEC dbo.sp_update_jobstep
@job_name = 'P3_Lash_['+@servername+']_Get_Blocking',
@step_id = 1,
@database_name = 'master' ;
I get the following error message -
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '+'.

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply

Jeff Moden SSC Guru Points: 1004356 More actions · Answer 1

You cannot do a caclulation directly in the parameter of a stored proc. Precalculate the value in a vaiable and then use that variable.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jason Selburg SSC-Insane Points: 24641 More actions · Answer 2

Susan-322874 (3/15/2011)
I need to modify a job that is on multiple servers and includes the servername in the job.
I have this (below), but it's not working, what am I doing wrong?
declare @servername nvarchar(255)
set @servername = (select @@servername)
EXEC dbo.sp_update_jobstep
@job_name = 'P3_Lash_['+@servername+']_Get_Blocking',
@step_id = 1,
@database_name = 'master' ;
I get the following error message -
Msg 102, Level 15, State 1, Line 5
Incorrect syntax near '+'.

You can't concatenate during the procedure call. You'll need to define the job's name before the procedure call. Also, you can eliminate the servername variable if you want.

DECLARE @jobName NVARCHAR(280)

SELECT @jobName = N'P3_Lash_[' + @@servername + N']_Get_Blocking'

EXEC dbo.sp_update_jobstep

@job_name = @jobName,

@step_id = 1,

@database_name = 'master' ;

______________________________________________________________________

Personal Motto: Why push the envelope when you can just open it?

If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg

Susan-322874 SSCrazy Points: 2300 More actions · Answer 3

Yes, I took your advise and am doing the following -

USE msdb ;

GO

declare @dbname nvarchar(255)

set @dbname= (select +'P3_Lash_['+@@servername+']_Get_Blocking')

EXEC dbo.sp_update_jobstep

@job_name = @dbname,

@step_id = 1,

@database_name = 'master' ;

Thanks for your help!