Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations TouchToneTommy on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Scheduling Stored Procedures 2
- Thread starter Waynest
- Start date
-
1
- #2
Congratulations!!
In Enterprise Manager, goto Tools menu and pick Job Scheduling, pick T-SQL command select your database and enter "exec spYourProcedure" and continue throught the wizard.
This will create a job which you can see in EM under Management, SQL Server Agent, Jobs.
p.s. Make sure SQL Server Agent service is running or your job wont execute
In Enterprise Manager, goto Tools menu and pick Job Scheduling, pick T-SQL command select your database and enter "exec spYourProcedure" and continue throught the wizard.
This will create a job which you can see in EM under Management, SQL Server Agent, Jobs.
p.s. Make sure SQL Server Agent service is running or your job wont execute
ColinM, Please let me know how I can create a stored procedure with the following syntax:
USE Eng_Survey
EXEC sp_addumpdevice 'disk', 'Eng_Survey_backup_device', 'h:\sqldb_backup\Eng_Survey_backup.dat'
BACKUP DATABASE Eng_Survey TO Eng_Survey_backup_device WITH FORMAT
USE Eng_CE_Council
EXEC sp_addumpdevice 'disk', 'Eng_CE_Council_backup_device', 'h:\sqldb_backup\Eng_CE_Council_backup.dat'
BACKUP DATABASE Eng_CE_Council TO Eng_CE_Council_backup_device WITH FORMAT
------------------------------
I get an error saying that "a USE database statement is not allowed". Please advise if you can. Thanks!
USE Eng_Survey
EXEC sp_addumpdevice 'disk', 'Eng_Survey_backup_device', 'h:\sqldb_backup\Eng_Survey_backup.dat'
BACKUP DATABASE Eng_Survey TO Eng_Survey_backup_device WITH FORMAT
USE Eng_CE_Council
EXEC sp_addumpdevice 'disk', 'Eng_CE_Council_backup_device', 'h:\sqldb_backup\Eng_CE_Council_backup.dat'
BACKUP DATABASE Eng_CE_Council TO Eng_CE_Council_backup_device WITH FORMAT
------------------------------
I get an error saying that "a USE database statement is not allowed". Please advise if you can. Thanks!
-
1
- #4
tlbroadbent
MIS
As the mesage says, USE database is not permitted in a stored procedure. However, it is not necessary in order to create a backup device or backup the database. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Thread starter
- #5
tlbroadbent
MIS
Wayne,
Colin posted his reply the same day that you posted the question. WildWest posted a new question on July 23 2002.
![[rofl]](/data/assets/smilies/rofl.gif "[rofl] [rofl]")
WildWest,
It is best to start a new thread and not use a year-old thread. In addition, it is best to post a question without requesting help from a specific member. There are many who can help. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
Colin posted his reply the same day that you posted the question. WildWest posted a new question on July 23 2002.
It is best to start a new thread and not use a year-old thread. In addition, it is best to post a question without requesting help from a specific member. There are many who can help. Terry L. Broadbent - DBA
Computing Links:
faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question