I want some Moore

Blog about stuff and things and stuff. Mostly about SQL server and .Net
posts - 219, comments - 2287, trackbacks - 33

My Links

Advertisement

News

Hi! My name is 
Mladen Prajdić  I'm from Slovenia and I'm currently working as a .Net (C#) and SQL Server developer.

I also speak at local user group meetings and conferences like SQLBits and NT Conference
Welcome to my blog.
SQL Server MVP

My Books

SQL Server MVP Deep Dives 2
The Red Gate Guide to SQL Server Team based Development Free e-book

My Blog Feed via Email
Follow MladenPrajdic on Twitter


Users Online: who's online

Article Categories

Archives

Post Categories

Cool software

Other Blogs

Other stuff

SQL stuff

SQL Server: How to run a stored procedure at SQL Server start-up

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.

 

kick it on DotNetKicks.com

Print | posted on Tuesday, August 14, 2007 4:34 PM | Filed Under [ SQL Server ]

Feedback

Gravatar

# re: SQL Server: How to run a stored procedure at SQL Server start-up

its nice...
if stored procedure return the value like function?
11/23/2007 12:12 PM | Malaravan
Gravatar

# re: SQL Server: How to run a stored procedure at SQL Server start-up

Very useful, thanks!
1/16/2008 10:07 AM | riaz
Gravatar

# re: SQL Server: How to run a stored procedure at SQL Server start-up

Question - if this is a startup procedure, does it run BEFORE anything else gets in?
2/20/2008 3:50 PM | LinuxLars
Gravatar

# re: SQL Server: How to run a stored procedure at SQL Server start-up

and by anything else you mean what?
2/20/2008 3:54 PM | Mladen
Gravatar

# re: SQL Server: How to run a stored procedure at SQL Server start-up

Use this code in 2005 to tell you which procs are set to run at startup
select name
from sys.objects
where type = 'p'
and OBJECTPROPERTY(object_id, 'ExecIsStartup') = 1
11/4/2008 3:52 PM | vince
Gravatar

# re: SQL Server: How to run a stored procedure at SQL Server start-up

ALter proc [dbo].[dbspace_build_sql] as
declare @DBname varchar(36),@sql_text_out varchar(255),@sql_text_out2 varchar(255)
declare DBname_cursor cursor
for
select name from master..sysdatabases where
DATABASEPROPERTY(name, 'IsSuspect') != 1 and DATABASEPROPERTY(name, 'IsOffline') != 1
and DATABASEPROPERTY(name, 'IsNotRecovered') != 1 and DATABASEPROPERTY(name, 'IsInStandBy') != 1
and DATABASEPROPERTY(name, 'IsInRecovery') != 1 and DATABASEPROPERTY(name, 'IsInLoad') != 1
and DATABASEPROPERTY(name, 'IsDetached') != 1 and DATABASEPROPERTY(name, 'IsEmergencyMode') != 1
and name <> 'tempdb'
order by name
set nocount on
open DBname_cursor
fetch DBname_cursor into @DBname
while @@fetch_status = 0
begin
select @sql_text_out ='use '+@DBname+''
print @sql_text_out
select @sql_text_out ='go'
print @sql_text_out
select @sql_text_out ='exec sp_DBA_DATABASE_USAGE'
print @sql_text_out

fetch DBname_cursor into @DBname
end
close DBname_cursor
deallocate DBname_cursor


how to execute this query pls tell me ...
2/11/2010 6:19 AM | ajitesh malhotra
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET