How to automate a stored procedure when you start SQL Server

Source: Internet
Author: User

This article explains how to execute a stored procedure problem when you start SQL Server.

How do I execute a stored procedure when I start SQL Server?

Create the stored procedure in the master database, and then locate the stored procedure in Enterprise Manager-right--Properties--check "execute whenever SQL Server starts".

or after the stored procedure is created in master, the execution statement is set to start automatically

use master
exec sp_procoption '存储过程名','startup','on'

Automatic execution of stored procedures

SQL Server can automatically execute one or more stored procedures when it starts. These stored procedures must be created by the system administrator and executed as background procedures under the sysadmin fixed server role. These procedures cannot have any input parameters.

There is no limit to the number of startup procedures, but be aware that each startup process consumes one connection at execution time. If you must execute multiple procedures at startup, but do not need to execute in parallel, you can specify a procedure to use as a startup procedure to call another procedure. This takes up only one connection.

When the last database is restored at startup, the stored procedure is started. To skip execution of these stored procedures, specify the startup parameters as trace flag 4022. If you start SQL Server with the lowest configuration (with the-f tag), the startup stored procedure is not executed. For more information, see Trace Flags.

To create a startup stored procedure, you must be logged on as a member of the sysadmin fixed server role and create a stored procedure in the master database. You can use sp_procoption to specify an existing stored procedure as a startup process. The blocking process executes when SQL Server starts.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.