Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

could not find stored procedure 'sp_add_jobserver'

Posted on 2007-11-20
3
Medium Priority
?
1,471 Views
Last Modified: 2012-08-13
i need to run a t-sql statement in a maintenance plan. the t-sql is written and functions correctly from query analyzer but i get an error message that i need to specify sp_add_jobserver for it to run in the maintenance plan. when i add this line into query analyzer with teh correct parameters i get the error message Msg 2812, Level 16, State 62, Line 2
Could not find stored procedure 'sp_add_jobserver'.

here is my query:


use master 
exec sp_add_jobserver @job_name = 'DBBackup.DBBackup' , @server_name = 'csvmsql2005'
 
DECLARE @DBName sysname
DECLARE @PrevDBName sysname
DECLARE @sql nvarchar(255)
DECLARE @StartName sysname
DECLARE @EndName sysname
 
SET @StartName = ''
SET @EndName = 'zzzzzzzzzzzzzzzzzzz'
 
SELECT TOP 1 @DBName = name FROM master.dbo.sysdatabases 
WHERE sid <> 0x01 AND name BETWEEN @StartName AND @EndName
ORDER BY name
WHILE @DBName IS NOT NULL
BEGIN
  PRINT 'Processing database ' + @DBName
  IF databasepropertyex(@DBName,'recovery') = 'FULL'
  BEGIN
    Print '  Recovery Mode is ' + convert(varchar,databasepropertyex(@DBName,'recovery')) + '. Setting it to SIMPLE.'
    SET @sql = N'ALTER DATABASE ' + CONVERT(nvarchar,@DBName) + N' SET RECOVERY SIMPLE'
    EXEC sp_executesql @sql
  END
  ELSE
    Print '  Recovery Mode is ' + convert(varchar,databasepropertyex(@DBName,'recovery')) + '.'
  
  SET @PrevDBName = @DBName
  SET @DBName = NULL
  SELECT TOP 1 @DBName = name FROM master.dbo.sysdatabases 
WHERE sid <> 0x01 AND name BETWEEN @StartName AND @EndName
AND name > @PrevDBName 
ORDER BY name
END

Open in new window

0
Comment
Question by:newimagent
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 20320574
sp_Add_jobServer  does not exist in master db, it is in msdb


use master
exec msdb.dbo.sp_add_jobserver @job_name = 'DBBackup.DBBackup' , @server_name = 'csvmsql2005'
0
 
LVL 3

Expert Comment

by:john_steed
ID: 20320590
Hi,

I think sp_add_jobserver is in the msdb database, try to specify that (since you're having "use master" on top of the script)

exec msdb.dbo.sp_add_jobserver....

hope this helps
0
 
LVL 1

Author Comment

by:newimagent
ID: 20321012
i tried it without the use master as well to no avail, but using exec msdb.do.sp_add_jobserver did the trick!

Thank you!
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
I have a large data set and a SSIS package. How can I load this file in multi threading?
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

650 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question