SQL Server provides a system stored procedure sp_procoption that can be used to assign one or more stored procedures automatically when the SQL Server service is started. This is a good choice and it can be used for a variety of purposes. For example, you might have a large, expensive query in your database that will take some time to execute first. By using sp_procoption, you can run this query at server startup to precompile the execution plan so that one of your users will not be the first unfortunate person to run this particular query. I used this feature to create an automatic execution feature of the profiler's server-side tracking that I wrote myself, which became part of a stored procedure that was set to execute automatically when the server was started.
Copy Code code as follows:
Sp_procoption Parameters
exec sp_procoption @ProcName = [' Stored procedure name '],
@OptionName = ' STARTUP ',
@OptionValue = [On|off]
The following is an explanation of the sp_procoption stored procedure parameters:
The L parameter @procname meaning is obvious, it is the name of the procedure that is marked to be executed automatically.
L parameter @optionname is optional and the only valid option is startup.
L parameter @optionvalue Toggle automatic execution of the open and closed.
Use sp_procoption with specific restrictions:
L You must log on to the server as a system administrator and use sp_procoption.
L You can only assign standard stored procedures, extended stored procedures, or CLR stored procedures to start.
L stored procedures must be on the master-slave database.
L stored procedures cannot require any input parameters or return any output parameters.
In the following example, I create a stored procedure that automatically executes every time my instance of SQL Server is started. The purpose of this stored procedure is to write a row in the database table that records the service startup time. With this database table, I have an idea of getting the server to run normally. The following script creates a new database that stores a metric table called Server_startup_log. This table retains the date and time the last server was started. Once this basic architecture is established, I create a stored procedure that will be used to insert the date and time into the table when the server is started. Note that the stored procedure is created on the master-slave database.
Current 1/2 page
12 Next read the full text
