{"id":1902,"date":"2013-10-07T21:49:28","date_gmt":"2013-10-07T21:49:28","guid":{"rendered":"http:\/\/bicortex.com\/?p=1902"},"modified":"2013-10-29T05:55:52","modified_gmt":"2013-10-29T05:55:52","slug":"how-to-synchronise-multiple-database-objects-across-two-sql-server-databases-or-instances","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/how-to-synchronise-multiple-database-objects-across-two-sql-server-databases-or-instances\/","title":{"rendered":"How To Synchronise Multiple Database Objects Across Two SQL Server Databases or Instances"},"content":{"rendered":"<p style=\"text-align: justify;\">In two of my previous posts (<a href=\"http:\/\/bicortex.com\/how-to-synchronise-data-across-two-sql-server-databases-part-1-sql-code-and-application-for-individual-objects-processing\/\" target=\"_blank\"><b>HERE<\/b><\/a> and <a href=\"http:\/\/bicortex.com\/how-to-synchronise-data-across-two-sql-server-databases-part-2-sql-code-ssis-package-and-application-for-multiple-objects-processing\/\" target=\"_blank\"><b>HERE<\/b><\/a>) I explored the concept of dynamic data synchronisation between tables in two different databases. Lately, I have been working with a client who required not only data to be replicated between individual tables but also the objects themselves. Data synchronisation can be easily achieved through embedded SSIS functionality or if dynamic configuration is required through <a href=\"http:\/\/bicortex.com\/how-to-synchronise-data-across-two-sql-server-databases-part-1-sql-code-and-application-for-individual-objects-processing\/\" target=\"_blank\"><b>THIS<\/b><\/a> solution implementation, however, in this particular instance, the caveat laid with not just data but also with schema acquisition dynamisms \u2013 the client had no way of knowing what objects (names, types, definitions, data etc.) were added to the source database that required to be recreated on the target. Simply put it \u2013 all tables and their data as well as stored procedures, functions and views had to be recreated on a daily\/nightly basis in the destination database without going through the exercise of selecting or nominating them individually, which SSIS is more than capable of performing through Transfer SQL Server Objects Task.<\/p>\n<p style=\"text-align: justify;\">In order to demonstrate how this functionality is resolved, let\u2019s first create a sandbox environment with two databases and a bunch of dummy objects. Source_DB is the database where stored procedure, function, view and a few tables will be placed on, whereas Target_DB, as the name implies, will become our destination database, not containing any user defined objects just yet.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;master]\r\nGO\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Source_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE &#x5B;Source_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE &#x5B;Source_DB]\r\nEND\r\nGO\r\nCREATE DATABASE &#x5B;Source_DB]\r\n\r\nIF EXISTS (SELECT name FROM sys.databases WHERE name = N'Target_DB')\r\nBEGIN\r\n-- Close connections to the DW_Sample database\r\nALTER DATABASE &#x5B;Target_DB] SET SINGLE_USER WITH ROLLBACK IMMEDIATE\r\nDROP DATABASE &#x5B;Target_DB]\r\nEND\r\nGO\r\nCREATE DATABASE &#x5B;Target_DB]\r\n\r\nUSE Source_DB\r\nCREATE TABLE Tbl1 (\r\nID int NOT NULL,\r\nSample_Data_Col1 varchar (50) NOT NULL,\r\nSample_Data_Col2 varchar (50) NOT NULL,\r\nSample_Data_Col3 varchar (50) NOT NULL)\r\nGO\r\n\r\nUSE Source_DB\r\nDECLARE @rowcount int = 0\r\nWHILE @rowcount &lt; 10000\r\n\tBEGIN\r\n\t\tSET NOCOUNT ON\r\n\t\tINSERT INTO Tbl1\r\n\t\t(ID, Sample_Data_Col1, Sample_Data_Col2, Sample_Data_Col3)\r\n\t\tSELECT\r\n\t\t@rowcount,\r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)),\r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10)),\r\n\t\t'Sample_Data' + CAST(@rowcount as varchar(10))\r\n\t\tSET @rowcount = @rowcount + 1\r\n\tEND\r\nGO\r\n\r\nSELECT * INTO Tbl2 FROM Tbl1\r\nSELECT * INTO Tbl3 FROM Tbl1\r\nSELECT * INTO Tbl4 FROM Tbl1\r\nSELECT * INTO Tbl5 FROM Tbl1\r\nGO\r\n\r\nCREATE PROCEDURE sp_SampleProcedure1\r\nAS\r\nBEGIN\r\n\tSET NOCOUNT ON;\r\n\tSELECT GetDate()\r\nEND\r\nGO\r\n\r\nCREATE FUNCTION dbo.ufn_SampleFunction1 (@ID int)\r\nRETURNS TABLE\r\nAS\r\nRETURN\r\n(SELECT * FROM Tbl1 WHERE ID = @ID)\r\nGO\r\n\r\nCREATE VIEW vw_SampleTop10\r\nAS\r\nSELECT TOP (10) * FROM dbo.Tbl1\r\n<\/pre>\n<p style=\"text-align: justify;\">Once the code executed, we are ready to create our objects synchronisation stored procedure which should account for all user defined objects and recreate them on the destination database \u2013 these will include tables with their corresponding data, functions, views and additional stored procedures and can be modified to encompass other types as per additional requirements. The code simply queries a collection of system objects to fetch their names and definitions, builds a temporary table to store this metadata and finally using cursors replicates those on a target database.<\/p>\n<pre class=\"brush: sql; title: ; notranslate\" title=\"\">\r\nUSE &#x5B;target_DB]\r\nIF EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'&#x5B;dbo].&#x5B;usp_SyncDBObjects]')\r\nAND type IN (N'P',N'PC'))\r\nDROP PROCEDURE &#x5B;dbo].&#x5B;usp_SyncDBObjects]\r\nGO\r\n \r\nCREATE PROCEDURE &#x5B;usp_SyncDBObjects]\r\n@SourceDBName       varchar (256),\r\n@SourceSchemaName   varchar (50),\r\n@TargetDBName       varchar (256),\r\n@TargetSchemaName   varchar (50)\r\nAS\r\nBEGIN\r\n \r\n    SET NOCOUNT ON\r\n    DECLARE @Err_Msg varchar (max)\r\n    DECLARE @IsDebugMode bit = 0\r\n    DECLARE @SQLSource nvarchar (max) =\r\n    'INSERT INTO #TempTbl\r\n    (ObjectID, ObjectName, SchemaName, DBName, ObjectType, ObjectDefinition)\r\n    SELECT DISTINCT\r\n    o.object_id,o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''Table'',''N\/A''\r\n    FROM   '+@SourceDBName+'.sys.tables t\r\n    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n    WHERE S.name = '''+@SourceSchemaName+''' and o.type = ''U''\r\n    UNION ALL\r\n    SELECT DISTINCT\r\n    o.object_id, o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''View'', m.definition\r\n    FROM   '+@SourceDBName+'.sys.tables t\r\n    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n    LEFT JOIN '+@SourceDBName+'.sys.sql_modules m ON m.object_id = o.object_id\r\n    WHERE S.name = '''+@SourceSchemaName+''' and o.type = ''V''\r\n    UNION ALL\r\n    SELECT DISTINCT\r\n    o.object_id, o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''Stored Procedure'', m.definition\r\n    FROM   '+@SourceDBName+'.sys.tables t\r\n    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n    LEFT JOIN '+@SourceDBName+'.sys.sql_modules m ON m.object_id = o.object_id\r\n    WHERE S.name = '''+@SourceSchemaName+''' and o.type = ''P''\r\n    UNION ALL\r\n    SELECT DISTINCT\r\n    o.object_id, o.name, '''+@SourceSchemaName+''', '''+@SourceDBName+''',''Function'', m.definition\r\n    FROM   '+@SourceDBName+'.sys.tables t\r\n    JOIN '+@SourceDBName+'.sys.schemas s ON t.schema_id = s.schema_id\r\n    JOIN '+@SourceDBName+'.sys.objects o ON t.schema_id = o.schema_id\r\n    LEFT JOIN '+@SourceDBName+'.sys.sql_modules m ON m.object_id = o.object_id\r\n    WHERE S.name = '''+@SourceSchemaName+''' and o.type IN (''TF'',''IF'', ''FN'', ''FS'')'\r\n \r\n    IF OBJECT_ID('tempdb..#TempTbl') IS NOT NULL\r\n    DROP TABLE #TempTbl\r\n \r\n    CREATE TABLE #TempTbl\r\n    (ID int IDENTITY (1,1),\r\n    ObjectID            int,\r\n    ObjectName          varchar (256),\r\n    SchemaName          varchar (50),\r\n    DBName              varchar (50),\r\n    ObjectType          varchar (20),\r\n    ObjectDefinition    varchar (max))\r\n \r\n    EXEC sp_executesql @SQLSource\r\n \r\n    IF NOT EXISTS (SELECT 1 FROM #TempTbl a WHERE a.dbname = @SourceDBName and a.SchemaName = @SourceSchemaName)\r\n    BEGIN\r\n        SET\r\n        @Err_Msg = 'Source database objects cannot be fetched. You nominated ''&#x5B;'+@SourceDBName+']'' database on a ''&#x5B;'+@SourceSchemaName+']'' schema. '\r\n        RAISERROR (\r\n        @Err_Msg  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n                    )\r\n        RETURN\r\n    END\r\n \r\n    IF @IsDebugMode = 1\r\n    SELECT * FROM #TempTbl\r\n    --IF @IsDebugMode = 1\r\n        PRINT ''\r\n        PRINT 'The following objects based on Source vs Target comparison will be synchronised between ''&#x5B;'+@TargetDBName+']'' and ''&#x5B;'+@SourceDBName+']''...'\r\n        DECLARE @ID int\r\n        DECLARE @TblName varchar (256)\r\n        DECLARE cur CURSOR FOR\r\n            SELECT ID, ObjectName FROM #TempTbl\r\n            OPEN cur\r\n            FETCH NEXT FROM cur INTO @ID, @TblName\r\n            WHILE @@FETCH_STATUS = 0\r\n            BEGIN\r\n                PRINT '' + CAST(@ID as varchar (20))+'. '+ @TblName +''\r\n                FETCH NEXT FROM cur INTO @ID, @TblName\r\n            END\r\n        CLOSE cur\r\n        DEALLOCATE cur\r\n    DECLARE @ObjectName varchar (256)\r\n    DECLARE @sql_select varchar (max)\r\n    DECLARE @sql_drop varchar (max)\r\n    DECLARE @sql_definition varchar (max)\r\n\tDECLARE @Err int\r\n    PRINT ''\r\n    PRINT 'Starting objects synchronisation process...'\r\n    PRINT ''\r\n    PRINT 'Recreating tables and data...'\r\n\r\n    DECLARE db_cursor CURSOR FORWARD_ONLY\r\n        FOR\r\n            SELECT ObjectName\r\n            FROM #TempTbl t\r\n            WHERE t.ObjectType = 'table'\r\n            OPEN db_cursor\r\n\t\t\tSELECT @Err = @@Error IF @Err &lt;&gt; 0 BEGIN DEALLOCATE db_cursor RETURN @Err END\r\n            FETCH NEXT\r\n            FROM db_cursor INTO @ObjectName\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n                            PRINT 'DROPPING TABLE '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''\r\n                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME\r\n                                                = '''+@ObjectName+''') DROP TABLE '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''\r\n                            EXEC sp_sqlexec @SQL_drop\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH\r\n\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n\t\t\t\t\t\t\tPRINT 'SELECTING INTO ' + @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' FROM '+ @TargetDBName + '.' + @TargetSchemaName + '.' + @ObjectName + ''\r\n                            SET @SQL_select     =   'SELECT * INTO '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+'\r\n                                                FROM '+@SourceDBName+'.'+@SourceSchemaName+'.'+@ObjectName+''\r\n                            EXEC sp_sqlexec @SQL_select\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH\r\n                    FETCH NEXT FROM db_cursor INTO @ObjectName\r\n                END\r\n            CLOSE db_cursor\r\n            DEALLOCATE db_cursor\r\n    PRINT ''\r\n    PRINT 'Recreating views...'\r\n    DECLARE db_cursor CURSOR FORWARD_ONLY\r\n        FOR\r\n            SELECT ObjectName\r\n            FROM #TempTbl t\r\n            WHERE t.ObjectType = 'view'\r\n            OPEN db_cursor\r\n\t\t\tSELECT @Err = @@Error IF @Err &lt;&gt; 0 BEGIN DEALLOCATE db_cursor RETURN @Err END\r\n            FETCH NEXT\r\n            FROM db_cursor INTO @ObjectName\r\n                WHILE @@FETCH_STATUS = 0\r\n\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n                            PRINT 'DROPPING VIEW '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''\r\n                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME\r\n                                                = '''+@ObjectName+''') DROP VIEW '+@TargetSchemaName+'.'+@ObjectName+'' \r\n                            EXEC sp_sqlexec @SQL_drop\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH\r\n\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n\t\t\t\t\t\t\tPRINT 'CREATING VIEW ' + @ObjectName + ' on '+@TargetDBName+' from '+ @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' view'\r\n\t\t\t\t\t\t\tSET @SQL_select     =   (SELECT ObjectDefinition FROM #TempTbl WHERE ObjectName = @ObjectName)\r\n                            EXEC sp_sqlexec @SQL_select\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH\r\n                    FETCH NEXT FROM db_cursor INTO @ObjectName\r\n                END\r\n            CLOSE db_cursor\r\n            DEALLOCATE db_cursor\r\n    PRINT ''\r\n    PRINT 'Recreating functions...'\r\n    DECLARE db_cursor CURSOR FORWARD_ONLY\r\n        FOR\r\n            SELECT ObjectName\r\n            FROM #TempTbl t\r\n            WHERE t.ObjectType = 'Function'\r\n            OPEN db_cursor\r\n\t\t\tSELECT @Err = @@Error IF @Err &lt;&gt; 0 BEGIN DEALLOCATE db_cursor RETURN @Err END\r\n            FETCH NEXT\r\n            FROM db_cursor INTO @ObjectName\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n                            PRINT 'DROPPING FUNCTION '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''\r\n                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.'+@TargetSchemaName+'.sysobjects WHERE id = object_id('''+@ObjectName+''')\r\n                                                    AND xtype IN (''TF'',''IF'', ''FN'', ''FS''))  DROP FUNCTION '+@TargetSchemaName+'.'+@ObjectName+''\r\n                            EXEC sp_sqlexec @SQL_drop\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH \r\n\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n\t\t\t\t\t\t\tPRINT 'CREATING FUNCTION  ' + @ObjectName + ' on '+@TargetDBName+' from '+ @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' function'\r\n\t\t\t\t\t\t\tSET @SQL_select     =   (SELECT ObjectDefinition FROM #TempTbl WHERE ObjectName = @ObjectName)\r\n                            EXEC sp_sqlexec @SQL_select\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH\r\n                    FETCH NEXT FROM db_cursor INTO @ObjectName\r\n                END\r\n            CLOSE db_cursor\r\n            DEALLOCATE db_cursor\r\n    PRINT ''\r\n    PRINT 'Recreating stored procedures...'\r\n        DECLARE db_cursor CURSOR FORWARD_ONLY\r\n        FOR\r\n            SELECT ObjectName\r\n            FROM #TempTbl t\r\n            WHERE t.ObjectType = 'Stored Procedure'\r\n            OPEN db_cursor\r\n\t\t\tSELECT @Err = @@Error IF @Err &lt;&gt; 0 BEGIN DEALLOCATE db_cursor RETURN @Err END\r\n            FETCH NEXT\r\n            FROM db_cursor INTO @ObjectName\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n                            PRINT 'DROPPING STORED PROCEDURE '+@TargetDBName+'.'+@TargetSchemaName+'.'+@ObjectName+''\r\n                            SET @SQL_drop       =   'IF EXISTS (SELECT * FROM '+@TargetDBName+'.'+@TargetSchemaName+'.sysobjects WHERE id = object_id('''+@ObjectName+'''))\r\n                                                     DROP PROCEDURE '+@TargetSchemaName+'.'+@ObjectName+''\r\n                            EXEC sp_sqlexec @SQL_drop\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH \r\n\r\n\t\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\t\t\tBEGIN TRANSACTION\r\n\t\t\t\t\t\t\tPRINT 'CREATING STORED PROCEDURE ' + @ObjectName + ' on '+@TargetDBName+' from '+ @SourceDBName + '.' + @SourceSchemaName + '.' + @ObjectName + ' procedure'\r\n\t\t\t\t\t\t\tSET @SQL_select     =   (SELECT ObjectDefinition FROM #TempTbl WHERE ObjectName = @ObjectName)\r\n                            EXEC sp_sqlexec @SQL_select\r\n\t\t\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\t\t\tEND TRY\r\n\t\t\t\t\t\tBEGIN CATCH\r\n\t\t\t\t\t\t\tROLLBACK TRANSACTION\r\n\t\t\t\t\t\t\tSELECT\r\n\t\t\t\t\t\t\t ERROR_NUMBER       () AS ErrorNumber\r\n\t\t\t\t\t\t\t,ERROR_SEVERITY     () AS ErrorSeverity\r\n\t\t\t\t\t\t\t,ERROR_STATE        () AS ErrorState\r\n\t\t\t\t\t\t\t,ERROR_PROCEDURE    () AS ErrorProcedure\r\n\t\t\t\t\t\t\t,ERROR_LINE         () AS ErrorLine\r\n\t\t\t\t\t\t\t,ERROR_MESSAGE      () AS ErrorMessage;\r\n\t\t\t\t\t\tEND CATCH \r\n                    FETCH NEXT FROM db_cursor INTO @ObjectName\r\n                END\r\n       CLOSE db_cursor\r\n       DEALLOCATE db_cursor\r\nEND\r\n<\/pre>\n<p style=\"text-align: justify;\">Naturally, this SQL code can be modified\/extended to work across two separate instances e.g. via a linked server connection, replicate other types of objects e.g. triggers, constraint, synonyms etc. and integrate with a separate SSIS routine\/framework. When cross-instance replication is involved, the only change required to the above code is setting up linked servers connection and using four part, fully qualified identifiers i.e. server.database.schema.object.<\/p>\n<p style=\"text-align: justify;\">When the procedure has finished executing, both Source_DB and Target_DB should contain identical data and objects schema for tables, functions, stored procedures and tables as per execution log and each database sys.objects query image below.<\/p>\n<p style=\"text-align: center;\"><a href=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2013\/10\/Objects_Syncing_Exec_Log_HTSDO.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter  wp-image-1904\" alt=\"Objects_Syncing_Exec_Log_HTSDO\" src=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2013\/10\/Objects_Syncing_Exec_Log_HTSDO.png\" width=\"580\" height=\"400\" \/><\/a><\/p>\n<p style=\"text-align: justify;\"><a href=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2013\/10\/Objects_Syncing_AferExec_Comparison_HTSDO.png\"><img loading=\"lazy\" decoding=\"async\" class=\"aligncenter  wp-image-1905\" alt=\"Objects_Syncing_AferExec_Comparison_HTSDO\" src=\"http:\/\/bicortex.com\/bicortex\/wp-content\/post_content\/\/2013\/10\/Objects_Syncing_AferExec_Comparison_HTSDO.png\" width=\"580\" height=\"350\" \/><\/a>If the schema is to remain static and you only wish to synchronise data, please check out my other two posts (<a href=\"http:\/\/bicortex.com\/how-to-synchronise-data-across-two-sql-server-databases-part-1-sql-code-and-application-for-individual-objects-processing\/\" target=\"_blank\"><b>HERE<\/b><\/a> and <a href=\"http:\/\/bicortex.com\/how-to-synchronise-data-across-two-sql-server-databases-part-2-sql-code-ssis-package-and-application-for-multiple-objects-processing\/\" target=\"_blank\"><b>HERE<\/b><\/a>) where I explored the dynamic functionality of cross-table data replication through on-the-fly MERGE statement creation and UPSERT execution.<\/p>\n","protected":false},"excerpt":{"rendered":"<p>In two of my previous posts (HERE and HERE) I explored the concept of dynamic data synchronisation between tables in two different databases. Lately, I have been working with a client who required not only data to be replicated between individual tables but also the objects themselves. Data synchronisation can be easily achieved through embedded [&hellip;]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[32,5],"tags":[49,19],"class_list":["post-1902","post","type-post","status-publish","format-standard","hentry","category-how-tos","category-sql","tag-sql","tag-sql-server"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1902","targetHints":{"allow":["GET"]}}],"collection":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/comments?post=1902"}],"version-history":[{"count":8,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1902\/revisions"}],"predecessor-version":[{"id":1921,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/1902\/revisions\/1921"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=1902"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=1902"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=1902"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}