Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Controlling SQL Server Settings

By Andre Vigneau,

Haven't you dreamt about a way to prevent the change of some parameters by some people that still have system administrators privilege and that can seriously put in danger your SQL Server and applications running on them. I know I have this nightmare.

I work for a software company and one of my tasks is to play the support level 3 role (last level) of over 500 SQL Server installations. How many times I have had to troubleshoot some problems at a customer site and finally find out that someone had played around with some SQL Server settings. If you are looking for a way to keep control over some SQL Server settings while you are not there here is the solution.

This is the way I structure the procedure calls. I have one startup stored procedure responsible for making sure the SQL Server Agent service is running. Then the startup stored procedure creates or re-creates the job that will run the parameter check stored procedure. Then the control goes to the SQLAgent to run the sp__OptionMonitor procedure by having the startup procedure start the newly created job.

The sp__OptionMonitor procedure is the one that does all the checks and re-settings. You will be able to add or to comment out all the pieces that you want. The most complicated part is the one taking care of the memory to allocated to SQL Server. I wanted to have a way to split memory between different SQL Server instances if any. As well, I wanted to be able to give some memory to particular applications when they were installed on the same server machine than is the SQL Server. This last part will need you to configure a bit to have it find what you want. Some settings are going to be applied only once the the service is restarted. No choice. So again your first question will be to ask when the SQL Server service was restarted last. Or find out in the error log files. You have some examples here. You can now apply this to your particular needs. Do not use this code without perfectly understanding its effects.

USE master
GO
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = OBJECT_ID(N'[dbo].[sp__OptionMonitor]') 
	AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp__OptionMonitor]
GO

CREATE PROC dbo.sp__OptionMonitor 
AS
/********************************************************************************************************
** Creation Date: 2002-10-02
** Midif. Date	: 2003-01-31
** Created By	: avigneau
** Database	: master
** Description  : To configure at startup SQL Server parameters  
		  
** Parameters   : none 

** Compatibility: SQL Server 7.0, 2000
** Remark	: Mod was applied to log whenever an automatic change is applied and to add checking on db chaining
		  new 2000 SP3 parameter.

** Example	: EXEC sp__OptionMonitor
******************************************************************************************************/
SET NOCOUNT ON
DECLARE @iError int, @vcRegValue varchar(255)
	,@vcKey varchar(512), @iOptionSetting int
	, @iConfigValue int, @iNeededValue int
	, @tiInstNodeServer tinyint, @tiInstSQLServer tinyint

DECLARE @vcCommand varchar(2048)

SELECT @tiInstNodeServer = 0,@iNeededValue = 0

-- DB & OS Version control START
DECLARE @SQLVersion varchar(10), @OSVersion dec(4,2)
CREATE TABLE #Version (IndexId int NOT NULL
			,Name varchar(60)
			,Internal_Value int NULL
			,Character_Value varchar(255) NULL)
INSERT #Version EXEC master.dbo.xp_msver
SELECT @SQLVersion = CONVERT(varchar(10),CONVERT(int,CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))*10))
FROM #Version
WHERE Name = 'ProductVersion'
SELECT @OSVersion = CONVERT(dec(4,2),SUBSTRING(Character_Value,1,4))
FROM #Version
WHERE Name = 'WindowsVersion'

DROP TABLE #Version
-- DB & OS Version control END

CREATE TABLE #ConfigValue
(
name varchar(64)
,minimum int
,maximum int
,config_value int
,run_value int
)

INSERT #ConfigValue EXEC sp_configure
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'show advanced options' AND run_value = 1) = 0
BEGIN
	EXEC master.dbo.sp_configure 'show advanced options', 1
	RECONFIGURE
	DELETE #ConfigValue
	INSERT #ConfigValue EXEC master.dbo.sp_configure
END

--*******************************************************************************************************
--******************************* max server memory option setup ****************************************
--*******************************************************************************************************
CREATE TABLE #INIFILE(Content varchar(8000))

---///////////////////////////////////////////////////////////////////////////////////////////////
--This following part need you to configure the parameter you want to look for or put in comments.
-- example to get information from a file where its location is stored in the registry
CREATE TABLE #KeyExist (KeyExist int)
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\A company name\A product name 1'
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
BEGIN
	EXEC @iError = master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey,@value_name='CurrentVersion'
	               ,@value=@vcRegValue OUTPUT
	SELECT @vcKey = @vcKey+'\'+@vcRegValue
	SELECT @vcRegValue = NULL
	EXEC @iError = master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey,@value_name='InstallPath'
	               ,@value=@vcRegValue OUTPUT
	SELECT @vcRegValue = REPLACE('TYPE "'+@vcRegValue+'\Bin\INIFile.ini"','\\','\')
	IF @vcRegValue IS NOT NULL
	BEGIN
		INSERT #INIFILE EXEC master.dbo.xp_cmdshell @vcRegValue
		IF (SELECT COUNT(*) FROM #INIFILE WHERE Content LIKE '%text to find as an indication%') <> 0
			SELECT @tiInstNodeServer = @tiInstNodeServer+1
	END
END
DELETE #KeyExist
-- Example to get the information you need directly from the registry for a value contents
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\A company name\A product name 2' 
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
BEGIN
	EXEC @iError = master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
					,@value_name='CurrentVersion'
	               ,@value=@vcRegValue OUTPUT
	IF convert(float,@vcRegValue) > 3.2
		SELECT @tiInstNodeServer = @tiInstNodeServer+1
END
DELETE #KeyExist
-- Example to get the information you need directly from the registry for a leaf name
SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\A company name\A product name 3' 
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
BEGIN
	SELECT @tiInstNodeServer = @tiInstNodeServer+1
END
DELETE #KeyExist
--This following part need you to configure the parameter you want to look for or put in comments.
---///////////////////////////////////////////////////////////////////////////////////////////////

-- Get number of installed SQL Server instances
CREATE TABLE #Instances (Value varchar(128), InstanceName varchar(128))

SELECT @vcRegValue = NULL,@vcKey = 'SOFTWARE\Microsoft\Microsoft SQL Server'
INSERT #KeyExist EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
IF (SELECT KeyExist FROM #KeyExist) = 1
	INSERT #Instances EXEC master.dbo.xp_regread @rootkey='HKEY_LOCAL_MACHINE',@key=@vcKey
	                  ,@value_name='InstalledInstances',@value=@vcRegValue OUTPUT
SELECT @tiInstSQLServer = count(*) from #Instances
IF @tiInstSQLServer = 0
	SELECT @tiInstSQLServer = 1

-- get available physical memory
CREATE TABLE #OptionSetting
(
IndexValue int
,Name varchar(64)
,Internal_Value int
,Character_Value varchar(128)
)                                                                                                                                                                                                    
INSERT #OptionSetting exec master.dbo.xp_msver PhysicalMemory
SELECT @iOptionSetting = Internal_Value FROM #OptionSetting


-- Get the SQL Server Memory Config value
SELECT @iConfigValue = config_value FROM #ConfigValue WHERE name = 'max server memory (MB)'

-- Calculate needed memory for other programs to limit memory taken by SQL Server
IF @tiInstNodeServer <> 0
BEGIN
	SELECT @iNeededValue = (@iOptionSetting -(@iOptionSetting*(((@iOptionSetting*((128.000000+@iOptionSetting) / 
	                        @iOptionSetting))-@iOptionSetting)/(@iOptionSetting*((128.000000+@iOptionSetting) / 
	                        @iOptionSetting)))*@tiInstNodeServer*(@tiInstNodeServer+1)/@tiInstNodeServer*.75)) /
	                        @tiInstSQLServer
	IF @iNeededValue < 16
		SELECT @iNeededValue = 16
END
ELSE
	SELECT @iNeededValue = @iOptionSetting/@tiInstSQLServer
-- Set the max memory value accordingly
IF @iConfigValue <> @iNeededValue
BEGIN
	IF @iNeededValue = @iOptionSetting OR (@tiInstNodeServer = 0 AND @tiInstSQLServer = 1)
	BEGIN
		IF @iConfigValue <> 0
		BEGIN
			EXEC master.dbo.sp_configure 'max server memory (MB)', 0
			EXEC master.dbo.xp_logevent 100000
			, 'The SQL Server parameter ''max server memory (MB)'' you earlier set will be reverted to its 
			default value on next SQL Server Service restart. To avoid this behavior see your system administrator.' 
			, 'WARNING'
		END
	END
	ELSE
	BEGIN
		EXEC master.dbo.sp_configure 'max server memory (MB)', @iNeededValue
		EXEC master.dbo.xp_logevent 100000
		, 'The SQL Server parameter ''max server memory (MB)'' you earlier set will be reverted to its 
		maximum value considering locally installed service needs on next SQL Server Service restart. 
		To avoid this behavior see your system administrator.' , 'WARNING'
	END
	RECONFIGURE WITH OVERRIDE
END

--***********************************************************************************************************
--*********************************** Other server settings options  ****************************************
--***********************************************************************************************************

-- reset all others to the needed defaults
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'affinity mask' AND run_value = 0) = 0
BEGIN
	EXEC master.dbo.sp_configure 'affinity mask',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''affinity mask'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'allow updates' AND run_value = 0) = 0
BEGIN
	EXEC master.dbo.sp_configure 'allow updates',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''allow updates'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) 
		FROM #ConfigValue
		WHERE name = 'Cross DB Ownership Chaining' 
		AND run_value = 0) = 0 
	AND (SELECT COUNT(*) 
			FROM #ConfigValue 
			WHERE name = 'Cross DB Ownership Chaining') = 1
BEGIN
	EXEC master.dbo.sp_configure 'Cross DB Ownership Chaining',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''Cross DB Ownership Chaining'' you earlier 
	set will be reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'locks' AND run_value = 0) = 0
BEGIN
	EXEC master.dbo.sp_configure 'locks',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''locks'' you earlier set will be reverted 
	to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'max worker threads' AND run_value = 255) = 0
BEGIN
	EXEC master.dbo.sp_configure 'max worker threads', 255
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''max worker threads'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'min memory per query (KB)' AND run_value = 1024) = 0
BEGIN
	EXEC master.dbo.sp_configure 'min memory per query (KB)',1024
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''min memory per query (KB)'' you earlier set 
	will be reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'nested triggers' AND run_value = 1) = 0
BEGIN
	EXEC master.dbo.sp_configure 'nested triggers',1
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''nested triggers'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'open objects' AND run_value = 0) = 0
BEGIN
	EXEC master.dbo.sp_configure 'open objects',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''open objects'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'user connections' AND run_value = 0) = 0
BEGIN
	EXEC master.dbo.sp_configure 'user connections',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''user connections'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
IF (SELECT COUNT(*) FROM #ConfigValue WHERE name = 'user options' AND run_value = 0) = 0
BEGIN
	EXEC master.dbo.sp_configure 'user options',0
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''user options'' you earlier set will be 
	reverted to its default value on next SQL Server Service restart. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END

--***********************************************************************************************************
--*********************************** max degree of parallelism option setup ********************************
--***********************************************************************************************************

-- Until the BUG on parallelism is tested with SQL versions and positive applications
DELETE #OptionSetting
INSERT #OptionSetting exec master.dbo.xp_msver ProcessorCount
SELECT @iOptionSetting = Internal_Value FROM #OptionSetting
SELECT @iConfigValue = config_value FROM #ConfigValue WHERE name = 'max degree of parallelism'
IF @iOptionSetting > 1 AND @iConfigValue <> 1
BEGIN
	EXEC master.dbo.sp_configure 'max degree of parallelism',1
	RECONFIGURE WITH OVERRIDE
	EXEC master.dbo.xp_logevent 100000, 'The SQL Server parameter ''max degree of parallelism'' was set to ''1'' 
	since some queries still does not perform well under multiple processors system. 
	To avoid this behavior see your system administrator.' , 'WARNING'
END
DROP TABLE #ConfigValue
--******************************************************************************************************
--********************************** Database option control setup *************************************
--******************************************************************************************************

CREATE TABLE #dboption(OptionName varchar(128))
DECLARE crDB CURSOR
READ_ONLY
FOR SELECT name FROM master.dbo.sysdatabases WHERE name NOT IN ('master','tempdb')

DECLARE @DBName varchar(40)
OPEN crDB

FETCH NEXT FROM crDB INTO @DBName
WHILE (@@fetch_status <> -1)
BEGIN
	IF (@@fetch_status <> -2)
	BEGIN
		SELECT @vcCommand = 'OSQL -S '+@@SERVERNAME+' -E -d' + @DBName + 
		       ' -n -w8000 -Q "EXEC master.dbo.sp_dbcmptlevel '+@DBName+', '+@SQLVersion+'"'
		EXEC master.dbo.xp_cmdshell @vcCommand, NO_OUTPUT
		INSERT #dboption EXEC master.dbo.sp_dboption @DBName
		-- not needed system wide. if needed set withing the applications
		--DELETE #dboption WHERE OptionName = 'select into/bulkcopy'
		--IF @@ROWCOUNT <> 1
		--	EXEC master.dbo.sp_dboption @DBName,'select into/bulkcopy',true
		DELETE #dboption WHERE OptionName = 'trunc. log on chkpt.'
		IF @@ROWCOUNT <> 1
		BEGIN
			SELECT @vcCommand = 'The database parameter ''trunc. log on chkpt'' was set to ''true'' automatically 
			for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
			EXEC master.dbo.sp_dboption @DBName,'trunc. log on chkpt.',true
			EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
		END
		DELETE #dboption WHERE OptionName = 'auto create statistics'
		IF @@ROWCOUNT <> 1
		BEGIN
			SELECT @vcCommand = 'The database parameter ''auto create statistics'' was set to ''true'' 
			automatically for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
			EXEC master.dbo.sp_dboption @DBName,'auto create statistics',true
			EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
		END
		DELETE #dboption WHERE OptionName = 'auto update statistics'
		IF @@ROWCOUNT <> 1
		BEGIN
			SELECT @vcCommand = 'The database parameter ''auto update statistics'' was set to ''true'' 
			automatically for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
			EXEC master.dbo.sp_dboption @DBName,'auto update statistics',true
			EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
		END
		DELETE #dboption WHERE OptionName = 'torn page detection'
		IF @@ROWCOUNT <> 1
		BEGIN
			SELECT @vcCommand = 'The database parameter ''torn page detection'' was set to ''true'' 
			automatically for database '''+@DBName+'''. To avoid this behavior see your system administrator.'
			EXEC master.dbo.sp_dboption @DBName,'torn page detection',true
			EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
		END
		DELETE #dboption 
		WHERE OptionName in ('subscribed','published','merge puplished','dbo use only','offline','single user')
		IF EXISTS (SELECT * FROM #dboption)
		BEGIN
			UPDATE #dboption SET OptionName = 'EXEC master.dbo.sp_dboption '+@DBName+','''+OptionName+''',false'
			WHILE EXISTS (SELECT * FROM #dboption)
			BEGIN
				SET ROWCOUNT 1
				SELECT @vcCommand = OptionName FROM #dboption
				SET ROWCOUNT 0
				IF @vcCommand = 'EXEC master.dbo.sp_dboption msdb,''db chaining'',false'
				BEGIN
					DELETE #dboption WHERE OptionName = @vcCommand
					CONTINUE
				END
				EXEC (@vcCommand)
				DELETE #dboption WHERE OptionName = @vcCommand
				SELECT @vcCommand = 'The database parameter '
					+SUBSTRING(SUBSTRING(@vcCommand,PATINDEX('%,%',@vcCommand)+1,LEN(@vcCommand) - 
					PATINDEX('%,%',@vcCommand)),1
					,PATINDEX('%,%',SUBSTRING(@vcCommand,PATINDEX('%,%',@vcCommand)+1,LEN(@vcCommand) - 
					PATINDEX('%,%',@vcCommand)))-1)
					+' was set to ''false'' automatically for database '''+@DBName+'''. 
					To avoid this behavior see your system administrator.'
				EXEC master.dbo.xp_logevent 200000, @vcCommand , 'WARNING'
			END
		END
	END
	FETCH NEXT FROM crDB INTO @DBName
END
CLOSE crDB
DEALLOCATE crDB

--*****************************************************************************************************
--************************************************** END **********************************************
--*****************************************************************************************************
GO

Because many programs I have written depend on the SQL Agent Service and to really make sure that no one has configured this service to run manually (you see many things in life) I rely on this little piece of code

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp__CheckSQLAgent]')
    and OBJECTPROPERTY(id, N'IsProcedure') = 1)
  drop procedure [dbo].[sp__CheckSQLAgent]
GO
CREATE PROC dbo.sp__CheckSQLAgent
AS

/************************************************************************************************
** Creation Date: 2002-10-02
** Created By	: avigneau
** Database	: master
** Description  : If SQL Agent is not already running run it  
		  
** Parameters   : none (could be flagged for startup)

** Compatibility: SQL Server 7.0, 2000
** Remark	: You need the privilege to run xp_cmdshell extended.
		  
** Example	: EXEC sp__CheckSQLAgent
****************************************************************************************************/

--****************************************************************************************************
--************************ Make sure SQL Agent Service is running ************************************
--****************************************************************************************************
SET NOCOUNT ON
DECLARE @flag int,@vcCommand varchar(2048)
EXEC @flag = msdb..sp_sqlagent_notify 'G'
IF @flag <> 0
BEGIN
	SELECT @vcCommand = 'NET START '+CASE WHEN PATINDEX('%\%',@@SERVERNAME) = 0 THEN 'SQLSERVERAGENT' 
		ELSE 'SQLAGENT$'+SUBSTRING(@@SERVERNAME,PATINDEX('%\%',@@SERVERNAME)+1,LEN(@@SERVERNAME) -
		PATINDEX('%\%',@@SERVERNAME))END
	EXEC master.dbo.xp_cmdshell @vcCommand
END

GO

IF EXISTS (SELECT * FROM dbo.sysobjects 
			WHERE id = OBJECT_ID(N'[dbo].[sp__ParamControl]') 
			AND OBJECTPROPERTY(id, N'IsProcedure') = 1)
DROP PROCEDURE [dbo].[sp__ParamControl]
GO

CREATE PROC dbo.sp__ParamControl 
AS
/****************************************************************************************************
** Creation Date: 2002-10-02
** Created By	: avigneau
** Database	: master
** Description  : To configure at startup SQL Server parameters  
		  
** Parameters   : none (flagged for startup)

** Compatibility: SQL Server 7.0, 2000
** Remark	: 
		  
** Example	: EXEC sp__ParamControl
******************************************************************************************************/

--*****************************************************************************************************
--************************ Make sure SQL Agent Service is running *************************************
--*****************************************************************************************************
SET NOCOUNT ON

EXEC dbo.sp__CheckSQLAgent

--*****************************************************************************************************
--************************ Refresh the JOB (Just in case someome modofied it) *************************
--*****************************************************************************************************

BEGIN TRANSACTION            
  DECLARE @JobID BINARY(16)  
  DECLARE @ReturnCode INT    
  SELECT @ReturnCode = 0     
IF (SELECT COUNT(*) FROM msdb.dbo.syscategories WHERE name = N'SQL Server Monitoring') < 1 
  EXECUTE msdb.dbo.sp_add_category @name = N'SQL Server Monitoring'

-- Delete the job with the same name (if it exists)
SELECT @JobID = job_id FROM msdb.dbo.sysjobs WHERE (name = N'Parameter Control') IF (@JobID IS NOT NULL) BEGIN -- Check if the job is a multi-server job IF (EXISTS (SELECT * FROM msdb.dbo.sysjobservers WHERE (job_id = @JobID) AND (server_id <> 0))) BEGIN -- There is, so abort the script RAISERROR (N'Unable to import job ''Parameter Control'' since there is already a multi-server job with this name.' , 16, 1) GOTO QuitWithRollback END ELSE -- Delete the [local] job EXECUTE msdb.dbo.sp_delete_job @job_name = N'Parameter Control' SELECT @JobID = NULL END BEGIN -- Add the job EXECUTE @ReturnCode = msdb.dbo.sp_add_job @job_id = @JobID OUTPUT , @job_name = N'Parameter Control', @owner_login_name = N'sa' , @description = N'No description available.' , @category_name = N'SQL Server Monitoring', @enabled = 1, @notify_level_email = 0 , @notify_level_page = 0, @notify_level_netsend = 0, @notify_level_eventlog = 2, @delete_level= 0 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job steps EXECUTE @ReturnCode = msdb.dbo.sp_add_jobstep @job_id = @JobID, @step_id = 1 , @step_name = N'Step 1', @command = N'EXEC master.dbo.sp__OptionMonitor', @database_name = N'master' , @server = N'', @database_user_name = N'', @subsystem = N'TSQL', @cmdexec_success_code = 0 , @flags = 0, @retry_attempts = 0, @retry_interval = 1, @output_file_name = N'' , @on_success_step_id = 0 , @on_success_action = 1, @on_fail_step_id = 0 , @on_fail_action = 2 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback EXECUTE @ReturnCode = msdb.dbo.sp_update_job @job_id = @JobID, @start_step_id = 1 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the job schedules EXECUTE @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @JobID, @name = N'Schedule 1' , @enabled = 1, @freq_type = 4, @active_start_date = 20021018, @active_start_time = 60000 , @freq_interval = 1, @freq_subday_type = 1, @freq_subday_interval = 0, @freq_relative_interval = 0 , @freq_recurrence_factor = 0, @active_end_date = 99991231, @active_end_time = 235959 IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback -- Add the Target Servers EXECUTE @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @JobID, @server_name = N'(local)' IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback END COMMIT TRANSACTION GOTO EndSave QuitWithRollback: IF (@@TRANCOUNT > 0) ROLLBACK TRANSACTION EndSave: --******************************************************************************************************* --************************** Start right away this JOB ************************************* --******************************************************************************************************* EXEC msdb.dbo.sp_start_job @job_id = @JobID --******************************************************************************************************** --******************************** END ******************************** --******************************************************************************************************** GO EXEC master.dbo.sp_procoption @ProcName = 'sp__ParamControl', @OptionName = 'startup', @OptionValue = 'TRUE' GO EXEC master.dbo.sp__ParamControl GO
Total article views: 11248 | Views in the last 30 days: 3
 
Related Articles
FORUM

master.dbo.xp_smtp_sendmail ( sql 2000)

master.dbo.xp_smtp_sendmail ( sql 2000)

BLOG

WHERE to begin

Many people, once they start getting comfortable writing SQL, begin asking the same questions. One s...

ARTICLE

Beginning SQL Server 2000 Programming

A review of Beginning SQL Server 2000 Programming

FORUM

"Select Where In" using a parameter?

select intCol from Tablename where intCol in (@intList)

ARTICLE

Beginning SQL Server - Logins and Users

Part 3 of Steve Jones' series on beginning SQL Server from the perspective of a system administrator...

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones