Database Maintenance on Remote servers (EXEC or OPENROWSET)

When I write database maintenance procedures I want to be able to execute them on any or all databases on any server so I use the EXEC to run local server commands and create a dynamic OpenRowSet SQL Statement to run remote server commands. I use the procedure below to implement this.


CREATE PROCEDURE [dbo].[usp_srvr_exec]
@cmd            varchar(MAX)
, @DisplayError        bit=0
, @ServerName        varchar(80)=NULL
, @Execute0Display1Both2    tinyint=0
, @UseOpenRowSet    bit=0
, @ReplaceTabsWithSpaces    bit=1
, @cmd_generated        varchar(8000)=NULL OUTPUT
, @DoubleSingleQuotes    bit = 1
, @UserAccount        varchar(50)= NULL
, @Password        varchar(50)= NULL
, @execute_command    bit=1
AS
BEGIN

DECLARE
@Retval        integer
, @cmd2        varchar(MAX)
, @uid        varchar(25)
, @pwd        varchar(50)
, @err        integer
, @pos        bigint

SET @Retval                = 0
SET @ReplaceTabsWithSpaces        = ISNULL(@ReplaceTabsWithSpaces    ,0)
SET @ServerName                = ISNULL(@ServerName, @@SERVERNAME)
SET @DoubleSingleQuotes            = ISNULL(@DoubleSingleQuotes,1)
SET @UID                = COALESCE( @UID, @UserAccount)
SET @PWD                = COALESCE(@PWD , @Password)

IF @ReplaceTabsWithSpaces = 1
    SET @cmd = REPLACE(@cmd, CHAR(9), CHAR(32))

IF @ServerName IS NOT NULL AND @ServerName <> @@SERVERNAME
BEGIN
    SET @ServerName = COALESCE(
(SELECT Server_Name
FROM msdb.dbo.sysmanagement_shared_registered_servers_internal
WHERE NAME = @ServerName)
,@ServerName)
end

IF (@ServerName <> @@SERVERNAME OR ISNULL(@UseOpenRowSet,0) = 1) --AND 1 = 2
BEGIN
---------------------------------------------------------------
-- Format the command to be called by the EXEC command to use
-- OPENROWSET so the command can be executed on a remote server.
---------------------------------------------------------------

---------------------------------------------------------------
-- The following section has been modified to account for
-- a less complicated version of this procedure.
-- The original procedure checked to see if CLR functionality was enabled.
-- If it was not then user ids and encrypted passwords necessary to access
-- remote servers were retrieved.
-- This procedure has been modified to assume CLR functionality is disabled.
-- It is also modified to only look for encrypted passwords if current database
-- is 'DBATools'. Otherwise server communications will only be possible if
-- the SQL service account is running under a domain account trusted on the
-- remote server being accessed.
---------------------------------------------------------------
    IF [dbo].[udf_config_use_trusted]() = 0
    AND (@UID IS NULL OR @PWD IS NULL)
    BEGIN
        SET @UID = [dbo].[udf_AppDefault]( 'SystemUID1','SQL_UID', @ServerName    , 'DBATools')
        SET @PWD = [dbo].[udf_AppDefault2]( 'SystemUID1','SQL_UID', @ServerName, 'DBATools')
        --PRINT @UID + ':' + @PWD
    END

---------------------------------------------------------------
--
---------------------------------------------------------------
    IF ISNULL(@uid,'') = '' OR ISNULL(@pwd,'') = ''
    BEGIN
        SET @cmd2 = CHAR(39) + 'Server=' + @ServerName + ';'
                 + 'Trusted_Connection=yes;'', ''set fmtonly off;'
    END
    ELSE
    BEGIN
        SET @cmd2 = CHAR(39) + @ServerName + CHAR(39) + ';'''
            + @uid + ''';''' + @pwd + ''', ''set fmtonly off; '
    END
---------------------------------------------------------------
-- First layer of double quotes.
-- This is necessary for any EXEC statements.
---------------------------------------------------------------
    IF @DoubleSingleQuotes    = 1
        SET @cmd = REPLACE(@cmd, CHAR(39),CHAR(39) + CHAR(39))

    IF CHARINDEX('USE ', @cmd) > 0
    BEGIN
        SET @cmd = 'EXEC(''' + @cmd + ''')'

---------------------------------------------------------------
-- Second layer of double quotes.
-- This is necessary for any EXEC statements executed on remote servers
-- using OPENROWSET.
---------------------------------------------------------------
        SET @cmd = REPLACE(@cmd, CHAR(39),CHAR(39) + CHAR(39))

    END
    ELSE
    BEGIN
        IF CHARINDEX('EXEC ', @cmd) = 0
        AND CHARINDEX('EXEC(', @cmd) = 0
        AND CHARINDEX('EXECUTE(', @cmd) = 0
        AND CHARINDEX('EXECUTE ', @cmd) = 0
        AND CHARINDEX('SELECT ', @cmd) = 0
        AND CHARINDEX('UPDATE ', @cmd) = 0
        AND CHARINDEX('DELETE ', @cmd) = 0
        AND CHARINDEX('TRUNCATE ', @cmd) = 0
        AND CHARINDEX('INSERT ', @cmd) = 0
        AND CHARINDEX('DBCC ', @cmd) = 0
        BEGIN
            SET @cmd2 = @cmd2 + ' EXEC '
        END
    END

    SET @cmd = 'SELECT * FROM OPENROWSET(''SQLNCLI'',' + @cmd2 + ' ' + @cmd + ''') '
END
SET @cmd_generated = @cmd

---------------------------------------------------------------
-- Execute the command
---------------------------------------------------------------
IF ISNULL(@Execute0Display1Both2,1) >= 1
    PRINT @cmd

IF ISNULL(@execute_command,0) = 1
AND (ISNULL(@Execute0Display1Both2,1) = 0 OR ISNULL(@Execute0Display1Both2,1) = 2)
BEGIN
    BEGIN TRY
        EXEC(@cmd)
        SET @retval    = @@ERROR
    END TRY
    BEGIN CATCH
        IF ISNULL(@DisplayError,1) = 1
            EXECUTE dbo.usp_srvr_error_print;
            --EXECUTE dbo.usp_srvr_error_add;
        SET @retval    = 1
    END CATCH
END

ProcExit:
    RETURN @Retval
ProcError:
    SET @retval = @err
    GOTO ProcExit

/*

DECLARE
@err int
, @cmd varchar(max)

SET @cmd = 'Use [AdventureWorks];SELECT * FROM dbo.sysfiles'

EXEC @err        = [dbo].[usp_srvr_exec]
@cmd            = @cmd
, @DisplayError        = 1
, @ServerName        = @@SERVERNAME
, @Execute0Display1Both2    = 2


*/

END
GO