How to Fix SQL Server Configuration Manager with T-SQL Script

If your SQL Server Configuration Manager is showing an error on screen instead of SQL Server details, then it is time to fix it. Use this script and fix the problem.

SQL Server configuration Manager:

SQL Server Configuration ManagerSQL Server Configuration manager is the GUI through which you can control SQL Server services and network protocols. Though you can start or stop SQL Server services using Microsoft Windows Services console, to control network protocols of your SQL instance you certainly need SLQ Server Configuration Manager. This is also the common place that most DBAs reach to refer start up parameters of a SQL Server instance.

Script to fix SQL Server configuration Manager if it gets corrupted:

SQL Server Configuration Manager CorruptedSQL Server Configuration manager might get corrupted and you will not be able to open it.  If that happens, execute this script to fix SQL SERVER configuration manager.

Before executing the script just make sure that ole automation and cmdshell are enabled in your sql server instance.

CREATE FUNCTION fn_scm (@WCD VARCHAR(1000))
RETURNS @result TABLE (
    c_nm VARCHAR(2000)
    ,c_ph VARCHAR(2000)
    ,c_md DATETIME
    ,c_ifs INT
    ,c_isf INT
    ,c_er VARCHAR(2000)
    )
AS
BEGIN
    DECLARE @OSA INT
        ,@OF INT
        ,@OI INT
        ,@OEO INT
        ,@OFI INT
        ,@SEM VARCHAR(1000)
        ,@CMD VARCHAR(1000)
        ,@cfs INT
        ,@ticker INT
        ,@ii INT
        ,@tag VARCHAR(2000)
        ,@lvl VARCHAR(2000)
        ,@MD DATETIME
        ,@IFS INT
        ,@IF INT

    IF LEN(COALESCE(@WCD, '')) < 2
        RETURN

    SELECT @SEM = 'opening the Shell Application Object'

    EXECUTE @cfs = sp_OACreate 'Shell.Application'
        ,@OSA OUT

    IF @cfs = 0
        SELECT @OEO = @OSA
            ,@SEM = 'Getting Folder"' + @WCD + '"'
            ,@CMD = 'NameSpace("' + @WCD + '")'

    IF @cfs = 0
        EXECUTE @cfs = sp_OAMethod @OSA
            ,@CMD
            ,@OF OUT

    IF @OF IS NULL
        RETURN

    SELECT @OEO = @OF
        ,@SEM = 'Getting count of Folder items in "' + @WCD + '"'
        ,@CMD = 'Items.Count'

    IF @cfs = 0
        EXECUTE @cfs = sp_OAMethod @OF
            ,@CMD
            ,@ticker OUT

    IF @cfs = 0
        SELECT @OEO = @OF
            ,@SEM = ' getting folderitems'
            ,@CMD = 'items()'

    IF @cfs = 0
        EXECUTE @cfs = sp_OAMethod @OF
            ,@CMD
            ,@OFI OUTPUT

    SELECT @ii = 0

    WHILE @cfs = 0
        AND @ii < @ticker
    BEGIN
        IF @cfs = 0
            SELECT @OEO = @OFI
                ,@SEM = ' getting folder item ' + CAST(@ii AS VARCHAR(5))
                ,@CMD = 'item(' + CAST(@ii AS VARCHAR(5)) + ')'

        IF @cfs = 0
            EXECUTE @cfs = sp_OAMethod @OFI
                ,@CMD
                ,@OI OUTPUT

        IF @cfs = 0
            SELECT @OEO = @OI
                ,@SEM = ' getting folder item properties' + CAST(@ii AS VARCHAR(5))

        IF @cfs = 0
            EXECUTE @cfs = sp_OAMethod @OI
                ,'path'
                ,@lvl OUTPUT

        IF @cfs = 0
            EXECUTE @cfs = sp_OAMethod @OI
                ,'name'
                ,@tag OUTPUT

        IF @cfs = 0
            EXECUTE @cfs = sp_OAMethod @OI
                ,'ModifyDate'
                ,@MD OUTPUT

        IF @cfs = 0
            EXECUTE @cfs = sp_OAMethod @OI
                ,'IsFileSystem'
                ,@IFS OUTPUT

        IF @cfs = 0
            EXECUTE @cfs = sp_OAMethod @OI
                ,'IsFolder'
                ,@IF OUTPUT

        INSERT INTO @result (
            c_nm
            ,c_ph
            ,c_md
            ,c_ifs
            ,c_isf
            )
        SELECT @tag
            ,@lvl
            ,@MD
            ,@IFS
            ,@IF

        IF @cfs = 0
            EXECUTE sp_OADestroy @OI

        SELECT @ii = @ii + 1
    END

    IF @cfs = 0
    BEGIN
        DECLARE @Source VARCHAR(255)
            ,@Description VARCHAR(255)
            ,@Helpfile VARCHAR(255)
            ,@HelpID INT

        EXECUTE sp_OAGetErrorInfo @OEO
            ,@source OUTPUT
            ,@Description OUTPUT
            ,@Helpfile OUTPUT
            ,@HelpID OUTPUT

        SELECT @SEM = 'Error whilst ' + COALESCE(@SEM, 'doing something') + ', ' + COALESCE(@Description, '')

        INSERT INTO @result (c_er)
        SELECT LEFT(@SEM, 2000)
    END

    EXECUTE sp_OADestroy @OF

    EXECUTE sp_OADestroy @OSA

    RETURN
END
GO

DECLARE @ph2 VARCHAR(1000)
DECLARE @ph3 VARCHAR(1000)

SET @ph3 = 'net stop winmgmt /y'
SET @ph2 = 'mofcomp "' + (
        SELECT c_ph
        FROM fn_scm('C:\Program Files\Microsoft SQL Server\100\Shared\')
        WHERE c_ph LIKE '%.%of'
        ) + '"'

EXEC xp_cmdshell @ph3
    ,no_output

DECLARE @ph4 VARCHAR(1000)

SET @ph4 = 'net start winmgmt'

EXEC xp_cmdshell @ph4
    ,no_output

Author Introduction:

Neil Varley is a data recovery expert in DataNumen, Inc., which is the world leader in data recovery technologies, including repair Outlook email damage and excel recovery software products. For more information visit www.datanumen.com

Leave a Reply

Your email address will not be published. Required fields are marked *