What is the error?

Question

What is the error?

Ken Powers

SSCommitted

Points: 1561
More actions
November 24, 2003 at 11:45 am

#83491

I created this stored procedure to load values into a database table.
NOTE: There are concatenation characters in lines 11, 12 and 14 of this code that do not display.
1 ALTER PROCEDURE ap_RemoteJobName
2@ServerName varchar(50)
3 AS
4 BEGIN
5 DECLARE @SCMD varchar(300)
6 DECLARE @SCMD2 varchar(300)
7 DECLARE @SCMD3 varchar(300)
8 DECLARE @SCMDTotal varchar(500)
9
10 SELECT @SCMD = 'Insert JobNames (JobID, ServerName, JobName)'
11 SELECT @SCMD2 = 'Select job_id as JobID, ' + @ServerName + ' as ServerName, name'
12 SELECT @SCMD3 = 'From ' + @ServerName + + '.msdb..sysdatabases'
13
14 SELECT @SCMDTotal = @SCMD + @SCMD2 + @SCMD
15
16 EXECUTE(@SCMDTotal)
17
18 RETURN @@error
19
20 END
No errors were reported when I ran the "create procedure" statement. However, when I attempt to execute the procedure I get this error:
Server: Msg 170, Level 15, State 1, Line 1
Line 1: Incorrect syntax near 'JobID'.
Where is the syntax error?
“If you're not outraged at the media, you haven't been paying attention.”

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

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

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 1

There is no column job_id located in msdb..sysdatabases. I think you might want to change that to sysjobs table if you are trying to find out the job name and ID.

mom

Ken Powers SSCommitted Points: 1561 More actions · Answer 2

Thanks for that catch. However, I'm still getting the same syntax error even after I changed the script to sysjobs.

“If you're not outraged at the media, you haven't been paying attention.”

Sopheap Suy SSCertifiable Points: 7893 More actions · Answer 3

The only thing I could think of, is the fact that you have no space between each word so the final statement looks like

"Insert jobnames (JobID, ServerName, JobName)Select job_id as JobID,@ServerName as ServerName, nameFrom @ServerName.msdb..sysjobs"

This statement can not be excecuted.

mom

G.R.Prithiviraj Kulasingham SSCarpal Tunnel Points: 4104 More actions · Answer 4

In addition to the modifications mensioned,

@ServerName is a variable declared in the procedure and not in your dynamic statements

You want to include the value of @servername there. SO change the line 11 with

11 SELECT @SCMD2 = 'Select job_id as JobID, ''' + @ServerName + ''' as ServerName, name'

When passing a varchar you should include it with single quotatin marks

Cheers,

Preethiviraj Kulasingham

Cheers,
Prithiviraj Kulasingham

http://preethiviraj.blogspot.com/