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
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
No comments:
Post a Comment