Technical Article

Synchronize a stored procedure between two servers

,

Sometimes I need to synchonize source codes between development and production server. So, I created this stored procedure to help me do that instead of generating scripts from one server and execute it in another server.

To run this stored procedure, you need to create two linked servers for your source server and your target server. You can create them in local SQL Server and then run this to synchronize.

For example, your development server is called DEV and your production server is called PROD, and you create two linked servers in local SQL Server with the same name. Assumed that your database between DEV and PROD have the same name: TestDB.

If you want to synchronize TestProc stored procedure between TestDB of DEV and PROD, you can excute like that:

EXEC @ProcedureName = 'TestProc', @SourceServer = 'DEV', @SourceDatabase = 'TestDB', @TargetServer = 'PROD', @TargetDatabase = 'TestDB'

CREATE PROCEDURE [dbo].[spSynchStoredProcedure]
@ProcedureName VARCHAR(50) = NULL,
@SourceServer VARCHAR(50) = NULL,
@SourceDatabase VARCHAR(50) = NULL,
@TargetServer VARCHAR(50) = NULL,
@TargetDatabase VARCHAR(50) = NULL
AS
BEGIN
SET NOCOUNT ON;
DECLARE @STRSQL NVARCHAR(MAX);
DECLARE @Params NVARCHAR(MAX);
DECLARE @IsExisted BIT;
DECLARE @ProcedureSource NVARCHAR(MAX);
DECLARE @Tmp NVARCHAR(MAX);
SET @IsExisted = 0;
SET @STRSQL = N' SELECT @IsExisted = 1
FROM ' + @TargetServer + '.' + @TargetDatabase + '.sys.objects
WHERE name = ''' + @ProcedureName + ''' AND Type = ''P''';
SET @Params = N'@IsExisted BIT OUTPUT';
EXEC sp_executesql @STRSQL, @Params, @IsExisted = @IsExisted OUTPUT
IF (@IsExisted = 1)
BEGIN
SET @STRSQL = N'EXEC ' + @TargetServer + '.' + @TargetDatabase + '.dbo.sp_executesql N''DROP PROCEDURE ' + @ProcedureName + '''';
--PRINT @STRSQL;
EXECUTE(@STRSQL);
END;

--Insert to temp table since some stored procedures contains over 4000 characters
CREATE TABLE #tblTmp(item NVARCHAR(MAX));
SET @STRSQL = N'INSERT INTO #tblTmp
SELECT t1.text 
FROM ' + @SourceServer + '.' + @SourceDatabase + '.dbo.syscomments t1
INNER JOIN ' + @SourceServer + '.' + @SourceDatabase + '.dbo.sysobjects t2 on t1.id = t2.id
WHERE t2.xtype = ''P''
AND t2.name = ''' + @ProcedureName + '''';
EXECUTE(@STRSQL);

SET @ProcedureSource = '';
DECLARE MyCursor CURSOR FOR
SELECT item FROM #tblTmp
OPEN MyCursor
FETCH FROM MyCursor
INTO @Tmp
WHILE @@FETCH_STATUS = 0
BEGIN
SET @ProcedureSource = @ProcedureSource + @Tmp;
FETCH NEXT FROM MyCursor
INTO @Tmp
END
CLOSE MyCursor
DEALLOCATE MyCursor

DROP TABLE #tblTmp;
--PRINT @ProcedureSource;

SET @ProcedureSource = REPLACE(@ProcedureSource, '''', '''''');
SET @STRSQL = N'EXEC ' + @TargetServer + '.' + @TargetDatabase + '.dbo.sp_executesql N''' + @ProcedureSource + '''';
--PRINT @ProcedureSource;
--PRINT @STRSQL;
EXECUTE(@STRSQL);
END

Rate

3 (2)

You rated this post out of 5. Change rating

Share

Share

Rate

3 (2)

You rated this post out of 5. Change rating