Home Microsoft SQL Server DigiMailing iRN Contact
    Keyword



sys.sp_procoption

 

Sets stored procedure for autoexecution. A stored procedure that is set to autoexecution runs every time an instance of SQL Server is started if the sp is stored in the master database. sp_procoption is available since SQL Server 7 and update in SP3 of this version.

Execution syntax:
sp_procoption [ @ProcName = ] 'procedure'
    , [ @OptionName = ] 'option'
    , [ @OptionValue = ] 'value'
 

[ @ProcName = ] 'procedure'
Is the name of the procedure for which to set an option. procedure is nvarchar(776), with no default.
[ @OptionName = ] 'option'
Is the name of the option to set. The only value for option is startup.
[ @OptionValue = ] 'value'
Is whether to set the option on (true or on) or off (false or off). value is varchar(12), with no default.


 

This is a procedure in the mssqlsystemresource database.




Syntax
create procedure sys.sp_procoption
    @ProcName		nvarchar(776)
   ,@OptionName		varchar(35)
   ,@OptionValue	varchar(12)
as
	-- DECLARE VARIABLES
	declare	@tabid		int
			,@uid		int
			,@opt_value	int
			,@opt_flag	bit
			,@dbname	sysname

    -- DISALLOW USER TRANSACTION 
	set nocount on
	set implicit_transactions off
	if @@trancount > 0
	begin
		raiserror(15002,-1,-1,'sys.sp_procoption')
		return @@error
	end

	-- VALIDATE OPTION NAME AND VALUE
	select @opt_flag =
		case
			when (lower(@OptionValue) in ('1' ,'on' ,'yes' ,'true')) then 1
			when (lower(@OptionValue) in ('0' ,'off' ,'no' ,'false')) then 0
		end
	if (@opt_flag is null) OR (isnull(lower(@OptionName), '') <> 'startup')
	begin
		raiserror(15600,-1,-1, 'sys.sp_procoption')
		return @@error
	end
	
	-- RESOLVE GIVEN OBJECT NAME 
	select @tabid = object_id, @uid = schema_id from sys.objects
		where object_id = object_id(@ProcName, 'local') and type in ('X','P','PC')
	
	if @tabid is null
	begin
		raiserror(15165,-1,-1,@ProcName)
		return @@error
	end
	
	BEGIN TRANSACTION
	
	-- LOCK PROC & CHECK PERMISSION 
	EXEC %%Object(MultiName = @ProcName).LockMatchID(ID = @tabid, Exclusive = 1, BindInternal = 0)
	if @@error <> 0
	begin
		ROLLBACK TRANSACTION
		raiserror(15165,-1,-1,@ProcName)
		return @@error
	end
		
	-- MUST BE sysadmin (Startup-procs run as sysadmin) 
	if is_srvrolemember('sysadmin') = 0
	begin
		--In case of failure Audit the event. Note in case of success the event will be audited when we lock the
		-- the object in exclusive mode and evaluate the permissions again through the security manager.
		if not (@tabid is null)
		begin
			EXEC %%System().AuditEvent(ID = 1128350287, Success = 0, TargetLoginName = NULL, TargetUserName = NULL, Role = NULL, Object = @ProcName, Provider = NULL, Server = NULL)
		end
		ROLLBACK TRANSACTION
		raiserror(15165,-1,-1,@ProcName)
		return @@error
	end
	
	-- STARTUP PROC MUST BE OWNED BY DBO IN MASTER 
	if (db_id() <> 1 OR @uid <> 1)
	begin
		ROLLBACK TRANSACTION
		raiserror(15398,-1,-1)
		return @@error
	end
	
	-- VALID OBJECT IN DATABASE? 
	if @tabid is null
	begin
		ROLLBACK TRANSACTION
		select @dbname = db_name()
		raiserror(15009,-1,-1 ,@ProcName, @dbname)
		return @@error
	end

	-- PROC CANNOT HAVE PARAMETERS 
	if exists ( select * from sys.parameters where object_id = @tabid )
	begin
		ROLLBACK TRANSACTION
		raiserror(15399,-1, -1)
		return @@error
	end

	EXEC %%Module(ID = @tabid).SetStartup(Value = @opt_flag)

	-- Set Config option for startup procs
	set @opt_value =
			case when exists (select * from sys.objects where type in ('X','P','PC')
				and ObjectProperty(object_id, 'ExecIsStartup') = 1)
			then 1 else 0 end
	EXEC %%ServerConfiguration(ConfigID = 1547).SetValue(Value = @opt_value)

	-- EMDEventType(x_eet_AlterProc), EMDUniversalClass(x_eunc_Table), src major id, src minor id, src name
	-- -1 means ignore target stuff, target major id, target minor id, target name,
	-- # of parameters, 5 parameters
	EXEC %%System().FireTrigger(ID = 52, ID = 1, ID = @tabid, ID = 0, Value = @ProcName,
		ID = -1, ID = 0, ID = 0, Value = NULL, ID = 3,
		Value = @ProcName, Value = @OptionName, Value = @OptionValue, Value = NULL, Value = NULL, Value = NULL, Value = NULL)

	-- Commit and reconfigure
	COMMIT TRANSACTION
	RECONFIGURE WITH OVERRIDE

	-- RETURN SUCCESS
	RETURN 0 -- sp_procoption

 
Last revision 2008RTM
See also

  sp_adddistributor (Procedure)
sp_dropdistributor (Procedure)
sp_replicationdboption (Procedure)
sp_vupgrade_publisher (Procedure)
       



News

  Query a named instance
Sybase+ASE+silent+install
SQL 2008 R2
Deprecated procedures in SQL2008
Reporting Services item-level role definitions
Create all your missing indexes
Converting datetime field
Start MSSQL Server Profiler at time
Replicating MSSQL Server views
Exploring Microsoft Sharepoint
The OLE DB provider "SQLNCLI10" for linked server indicates.
Mobile solar charger
Oracle to SQL Server replication
Cannot insert the value NULL into column
Undocumented Microsoft SQL Server 2008
VMware
Zoekmachine optimalisatie
SQL Servers hidden objects
FckEditor
Flash in Ajax
De hype die AJAX heet
Barcode scannen
MySQL; Gratis, makkelijk en snel!
PHP
sIFR; de combinatie tussen HTML en Flash