USE master
GO
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_setautostartsql'), 'IsProcedure') = 1
BEGIN
    DROP PROCEDURE dbo.sp_setautostartsql
    IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_setautostartsql'), 'IsProcedure') = 1
        PRINT '<<< FAILED DROPPING PROCEDURE dbo.sp_setautostartsql >>>'
    ELSE
        PRINT '<<< DROPPED PROCEDURE dbo.sp_setautostartsql >>>'
END
go
CREATE PROCEDURE sp_setautostartsql (
        @setting varchar(10) = 'True'
)
WITH ENCRYPTION
AS
/********************************************************************************/
/*      Created By :    Umachandar Jayachandran (UC)                            */
/*      Created On :    09 February 1999                                        */
/*      Description:    This SP demonstrates how to use SQLDMO to set SQL Server*/
/*                      registry settings. This one sets the AUTOSTART property */
/*                      for the SQL Server service.                             */
/********************************************************************************/
/*      Resources  :    https://umachandar.com/resources.htm                 */
/********************************************************************************/
DECLARE @local int, @retcode int, @method varchar(255), @registry int
IF LOWER( @setting ) NOT IN ('yes', 'no', 'y', 'n', '1', '0', 'true', 'false')
BEGIN
        RAISERROR('Invalid Setting!
Valid values are "Yes", "No", "Y", "N", "1", "0", "True", "False".', 1, 2) WITH SETERROR
END
SELECT @setting = 'False' WHERE lower(@setting) IN ('no', 'n', '0', 'false')
SELECT @setting = 'True' WHERE lower(@setting) IN ('yes', 'y', '1', 'true')

EXEC @retcode = sp_OACreate 'SQLDMO.SQLServer', @local OUT
IF @retcode <> 0 GOTO ErrorHandler

EXEC @retcode = sp_OAMethod @local, 'Connect', NULL, '(local)'
IF @retcode <> 0 GOTO ErrorHandler

EXEC @retcode = sp_OAMethod @local, 'Registry', @registry OUT
IF @retcode <> 0 GOTO ErrorHandler

EXEC @retcode = sp_OASetProperty @registry, 'AutoStartServer', @setting
IF @retcode <> 0 GOTO ErrorHandler
GOTO Cleanup

ErrorHandler:
    EXEC sp_displayoaerrorinfo @local, @retcode

Cleanup:
EXEC sp_OADestroy @local
go
IF OBJECTPROPERTY(OBJECT_ID('dbo.sp_setautostartsql'), 'IsProcedure') = 1
BEGIN
    GRANT EXECUTE ON dbo.sp_setautostartsql To Public
    PRINT '<<< CREATED PROCEDURE dbo.sp_setautostartsql >>>'
END
ELSE
    PRINT '<<< FAILED CREATING PROCEDURE dbo.sp_setautostartsql >>>'
go

This page was last updated on May 01, 2006 04:28 PM.