Re: Unable to locate job scheduling stored procedures

From: Jacco Schalkwijk (NOSPAMjaccos_at_eurostop.co.uk)
Date: 02/18/04 

Date: Wed, 18 Feb 2004 10:43:30 -0000

Hi Andrew,

sp_add_job and all other SQL Server Agent stored procedure exist in the msdb
database, not in the master database. That means that when you call these
stored procedures from another database without prefixing them with msdb..,
the name won't be resolved automatically to the correct database, as happens
with stored procedures whose name starts with sp_ and that exist in the
master database.

You can fix this by either starting your script with "USE msdb", or by
calling the sp_add_job stored procedure with it's fully qualified name using
EXEC msdb..sp_add_job. 

-- 
Jacco Schalkwijk
SQL Server MVP
"Andrew Rusling" <anonymous@discussions.microsoft.com> wrote in message
news:209BAE71-4C34-4FDE-89DE-7048F4A283B6@microsoft.com...
> Hello all, I have written some osql scripts to perform scheduled backups.
However when I attempt to run them on MSDE2000 it informs me "Could not find
stored procedure 'sp_add_job'." I search the sysobjects table for that SP
and it does not exist. All of the help on the web says use sp_add_job to
schedule a backup task for MSDE. I am wondering if there is another package
that I need to install to get access to the SP? The first part of the script
I am using is below, maybe my script is incorrect?
> USE pcsDrugs
> EXEC sp_add_job @job_name = 'daily01',
>     @enabled = 1,
>     @description = 'pcsFullDatabaseBackupJob',
>     @owner_login_name = 'sa',
>     @notify_level_eventlog = 2,
>     @notify_level_email = 2,
>     @notify_level_netsend =2,
>     @notify_level_page = 2
> go
>
>
>

Relevant Pages

  • Re: Looking for data dictionary/definition integrity checking utility
    ... That script could come in handy in some situations. ... (or in a stored procedure using the field). ... >>database, i.e. all table names, field names, etc. ... >>the identifier in each location. ...
    (microsoft.public.sqlserver.programming)
  • Re: Help with SQL 2005 and Sourcesafe 6
    ... I understand where you're coming from but if you open up the .sql script ... stored procedure in the database that it belongs to. ...
    (microsoft.public.sqlserver.tools)
  • Re: What does the .run method return
    ... not a database stored procedure. ... 'stored procedure', yet your example is a VBS script. ... I have a batch job that calls a VBscript. ...
    (microsoft.public.scripting.vbscript)
  • Re: CDO to send mail via .vbs
    ... It doesn't work when I run the query in query analyzer. ... database I am querying doesn't have the stored procedure.. ... Could not find stored procedure 'master.dbo.xp_smtp_sendmail' ... I did decide to run my script from Windows scheduler instead of a DTS ...
    (microsoft.public.sqlserver.programming)
  • Re: Can I restore msdb from another sqlserver s backup
    ... It is not required to restore the MSDB from another server. ... Login to sql server using query analyzer ... Drop the MSD database ... Just execute the script. ...
    (microsoft.public.sqlserver.server)