This is acctually quite simple. There is 'startup' option that you can set to the procedure.
There are a few limitations though:
- your stored pricedure must reside in the [master] database
- it's owner must be dbo
- it musn't have any input or output parameters
Here is some sample code:
USE master;
GO
-- first set the server to show advanced options
EXEC sp_configure 'show advanced option', '1';
RECONFIGURE
-- then set the scan for startup procs to 1
EXEC sp_configure 'scan for startup procs', '1';
RECONFIGURE
IF OBJECT_ID('spTest') IS NOT NULL
DROP PROC spTest
GO
-- crate a test stored procedure
CREATE PROC spTest
AS
-- just create a sample database
EXEC('CREATE database db1')
GO
-- set it to run at sql server start-up
exec sp_procoption N'spTest', 'startup', 'on'
Note that each stored procedure run at start up takes up one worker thread until finished. So if you want to run multiple sprocs at runtime
and parallelism doesn't matter create one sproc that executes all others.
