{"id":2888,"date":"2016-05-25T06:33:30","date_gmt":"2016-05-25T06:33:30","guid":{"rendered":"http:\/\/bicortex.com\/?p=2888"},"modified":"2016-05-27T01:18:45","modified_gmt":"2016-05-27T01:18:45","slug":"designing-data-acquisition-framework-in-sql-server-and-ssis-how-to-source-and-integrate-external-data-for-a-decision-support-system-or-data-warehouse-part-2","status":"publish","type":"post","link":"http:\/\/bicortex.com\/bicortex\/designing-data-acquisition-framework-in-sql-server-and-ssis-how-to-source-and-integrate-external-data-for-a-decision-support-system-or-data-warehouse-part-2\/","title":{"rendered":"Designing data acquisition framework in SQL Server and SSIS \u2013 how to source and integrate external data for a decision support system or data warehouse (Part 2)"},"content":{"rendered":"

Note: Part 1 to this series can be found HERE<\/a>, Part 3 HERE<\/a>, Part 4 HERE <\/a>and all the code and additional files for this post can be downloaded from my OneDrive folder HERE<\/a>.<\/p>\n

Continuing on from part 1 to this series, having set up all the scaffolding and support databases\/objects, we are now ready to roll with the punches and start implementing the actual code responsible for some preliminary activities (before acquisition commences) as well as all heavy lifting when acquiring the source data. As per activities outlined in part 1, this post looks into pre-acquisition tasks (activities which facilitate subsequent data coping e.g. source server availability checking, schema modifications check, pre-load indexes management) and acquisition code for large tables, so jumping ahead a little bit into the actual package design (see SSIS package design details in part 4<\/a>), the area of focus is as per the image below.<\/p>\n

\"Data_Acquisition_Framework_Part2_SSIS_Package_Top_Level_Overview\"<\/a><\/p>\n

Pre-acquisition Tasks Overview and Code<\/h3>\n

To start with, we will set the initial values for the three variables used in the package using a combination of two transformations i.e. Script Task and Execute SQL Task. The Script Task transformation will run a snippet of C# code which sets the initial values of \u2018Is_All_OK\u2019 and \u2018Sync_Exec_StartTime\u2019 variables responsible for binary task execution status and package execution start time respectively. Since one of post-execution steps deals with checking the AdminDBA database for an errors encountered during package runtime, it is important to log package execution start date and time (used as log search query parameters when defining search time frame). The code run in this step (most of it just boilerplate, with the main sections highlighted) is as per below whereas all the package configuration details are described in part 4<\/a> to this series.<\/p>\n

\r\n#region Help:  Introduction to the script task\r\n\/* The Script Task allows you to perform virtually any operation that can be accomplished in\r\n * a .Net application within the context of an Integration Services control flow. \r\n * \r\n * Expand the other regions which have "Help" prefixes for examples of specific ways to use\r\n * Integration Services features within this script task. *\/\r\n#endregion\r\n\r\n\r\n#region Namespaces\r\nusing System;\r\nusing System.Data;\r\nusing Microsoft.SqlServer.Dts.Runtime;\r\nusing System.Windows.Forms;\r\n#endregion\r\n\r\nnamespace ST_44c20947eb6c4c5cb2f698bdd17b3534\r\n{\r\n    \/\/\/ <summary>\r\n    \/\/\/ ScriptMain is the entry point class of the script.  Do not change the name, attributes,\r\n    \/\/\/ or parent of this class.\r\n    \/\/\/ <\/summary>\r\n    [Microsoft.SqlServer.Dts.Tasks.ScriptTask.SSISScriptTaskEntryPointAttribute]\r\n    public partial class ScriptMain : Microsoft.SqlServer.Dts.Tasks.ScriptTask.VSTARTScriptObjectModelBase\r\n    {\r\n        #region Help:  Using Integration Services variables and parameters in a script\r\n        \/* To use a variable in this script, first ensure that the variable has been added to \r\n         * either the list contained in the ReadOnlyVariables property or the list contained in \r\n         * the ReadWriteVariables property of this script task, according to whether or not your\r\n         * code needs to write to the variable.  To add the variable, save this script, close this instance of\r\n         * Visual Studio, and update the ReadOnlyVariables and \r\n         * ReadWriteVariables properties in the Script Transformation Editor window.\r\n         * To use a parameter in this script, follow the same steps. Parameters are always read-only.\r\n         * \r\n         * Example of reading from a variable:\r\n         *  DateTime startTime = (DateTime) Dts.Variables["System::StartTime"].Value;\r\n         * \r\n         * Example of writing to a variable:\r\n         *  Dts.Variables["User::myStringVariable"].Value = "new value";\r\n         * \r\n         * Example of reading from a package parameter:\r\n         *  int batchId = (int) Dts.Variables["$Package::batchId"].Value;\r\n         *  \r\n         * Example of reading from a project parameter:\r\n         *  int batchId = (int) Dts.Variables["$Project::batchId"].Value;\r\n         * \r\n         * Example of reading from a sensitive project parameter:\r\n         *  int batchId = (int) Dts.Variables["$Project::batchId"].GetSensitiveValue();\r\n         * *\/\r\n\r\n        #endregion\r\n\r\n        #region Help:  Firing Integration Services events from a script\r\n        \/* This script task can fire events for logging purposes.\r\n         * \r\n         * Example of firing an error event:\r\n         *  Dts.Events.FireError(18, "Process Values", "Bad value", "", 0);\r\n         * \r\n         * Example of firing an information event:\r\n         *  Dts.Events.FireInformation(3, "Process Values", "Processing has started", "", 0, ref fireAgain)\r\n         * \r\n         * Example of firing a warning event:\r\n         *  Dts.Events.FireWarning(14, "Process Values", "No values received for input", "", 0);\r\n         * *\/\r\n        #endregion\r\n\r\n        #region Help:  Using Integration Services connection managers in a script\r\n        \/* Some types of connection managers can be used in this script task.  See the topic \r\n         * "Working with Connection Managers Programatically" for details.\r\n         * \r\n         * Example of using an ADO.Net connection manager:\r\n         *  object rawConnection = Dts.Connections["Sales DB"].AcquireConnection(Dts.Transaction);\r\n         *  SqlConnection myADONETConnection = (SqlConnection)rawConnection;\r\n         *  \/\/Use the connection in some code here, then release the connection\r\n         *  Dts.Connections["Sales DB"].ReleaseConnection(rawConnection);\r\n         *\r\n         * Example of using a File connection manager\r\n         *  object rawConnection = Dts.Connections["Prices.zip"].AcquireConnection(Dts.Transaction);\r\n         *  string filePath = (string)rawConnection;\r\n         *  \/\/Use the connection in some code here, then release the connection\r\n         *  Dts.Connections["Prices.zip"].ReleaseConnection(rawConnection);\r\n         * *\/\r\n        #endregion\r\n\r\n\r\n        \/\/\/ <summary>\r\n        \/\/\/ This method is called when this script task executes in the control flow.\r\n        \/\/\/ Before returning from this method, set the value of Dts.TaskResult to indicate success or failure.\r\n        \/\/\/ To open Help, press F1.\r\n        \/\/\/ <\/summary>\r\n        \/\/\/ \r\n        public void Main()\r\n        {\r\n            \/\/ TODO: Add your code here\r\n            DateTime saveNow = DateTime.Now;\r\n            Dts.Variables["Is_All_OK"].Value = 0;\r\n            Dts.Variables["Sync_Exec_StartTime"].Value = saveNow;\r\n            Dts.TaskResult = (int)ScriptResults.Success;\r\n        }\r\n\r\n        #region ScriptResults declaration\r\n        \/\/\/ <summary>\r\n        \/\/\/ This enum provides a convenient shorthand within the scope of this class for setting the\r\n        \/\/\/ result of the script.\r\n        \/\/\/ \r\n        \/\/\/ This code was generated automatically.\r\n        \/\/\/ <\/summary>\r\n        enum ScriptResults\r\n        {\r\n            Success = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Success,\r\n            Failure = Microsoft.SqlServer.Dts.Runtime.DTSExecResult.Failure\r\n        };\r\n        #endregion\r\n\r\n    }\r\n}\r\n<\/pre>\n
The second transformation executes user defined function created in part 1, acquiring e-mail addresses from the corresponding table and storing them in a DBMail_Receipients SSIS variable. These email addresses can be used for operator notifications handling part of the process, when an exception is raised at package runtime. I will dive into package structure in more details in the next post to this series so at this stage is only important to understand that these two steps run simultaneously and as a prelude to further tasks execution.<\/p>\n
Next up, we will proceed to update AdminDBA database based on the target server instance metadata. For this purpose, I have used the UPDATE stored procedure described in detail in two of my previous posts, mainly HERE<\/a> and HERE<\/a>. The code is quite complex and lengthy so for the sake of succinctness I will refrain from posting it here but you can read more about its functionality HERE<\/a> and grab a copy from my OneDrive folder HERE<\/a>. This stored procedure is run in order to update AdminDBA database with the latest instance metadata. As AdminDBA database stores a granular data of all databases, objects, schemas etc. for error log reference purposes, this data needs to be kept up-to-date to account for every change that has either added, deleted or updated the object and\/or its status. This code does exactly that \u2013 it ploughs through the SQL Server instance and packages metadata, making sure that all changes have been accounted for so that any error in the package execution can be logged and referenced across instance, database, schema and object level.<\/p>\n
Since one of the assumptions made in Part 1 was that in case any connectivity issues occur, the job will wait for a predefined period of time, a predefined number of times before reporting a failed connectivity status, the first thing to do is to ensure that at the initiation phase the linked server connection status is validated. For this purpose, we can use the SQL Server connection status system stored procedure along with a re-try routine delayed by the predefined amount of time as per the code below.<\/p>\n
\r\nUSE StagingDB;\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[usp_checkRemoteSvrConnectionStatus]\r\n    @Remote_Server_Name VARCHAR(256) ,\r\n    @Is_All_OK INT OUTPUT ,\r\n    @Error_Message VARCHAR(2000) OUTPUT ,\r\n    @Process_Name VARCHAR(250) OUTPUT\r\nAS\r\n    BEGIN\r\n        SET NOCOUNT ON;\r\n        SET @Process_Name = ( SELECT    OBJECT_NAME(objectid)\r\n                              FROM      sys.dm_exec_requests r\r\n                                        CROSS   APPLY sys.dm_exec_sql_text(r.sql_handle) a\r\n                              WHERE     session_id = @@spid\r\n                            );\r\n        DECLARE @errno INT;\r\n        DECLARE @errMsg VARCHAR(2048);\r\n        DECLARE @isDebugMode INT = 1;\r\n        DECLARE @Remote_Server_NameConv sysname;\r\n        DECLARE @ct INT = 4;\t\r\n        IF @isDebugMode = 1\r\n            BEGIN\r\n                PRINT 'Checking for Linked Server Connection...';\r\n            END;\r\n        Start:\r\n        BEGIN TRY\t\t\t\r\n            SELECT  @Remote_Server_NameConv = CONVERT(sysname, @Remote_Server_Name);\r\n            EXEC sys.sp_testlinkedserver @Remote_Server_NameConv;\r\n        END TRY\r\n        BEGIN CATCH\r\n            SET @errno = @@ERROR;\r\n            SET @errMsg = ERROR_MESSAGE();\r\n            SET @Is_All_OK = 0;\t\t\r\n        END CATCH;\t\r\n        IF @Is_All_OK = 0\r\n            AND @ct > 1\r\n            BEGIN \r\n                SET @ct = @ct - 1;\r\n                IF @isDebugMode = 1\r\n                    BEGIN\r\n                        PRINT 'Connection to Linked Server '\r\n                            + @Remote_Server_Name\r\n                            + ' cannot be established. Will attempt to connect again in 5 minutes. Number of re-tries left: '\r\n                            + CAST(@ct AS VARCHAR(10)) + '';\r\n                    END;\r\n                WAITFOR DELAY '00:05:00';\t\t\t\t\r\n                GOTO Start;\t\r\n            END;\r\n        IF @Is_All_OK = 1\r\n            BEGIN\r\n                GOTO Finish; \r\n            END;\r\n        Finish:\r\n        IF @errno <> 0\r\n            OR @Is_All_OK = 0\r\n            BEGIN\r\n                SET @Error_Message = 'Connection to Linked Server '\r\n                    + @Remote_Server_Name\r\n                    + ' has dropped or cannot be resolved. The error massage recorded by this process is as follows: '\r\n                    + @errMsg\r\n                    + ' This package cannot proceed any further....please troubleshoot!';\r\n                IF @isDebugMode = 1\r\n                    BEGIN\r\n                        PRINT @Error_Message;\r\n                    END;\r\n            END;\r\n        ELSE\r\n            BEGIN\r\n                SET @Is_All_OK = 1;\r\n                SET @Error_Message = 'All Good !';\r\n                IF @isDebugMode = 1\r\n                    BEGIN\r\n                        PRINT 'Connection to Linked Server '\r\n                            + @Remote_Server_Name + ' successfull !';\r\n                    END;\r\n            END;\r\n    END;\r\n<\/pre>\n
In case of persisting connection issues (the code will execute 3 times, with the 5-minute wait time between each interval), error message, process name and binary error variable will be passed back to the SSIS package triggering sending an error event (more on the SSIS package implementation in part 4<\/a> to this series).<\/p>\n
Continuing on, conforming to part 1 requirement which stated that ‘The source database schema is under constant development so target database, where the acquired data is stored, needs to be adjusted automatically’, we will focus on automated schema reconciliation. There are number of ways this functionality can be achieved but the simplest (and cheapest, since it does not involve third party tooling) way would be to use plain, old T-SQL wrapped around\u00a0 a stored procedure. The code below compares MySQL internal metadata for selected tables with the one in the staging database on the SQL Server instance and in case any dichotomies are found, it drops and recreates the affected object(s) while resolving any data types, null-ability, characters precisions, scale, length etc. This stored procedure is only applicable to MySQL-to-MSSQL schema synchronisation but I have developed similar code for a PostgreSQL-to-MSSQL job (see my blog post HERE<\/a>) and also included a snipped of T-SQL which transforms it into a MSSQL-to-MSSQL job (please refer to the commented-out section). Also, in order for this code to work correctly, a unique primary key needs to be present on the tables participating in the comparison. Another thing to notice is that this code takes advantage of the data in one of the control tables storing database object names etc. This to ensure that in case we wish to take one or a number of tables out of this process, we can do so via including or excluding it in the referenced control table rather than hardcoding their names in the stored procedure. This allows for a central point of control at a granular level and prevents catering for specific scenarios in the procedure\u2019s code.<\/p>\n
\r\nUSE StagingDB;\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[usp_checkRemoteSvrMySQLTablesSchemaChanges]\r\n    (\r\n      @Remote_Server_Name VARCHAR(256) ,\r\n      @Remote_Server_DB_Name VARCHAR(128) ,\r\n      @Remote_Server_DB_Schema_Name VARCHAR(128) ,\r\n      @Target_DB_Name VARCHAR(128) ,\r\n      @Target_DB_Schema_Name VARCHAR(128) ,\r\n      @Is_All_OK INT OUTPUT ,\r\n      @Process_Name VARCHAR(250) OUTPUT ,\r\n      @Error_Message VARCHAR(MAX) OUTPUT\r\n    )\r\n    WITH RECOMPILE\r\nAS\r\n    SET NOCOUNT ON;\r\n    BEGIN\r\n        DECLARE @Is_ReCheck BIT = 0;\r\n        DECLARE @SQL NVARCHAR(MAX);\r\n        DECLARE @Is_Debug_Mode BIT = 0;\r\n        SET @Process_Name = ( SELECT    OBJECT_NAME(objectid)\r\n                              FROM      sys.dm_exec_requests r\r\n                                        CROSS   APPLY sys.dm_exec_sql_text(r.sql_handle) a\r\n                              WHERE     session_id = @@spid\r\n                            );\r\n\r\n        Check_RemoteSvr_Schema:\r\n        IF OBJECT_ID('tempdb..#t_TblsMetadata') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_TblsMetadata];\t\t\t\r\n            END;  \r\n        CREATE TABLE tempdb..[#t_TblsMetadata]\r\n            (\r\n              table_name VARCHAR(256) ,\r\n              column_name VARCHAR(256) ,\r\n              ordinal_position INT ,\r\n              is_nullable BIT ,\r\n              data_type VARCHAR(256) ,\r\n              character_maximum_length BIGINT ,\r\n              numeric_scale SMALLINT ,\r\n              numeric_precision SMALLINT ,\r\n              is_primary_key BIT ,\r\n              local_schema_name VARCHAR(55) ,\r\n              remote_schema_name VARCHAR(55) ,\r\n              local_or_remote VARCHAR(25)\r\n            );\r\n\t  \r\n        SET @SQL = '\r\n\t\t\t\tINSERT INTO #t_TblsMetadata\r\n\t\t\t\t(\r\n\t\t\t\t\t[table_name]\r\n\t\t\t\t\t,[column_name]\r\n\t\t\t\t\t,[ordinal_position]\r\n\t\t\t\t\t,[is_nullable]\r\n\t\t\t\t\t,[data_type]\r\n\t\t\t\t\t,[character_maximum_length]\r\n\t\t\t\t\t,[numeric_scale]\r\n\t\t\t\t\t,[numeric_precision]\r\n\t\t\t\t\t,[is_primary_key]\r\n\t\t\t\t\t,[local_schema_name]\r\n\t\t\t\t\t,[remote_schema_name]\r\n\t\t\t\t\t,[local_or_remote]\r\n\t\t\t\t)\r\n\t\t\t\tSELECT  \r\n\t\t\t\t\tLTRIM(RTRIM(a.table_name))\t\t\t\t\tAS table_name ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.column_name))\t\t\t\t\tAS column_name ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.ordinal_position))\t\t\tAS ordinal_position ,\r\n\t\t\t\t\tCASE WHEN a.is_nullable = ''YES'' \r\n\t\t\t\t\tTHEN 1 ELSE 0 END\t\t\t\t\t\t\tAS is_nullable ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.data_type))\t\t\t\t\tAS data_type ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.character_maximum_length))\tAS character_maximum_length ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.numeric_scale))\t\t\t\tAS numeric_scale ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.numeric_precision))\t\t\tAS numeric_precision ,\r\n\t\t\t\t\tCASE WHEN a.column_key = \r\n\t\t\t\t\tLTRIM(RTRIM(''pri'')) THEN 1 ELSE 0 END\t\tAS is_primary_key ,\r\n\t\t\t\t\tm.local_schema_Name\t\t\t\t\t\t\tAS local_schema_name ,\r\n\t\t\t\t\tLTRIM(RTRIM(a.table_schema))\t\t\t\tAS remote_schema_name,\r\n\t\t\t\t\t''remote''\t\t\t\t\t\t\t\t\tAS local_or_remote\r\n\t\t\t\tFROM    OPENQUERY(' + @Remote_Server_Name + ',\r\n\t\t\t\t\t\t\t\t\t\t\t''select \r\n\t\t\t\t\t\t\t\t\t\t\ttable_name, \r\n\t\t\t\t\t\t\t\t\t\t\tcolumn_name, \r\n\t\t\t\t\t\t\t\t\t\t\tordinal_position, \r\n\t\t\t\t\t\t\t\t\t\t\tis_nullable, \r\n\t\t\t\t\t\t\t\t\t\t\tdata_type,\r\n\t\t\t\t\t\t\t\t\t\t\tcharacter_maximum_length,\r\n\t\t\t\t\t\t\t\t\t\t\tnumeric_scale,\r\n\t\t\t\t\t\t\t\t\t\t\tnumeric_precision ,\r\n\t\t\t\t\t\t\t\t\t\t\tcolumn_key,\r\n\t\t\t\t\t\t\t\t\t\t\ttable_schema\r\n\t\t\t\t\t\t\t\t\t\t\tfrom information_Schema.columns\r\n\t\t\t\t\t\t\t\t\t\t\twhere table_schema = '''''\r\n\t\t\t\t\t\t\t\t\t\t\t+ @Remote_Server_DB_Schema_Name + ''''''') a \r\n\t\t\t\tJOIN \r\n\t\t\t\t\tControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process m \r\n\t\t\t\t\tON a.table_name = m.local_table_name \t\t\r\n\t\t\t\t\tAND m.local_schema_name = ''' + @Target_DB_Schema_Name + '''\t\t\t\t\t\t\t\r\n\t\t\t\t\tAND m.Remote_Server_Name = ''' + @Remote_Server_Name + '''\r\n\t\t\t\tWHERE m.Is_Active = 1\r\n\t\t\t\tAND NOT EXISTS (SELECT 1\r\n\t\t\t\t\t\t\t\tFROM  ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process_ColumnExceptions o \r\n\t\t\t\t\t\t\t\tWHERE \r\n\t\t\t\t\t\t\t\to.Local_Field_Name = a.column_name AND\r\n\t\t\t\t\t\t\t\to.local_table_name = a.table_name AND \r\n\t\t\t\t\t\t\t\to.local_schema_name = m.local_schema_name AND\r\n\t\t\t\t\t\t\t\to.Remote_Schema_Name = m.remote_schema_name \r\n\t\t\t\t\t\t\t\tAND o.Is_Active = 1)';\r\n\r\n\t\t\/*===========================================================================================\r\n\t\t-- SQL Server Version for reference only\r\n\t\tSET @SQL = 'INSERT INTO #t_TblsMetadata\r\n\t\t\t\t(\r\n\t\t\t\t\t [table_name]\r\n\t\t\t\t\t,[column_name]\r\n\t\t\t\t\t,[ordinal_position]\r\n\t\t\t\t\t,[is_nullable]\r\n\t\t\t\t\t,[data_type]\r\n\t\t\t\t\t,[character_maximum_length]\r\n\t\t\t\t\t,[numeric_scale]\r\n\t\t\t\t\t,[numeric_precision]\r\n\t\t\t\t\t,[is_primary_key]\r\n\t\t\t\t\t,[local_schema_name]\r\n\t\t\t\t\t,[remote_schema_name]\r\n\t\t\t\t\t,[local_or_remote]\r\n\t\t\t\t)\t\t\t\t\r\n\t\t\t\tSELECT \r\n\t\t\t\t\ta.table_name, \r\n\t\t\t\t\ta.column_name, \r\n\t\t\t\t\ta.ordinal_position, \r\n\t\t\t\t\ta.is_nullable, \r\n\t\t\t\t\ta.data_type, \r\n\t\t\t\t\ta.character_maximum_length, \r\n\t\t\t\t\ta.numeric_scale, \r\n\t\t\t\t\ta.numeric_precision, \r\n\t\t\t\t\ta.is_primary_key,\r\n\t\t\t\t\tm.local_schema_name,\r\n\t\t\t\t\ta.remote_schema_name,\r\n\t\t\t\t\t''Remote'' as local_or_remote\r\n\t\t\t\tFROM OPENQUERY(' + @Remote_Server_Name\r\n\t\t\t\t\t\t\t\t\t\t+ ',''SELECT  \r\n\t\t\t\t\t\t\t\t\t\tt.name AS table_name ,\r\n\t\t\t\t\t\t\t\t\t\tc.name AS column_name ,\r\n\t\t\t\t\t\t\t\t\t\tc.column_id AS ordinal_position ,\r\n\t\t\t\t\t\t\t\t\t\tc.is_nullable ,\r\n\t\t\t\t\t\t\t\t\t\tss.name ,\r\n\t\t\t\t\t\t\t\t\t\ttp.name AS data_type ,\r\n\t\t\t\t\t\t\t\t\t\tc.max_length AS character_maximum_length ,\r\n\t\t\t\t\t\t\t\t\t\tc.scale AS numeric_scale ,\r\n\t\t\t\t\t\t\t\t\t\tc.precision AS numeric_precision ,\r\n\t\t\t\t\t\t\t\t\t\tISNULL(idx.pk_flag,0) as ''''is_primary_key'''' ,\r\n\t\t\t\t\t\t\t\t\t\tss.name AS remote_schema_name\r\n\t\t\t\t\t\t\t\t\t\tFROM    sys.tables t\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.columns c ON t.object_id = c.object_id\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.types tp ON c.user_type_id = tp.user_type_id\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.objects so ON so.object_id = t.object_id\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.schemas ss ON so.schema_id = ss.schema_id\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN\t\t(select i.name as index_name, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\ti.is_primary_key as pk_flag, \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tOBJECT_NAME(ic.OBJECT_ID) AS table_name,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tCOL_NAME(ic.OBJECT_ID,ic.column_id) AS column_name \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tFROM sys.indexes AS i INNER JOIN \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND i.index_id = ic.index_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE   i.is_primary_key = 1) idx \r\n\t\t\t\t\t\t\t\t\t\tON idx.table_name = t.name and idx.column_name = c.name\r\n\t\t\t\t\t\t\t\t\t\tWHERE ss.name =''''' + @Remote_Server_DB_Schema_Name + ''''' \r\n\t\t\t\t\t\t\t\t\t\tAND t.type = ''''u'''''') a\r\n\t\t\t\tJOIN \r\n\t\t\t\t\tControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process m \r\n\t\t\t\t\tON a.table_name = m.local_table_name \t\t\t\t\t \r\n\t\t\t\tWHERE \r\n\t\t\t\t\tm.Remote_Server_Name = ''' + @Remote_Server_Name + '''\r\n\t\t\t\t\tAND m.Is_Active = 1\r\n\t\t\t\t\tAND NOT EXISTS (SELECT 1\r\n\t\t\t\t\t\t\t\t\tFROM  ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process_ColumnExceptions o \r\n\t\t\t\t\t\t\t\t\tWHERE \r\n\t\t\t\t\t\t\t\t\to.Local_Field_Name = a.column_name AND\r\n\t\t\t\t\t\t\t\t\to.local_table_name = a.table_name AND \r\n\t\t\t\t\t\t\t\t\to.local_schema_name = m.Local_Schema_Name AND\r\n\t\t\t\t\t\t\t\t\to.Remote_Schema_Name = a.remote_schema_name \r\n\t\t\t\t\t\t\t\t\tAND o.Is_Active = 1)';\t\r\n\t\t===========================================================================================*\/\r\n\t\t\r\n        EXEC(@SQL);\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                PRINT @SQL;\r\n                SELECT  '#t_TblsMetadata table content for remote objects metadata:' AS 'HINT';\r\n                SELECT  *\r\n                FROM    #t_TblsMetadata\r\n                WHERE   local_or_remote = 'Remote'\r\n                ORDER BY table_name ,\r\n                        ordinal_position;\r\n            END;\r\n\t\t\r\n\r\n\r\n        IF @Is_ReCheck = 1\r\n            BEGIN\r\n                GOTO Check_Local_Schema;\r\n            END;\r\n\r\n        Check_Local_Schema:  \r\n        SET @SQL = 'INSERT INTO #t_TblsMetadata\r\n\t\t\t\t(\r\n\t\t\t\t\t[table_name]\r\n\t\t\t\t\t,[column_name]\r\n\t\t\t\t\t,[ordinal_position]\r\n\t\t\t\t\t,[is_nullable]\r\n\t\t\t\t\t,[data_type]\r\n\t\t\t\t\t,[character_maximum_length]\r\n\t\t\t\t\t,[numeric_scale]\r\n\t\t\t\t\t,[numeric_precision]\r\n\t\t\t\t\t,[is_primary_key]\r\n\t\t\t\t\t,[local_schema_name]\r\n\t\t\t\t\t,[remote_schema_name]\r\n\t\t\t\t\t,[local_or_remote]\r\n\t\t\t\t)\t\t\r\n        SELECT  \r\n\t\t\t\t\t\t\t\t\t\tt.name AS table_name ,\r\n\t\t\t\t\t\t\t\t\t\tc.name AS column_name ,\r\n\t\t\t\t\t\t\t\t\t\tc.column_id AS ordinal_position ,\r\n\t\t\t\t\t\t\t\t\t\tc.is_nullable ,\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\ttp.name AS data_type ,\r\n\t\t\t\t\t\t\t\t\t\tc.max_length AS character_maximum_length ,\r\n\t\t\t\t\t\t\t\t\t\tc.scale AS numeric_scale ,\r\n\t\t\t\t\t\t\t\t\t\tc.precision AS numeric_precision ,\r\n\t\t\t\t\t\t\t\t\t\tISNULL(idx.pk_flag,0) as ''is_primary_key'' ,\r\n\t\t\t\t\t\t\t\t\t\tm.local_schema_name,\r\n\t\t\t\t\t\t\t\t\t\tm.remote_schema_name ,\r\n\t\t\t\t\t\t\t\t\t\t''local'' AS local_or_remote\r\n\t\t\t\t\t\t\t\t\t\tFROM    sys.tables t\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.columns c ON t.object_id = c.object_id\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.types tp ON c.user_type_id = tp.user_type_id\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.objects so ON so.object_id = t.object_id\r\n\t\t\t\t\t\t\t\t\t\tJOIN sys.schemas ss ON so.schema_id = ss.schema_id\r\n\t\t\t\t\t\t\t\t\t\tLEFT JOIN\t\t(select i.name as index_name, i.is_primary_key as pk_flag, OBJECT_NAME(ic.OBJECT_ID) AS table_name,\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tCOL_NAME(ic.OBJECT_ID,ic.column_id) AS column_name FROM sys.indexes AS i INNER JOIN \r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tsys.index_columns AS ic ON  i.OBJECT_ID = ic.OBJECT_ID\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tAND i.index_id = ic.index_id\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\tWHERE   i.is_primary_key = 1) idx on idx.table_name = t.name and idx.column_name = c.name\r\n\t\t\t\tJOIN ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process m ON t.name = m.local_table_name AND m.local_schema_name = ss.name\r\n        WHERE   t.type = ''u'' \t\t\t\t \r\n\t\t\t\tAND m.Remote_Server_Name = ''' + @Remote_Server_Name + '''\r\n\t\t\t\tAND ss.name = ''' + @Target_DB_Schema_Name + '''   \r\n\t\t\t\t';\r\n        EXEC(@SQL);\r\n\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                PRINT @SQL;\r\n                SELECT  '#t_TblsMetadata table content for local objects metadata:' AS 'HINT';\r\n                SELECT  *\r\n                FROM    #t_TblsMetadata\r\n                WHERE   local_or_remote = 'Local'\r\n                ORDER BY table_name ,\r\n                        ordinal_position;\r\n            END;\r\n\r\n\r\n        IF OBJECT_ID('tempdb..#t_sql') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_sql];\r\n            END;\r\n\t\t\r\n        SELECT  DISTINCT\r\n                t1.table_name AS Table_Name ,\r\n                t1.local_schema_name AS Local_Schema_Name ,\r\n                'create table [' + t1.local_schema_name + '].['\r\n                + LOWER(t1.table_name) + '] (' + STUFF(o.list, LEN(o.list), 1,\r\n                                                       '') + ')'\r\n                + CASE WHEN t2.is_primary_key = 0 THEN ''\r\n                       ELSE '; ALTER TABLE   [' + t1.local_schema_name + '].['\r\n                            + t1.table_name + '] ' + ' ADD CONSTRAINT pk_'\r\n                            + LOWER(t1.local_schema_name) + '_'\r\n                            + LOWER(t2.table_name) + '_'\r\n                            + LOWER(REPLACE(t2.pk_column_names, ',', '_'))\r\n                            + ' PRIMARY KEY CLUSTERED ' + '('\r\n                            + LOWER(t2.pk_column_names) + ')'\r\n                  END AS Create_Table_Schema_Definition_SQL ,\r\n                'if object_id (''[' + t1.local_schema_name + '].['\r\n                + t1.table_name + ']' + ''', ''U'') IS NOT NULL drop table ['\r\n                + t1.local_schema_name + '].[' + t1.table_name + ']' AS Drop_Table_SQL\r\n        INTO    #t_sql\r\n        FROM    #t_TblsMetadata t1\r\n                CROSS APPLY ( SELECT    '[' + column_name + '] '\r\n                                        + CASE WHEN data_type IN ( 'tinytext',\r\n                                                              'smalltext',\r\n                                                              'mediumtext',\r\n                                                              'text', 'enum',\r\n                                                              'longtext' )\r\n                                               THEN 'varchar'\r\n                                               WHEN data_type IN ( 'timestamp' )\r\n                                               THEN 'datetime'\r\n                                               ELSE data_type\r\n                                          END\r\n                                        + CASE WHEN data_type IN ( 'date',\r\n                                                              'time',\r\n                                                              'tinyint',\r\n                                                              'smallint',\r\n                                                              'int', 'bigint',\r\n                                                              'timestamp',\r\n                                                              'uniqueidentifier',\r\n                                                              'bit',\r\n                                                              'datetimeoffset' )\r\n                                               THEN ''\r\n                                               WHEN character_maximum_length > 8000\r\n                                                    OR character_maximum_length = -1\r\n                                               THEN '(max)'\r\n                                               WHEN data_type IN ( 'nvarchar',\r\n                                                              'nchar' )\r\n                                               THEN '('\r\n                                                    + CAST(character_maximum_length\r\n                                                    \/ 2 AS VARCHAR) + ')'\r\n                                               WHEN data_type = 'decimal'\r\n                                               THEN '('\r\n                                                    + CAST(numeric_precision AS VARCHAR)\r\n                                                    + ', '\r\n                                                    + CAST(numeric_scale AS VARCHAR)\r\n                                                    + ')'\r\n                                               ELSE COALESCE('('\r\n                                                             + CAST(character_maximum_length AS VARCHAR)\r\n                                                             + ')', '')\r\n                                          END + ' '\r\n                                        + ( CASE WHEN is_nullable = 0\r\n                                                 THEN 'NOT '\r\n                                                 ELSE ''\r\n                                            END ) + 'NULL' + ','\r\n                              FROM      #t_TblsMetadata\r\n                              WHERE     table_name = t1.table_name\r\n                                        AND local_or_remote = 'Remote'\r\n                              ORDER BY  ordinal_position\r\n                            FOR\r\n                              XML PATH('')\r\n                            ) o ( list )\r\n                JOIN ( SELECT   table_name ,\r\n                                is_primary_key ,\r\n                                pk_column_names ,\r\n                                column_name = REVERSE(RIGHT(REVERSE(pk_column_names),\r\n                                                            LEN(pk_column_names)\r\n                                                            - CHARINDEX(',',\r\n                                                              REVERSE(pk_column_names))))\r\n                       FROM     ( SELECT    table_name ,\r\n                                            is_primary_key ,\r\n                                            pk_column_names = STUFF(( SELECT\r\n                                                              ','\r\n                                                              + CAST(column_name AS VARCHAR(500))\r\n                                                              FROM\r\n                                                              #t_TblsMetadata z2\r\n                                                              WHERE\r\n                                                              z1.table_name = z2.table_name\r\n                                                              AND z2.is_primary_key = 1\r\n                                                              AND z2.local_or_remote = 'Remote'\r\n                                                              ORDER BY z2.column_name ASC\r\n                                                              FOR\r\n                                                              XML\r\n                                                              PATH('')\r\n                                                              ), 1, 1, '')\r\n                                  FROM      #t_TblsMetadata z1\r\n                                  WHERE     z1.is_primary_key = 1\r\n                                            AND z1.local_or_remote = 'Remote'\r\n                                  GROUP BY  z1.table_name ,\r\n                                            z1.is_primary_key\r\n                                ) a\r\n                     ) t2 ON t1.table_name = t2.table_name\r\n        WHERE   t1.local_or_remote = 'Remote';\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                SELECT  '#t_sql table content:' AS 'HINT';\r\n                SELECT  *\r\n                FROM    #t_sql;\r\n            END;\r\n\r\n        IF @Is_ReCheck = 1\r\n            BEGIN\r\n                GOTO Do_Table_Diff;\r\n            END;\r\n\r\n        Do_Table_Diff:\r\n        IF OBJECT_ID('tempdb..#t_diff') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_diff];\r\n            END;\r\n            WITH    Temp_CTE ( table_name, column_name, is_nullable, data_type, local_schema_name, is_primary_key, character_maximum_length, numeric_scale, numeric_precision )\r\n                      AS ( SELECT   table_name = m.table_name ,\r\n                                    column_name = m.column_name ,\r\n                                    is_nullable = m.is_nullable ,\r\n                                    data_type = CASE WHEN m.data_type IN (\r\n                                                          'tinytext',\r\n                                                          'smalltext',\r\n                                                          'mediumtext', 'text',\r\n                                                          'enum', 'longtext' )\r\n                                                     THEN 'varchar'\r\n                                                     WHEN m.data_type IN (\r\n                                                          'timestamp' )\r\n                                                     THEN 'datetime'\r\n                                                     ELSE m.data_type\r\n                                                END ,\r\n                                    local_schema_name = m.local_schema_name ,\r\n                                    is_primary_key = m.is_primary_key ,\r\n                                    character_maximum_length = COALESCE(CASE\r\n                                                              WHEN m.character_maximum_length > 8000\r\n                                                              OR m.character_maximum_length = -1\r\n                                                              THEN 'max'\r\n                                                              WHEN m.data_type = 'timestamp'\r\n                                                              THEN CAST(l.character_maximum_length AS VARCHAR)\r\n                                                              ELSE CAST(m.character_maximum_length AS VARCHAR)\r\n                                                              END,\r\n                                                              constants.character_maximum_length,\r\n                                                              CAST(l.character_maximum_length AS VARCHAR)) ,\r\n                                    numeric_scale = COALESCE(CASE\r\n                                                              WHEN m.data_type = 'timestamp'\r\n                                                              THEN l.numeric_scale\r\n                                                              ELSE m.numeric_scale\r\n                                                             END,\r\n                                                             constants.numeric_scale,\r\n                                                             l.numeric_scale) ,\r\n                                    numeric_precision = COALESCE(CASE\r\n                                                              WHEN m.data_type = 'timestamp'\r\n                                                              THEN l.numeric_precision\r\n                                                              ELSE m.numeric_precision\r\n                                                              END,\r\n                                                              m.numeric_precision,\r\n                                                              constants.numeric_precision,\r\n                                                              l.numeric_precision)\r\n                           FROM     #t_TblsMetadata m\r\n                                    LEFT JOIN ( SELECT  'char' AS data_type ,\r\n                                                        NULL AS character_maximum_length ,\r\n                                                        0 AS numeric_scale ,\r\n                                                        0 AS numeric_precision\r\n                                                UNION ALL\r\n                                                SELECT  'varchar' ,\r\n                                                        NULL ,\r\n                                                        '0' ,\r\n                                                        '0'\r\n                                                UNION ALL\r\n                                                SELECT  'time' ,\r\n                                                        '5' ,\r\n                                                        '7' ,\r\n                                                        '16'\r\n                                                UNION ALL\r\n                                                SELECT  'date' ,\r\n                                                        '3' ,\r\n                                                        '0' ,\r\n                                                        '10'\r\n                                                UNION ALL\r\n                                                SELECT  'datetime' ,\r\n                                                        '8' ,\r\n                                                        '3' ,\r\n                                                        '23'\r\n                                                UNION ALL\r\n                                                SELECT  'datetime2' ,\r\n                                                        '8' ,\r\n                                                        '7' ,\r\n                                                        '27'\r\n                                                UNION ALL\r\n                                                SELECT  'smalldatetime' ,\r\n                                                        '4' ,\r\n                                                        '0' ,\r\n                                                        '16'\r\n                                                UNION ALL\r\n                                                SELECT  'bit' ,\r\n                                                        '1' ,\r\n                                                        '0' ,\r\n                                                        '1'\r\n                                                UNION ALL\r\n                                                SELECT  'float' ,\r\n                                                        '8' ,\r\n                                                        '0' ,\r\n                                                        '53'\r\n                                                UNION ALL\r\n                                                SELECT  'money' ,\r\n                                                        '8' ,\r\n                                                        '4' ,\r\n                                                        '19'\r\n                                                UNION ALL\r\n                                                SELECT  'smallmoney' ,\r\n                                                        '4' ,\r\n                                                        '4' ,\r\n                                                        '10'\r\n                                                UNION ALL\r\n                                                SELECT  'uniqueidentifier' ,\r\n                                                        '16' ,\r\n                                                        '0' ,\r\n                                                        '0'\r\n                                                UNION ALL\r\n                                                SELECT  'xml' ,\r\n                                                        'max' ,\r\n                                                        '0' ,\r\n                                                        '0'\r\n                                                UNION ALL\r\n                                                SELECT  'numeric' ,\r\n                                                        '9' ,\r\n                                                        '0' ,\r\n                                                        '18'\r\n                                                UNION ALL\r\n                                                SELECT  'real' ,\r\n                                                        '4' ,\r\n                                                        '0' ,\r\n                                                        '24'\r\n                                                UNION ALL\r\n                                                SELECT  'tinyint' ,\r\n                                                        '1' ,\r\n                                                        '0' ,\r\n                                                        '3'\r\n                                                UNION ALL\r\n                                                SELECT  'smallint' ,\r\n                                                        '2' ,\r\n                                                        '0' ,\r\n                                                        '5'\r\n                                                UNION ALL\r\n                                                SELECT  'int' ,\r\n                                                        '4' ,\r\n                                                        '0' ,\r\n                                                        '10'\r\n                                                UNION ALL\r\n                                                SELECT  'bigint' ,\r\n                                                        '8' ,\r\n                                                        '0' ,\r\n                                                        '20'\r\n                                              ) constants ON m.data_type = constants.data_type\r\n                                    LEFT JOIN #t_TblsMetadata l ON l.column_name = m.column_name\r\n                                                              AND l.table_name = m.table_name\r\n                                                              AND l.data_type = ( CASE\r\n                                                              WHEN m.data_type IN (\r\n                                                              'tinytext',\r\n                                                              'smalltext',\r\n                                                              'mediumtext',\r\n                                                              'text', 'enum',\r\n                                                              'longtext' )\r\n                                                              THEN 'varchar'\r\n                                                              WHEN m.data_type IN (\r\n                                                              'timestamp' )\r\n                                                              THEN 'datetime'\r\n                                                              ELSE m.data_type\r\n                                                              END )\r\n                                                              AND l.local_or_remote = 'Local'\r\n                           WHERE    m.local_or_remote = 'Remote'\r\n                           EXCEPT\r\n                           SELECT   table_name ,\r\n                                    column_name ,\r\n                                    is_nullable ,\r\n                                    data_type ,\r\n                                    local_schema_name ,\r\n                                    is_primary_key ,\r\n                                    CASE WHEN character_maximum_length > 8000\r\n                                              OR character_maximum_length = -1\r\n                                         THEN 'max'\r\n                                         ELSE CAST(character_maximum_length AS VARCHAR)\r\n                                    END AS character_maximum_length ,\r\n                                    numeric_scale ,\r\n                                    numeric_precision\r\n                           FROM     #t_TblsMetadata\r\n                           WHERE    local_or_remote = 'Local'\r\n                         )\r\n            SELECT DISTINCT\r\n                    table_name ,\r\n                    local_schema_name\r\n            INTO    #t_diff\r\n            FROM    Temp_CTE;\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                SELECT  '#t_diff table content:' AS 'HINT';\r\n                SELECT  *\r\n                FROM    #t_diff;\r\n            END;\r\n\r\n        IF @Is_ReCheck = 1\r\n            GOTO Results;\r\n\r\n        Run_SQL:\r\n        IF NOT EXISTS ( SELECT DISTINCT\r\n                                Table_Name ,\r\n                                Local_Schema_Name\r\n                        FROM    #t_sql a\r\n                        WHERE   EXISTS ( SELECT table_name\r\n                                         FROM   #t_diff i\r\n                                         WHERE  a.Table_Name = i.table_name ) )\r\n            BEGIN\r\n                GOTO Schema_Diff_ReCheck;\r\n            END;\r\n        ELSE\r\n            BEGIN\r\n                DECLARE @schema_name VARCHAR(50);\r\n                DECLARE @table_name VARCHAR(256);\r\n                DECLARE @sql_select_dropcreate NVARCHAR(MAX);\r\n\r\n                DECLARE db_cursor CURSOR FORWARD_ONLY\r\n                FOR\r\n                    SELECT DISTINCT\r\n                            Table_Name ,\r\n                            Local_Schema_Name\r\n                    FROM    #t_sql a\r\n                    WHERE   EXISTS ( SELECT table_name\r\n                                     FROM   #t_diff i\r\n                                     WHERE  a.Table_Name = i.table_name );\r\n                OPEN db_cursor;\r\n                FETCH NEXT\r\n\t\t\tFROM db_cursor INTO @table_name, @schema_name;\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\r\n                        BEGIN TRY\r\n                            BEGIN TRANSACTION;\r\n                            SET @sql_select_dropcreate = ( SELECT\r\n                                                              Drop_Table_SQL\r\n                                                           FROM\r\n                                                              #t_sql\r\n                                                           WHERE\r\n                                                              Table_Name = @table_name\r\n                                                         ) + '; '\r\n                                + ( SELECT  Create_Table_Schema_Definition_SQL\r\n                                    FROM    #t_sql\r\n                                    WHERE   Table_Name = @table_name\r\n                                  );                                        \r\n                            IF @Is_Debug_Mode = 1\r\n                                BEGIN\r\n                                    PRINT @sql_select_dropcreate;\r\n                                END;\r\n                            EXEC sp_sqlexec @sql_select_dropcreate;\r\n                            SET @Error_Message = 'All Good!';\r\n                            COMMIT TRANSACTION;\r\n                        END TRY\r\n                        BEGIN CATCH\t\t\r\n                            IF @@TRANCOUNT > 0\r\n                                ROLLBACK TRANSACTION;\r\n                            SET @Is_All_OK = 0;\r\n                            SET @Error_Message = 'This operation has been unexpectandly terminated due to error: '''\r\n                                + ERROR_MESSAGE() + ''' at line '\r\n                                + CAST(ERROR_LINE() AS VARCHAR);\t\t\t\t\t\t\t\t\r\n                        END CATCH;\r\n                        FETCH NEXT FROM db_cursor INTO @table_name,\r\n                            @schema_name;\r\n                    END;\r\n                CLOSE db_cursor;\r\n                DEALLOCATE db_cursor;\r\n                SET @Is_ReCheck = 1;\r\n            END;\r\n\r\n\r\n        Schema_Diff_ReCheck:\r\n        IF @Is_ReCheck = 1\r\n            BEGIN\r\n                GOTO Check_RemoteSvr_Schema;\r\n            END;\r\n\r\n        Results:\r\n        IF EXISTS ( SELECT TOP 1\r\n                            *\r\n                    FROM    #t_diff )\r\n            BEGIN \r\n                SET @Is_All_OK = 0;\r\n                SET @Error_Message = 'Table schema reconciliation between '\r\n                    + '' + @@SERVERNAME + '' + ' and remote database on '''\r\n                    + @Remote_Server_Name + '''' + CHAR(10);\r\n                SET @Error_Message = @Error_Message\r\n                    + 'failed. Please troubleshoot.';\r\n            END;\r\n        ELSE\r\n            BEGIN\r\n                SET @Is_All_OK = 1;\r\n                SET @Error_Message = 'All Good!';\r\n            END;\r\n\r\n\r\n\r\n\r\n        IF OBJECT_ID('tempdb..#t_TblsMetadata') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_TblsMetadata];\r\n            END; \r\n\r\n        IF OBJECT_ID('tempdb..#t_sql') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_sql];\r\n            END;\r\n\r\n        IF OBJECT_ID('tempdb..#t_diff') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE [#t_diff];\r\n            END;\r\n\t\r\n    END;\r\nGO\r\n<\/pre>\n
Next step in the process involves dropping any indexes. For this task, the following stored procedure can be used, which also allows indexes re-creation and reorganization once all the source data has been copied across. In this way we can reference the same code for both actions, changing variable values based on the intended action i.e. setting @Create_Drop_Idxs value to either DROP or CREATE. Again, this code references specific ControlDB database object to allow for finer control and customisations and also creates a small table on the StagingDB database later used in indexes recreation process before it gets deleted at the step completion.<\/p>\n
\r\nUSE StagingDB;\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[usp_runCreateDropStagingIDXs]\r\n    (\r\n      @Target_DB_Name VARCHAR(128) ,\r\n      @Target_DB_Schema_Name VARCHAR(128) ,\r\n      @Create_Drop_Idxs VARCHAR(6) ,\r\n      @Reorg_PKs CHAR(3) ,\r\n      @Drop_TempTbls CHAR(3) ,\r\n      @Error_Message VARCHAR(2000) OUTPUT ,\r\n      @Process_Name VARCHAR(256) OUTPUT ,\r\n      @Is_All_OK INT OUTPUT\t  \r\n    )\r\n    WITH RECOMPILE\r\nAS\r\n    SET NOCOUNT ON;\r\n    BEGIN\t\r\n        DECLARE @Is_Debug_Mode BIT = 1;\r\n        DECLARE @err INT;\r\n        DECLARE @SQL NVARCHAR(MAX);\r\n        DECLARE @Index_Or_PKName VARCHAR(256);\r\n        DECLARE @Tgt_Object_Name VARCHAR(256);\r\n        DECLARE @ID INT;\r\n        DECLARE @Index_Type VARCHAR(128); \r\n        DECLARE @Index_ColNames VARCHAR(1024); \r\n        DECLARE @PkColNames VARCHAR(1024);\r\n        DECLARE @Is_Unique VARCHAR(56);\r\n        DECLARE @Indx_Options VARCHAR(MAX);\r\n\r\n        SET @Process_Name = ( SELECT    OBJECT_NAME(objectid)\r\n                              FROM      sys.dm_exec_requests r\r\n                                        CROSS   APPLY sys.dm_exec_sql_text(r.sql_handle) a\r\n                              WHERE     session_id = @@spid\r\n                            );\r\n\r\n        IF EXISTS ( SELECT  a.TABLE_NAME ,\r\n                            a.TABLE_SCHEMA ,\r\n                            b.create_date\r\n                    FROM    INFORMATION_SCHEMA.TABLES a\r\n                            JOIN sys.objects b ON a.TABLE_NAME = b.name\r\n                    WHERE   a.TABLE_NAME = 'Temp_Staging_PKsIndexes_MetaData'\r\n                            AND a.TABLE_SCHEMA = 'dbo' --AND DATEDIFF(SECOND, b.create_date, SYSDATETIME()) > 10 \r\n\t\t\t\t\t\t)\r\n            BEGIN                  \r\n                DROP TABLE StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData;\r\n            END;\r\n\r\n        CREATE TABLE [dbo].[Temp_Staging_PKsIndexes_MetaData]\r\n            (\r\n              ID INT IDENTITY(1, 1)\r\n                     NOT NULL ,\r\n              [DBName] [sysname] NOT NULL ,\r\n              [SchemaName] [sysname] NOT NULL ,\r\n              [TableName] [NVARCHAR](128) NULL ,\r\n              [IndexOrPKName] [sysname] NULL ,\r\n              [IndexType] [NVARCHAR](60) NULL ,\r\n              [IsUnique] [VARCHAR](6) NOT NULL ,\r\n              [IsPK] [VARCHAR](11) NOT NULL ,\r\n              [PKColNames] [VARCHAR](MAX) NULL ,\r\n              [IndexColNames] [VARCHAR](MAX) NULL ,\r\n              [Indx_Options] VARCHAR(MAX) NULL ,\r\n              [From_MetaData] [BIT] NULL\r\n            ); \t\t\t\t         \r\n          \r\n            \r\n\r\n       \r\n        ;\r\n        WITH    TempTbl ( dbName, schemaName, tableName, indexOrPKName, isUniqueConstraint, isUnique, isPK, indexType, colNames )\r\n                  AS ( SELECT   dbName = tt.TABLE_CATALOG ,\r\n                                schemaName = s.name ,\r\n                                tableName = OBJECT_NAME(i.object_id) ,\r\n                                indexOrPKName = i.name ,\r\n                                isUniqueConstraint = i.is_unique_constraint ,\r\n                                isUnique = i.is_unique ,\r\n                                isPK = i.is_primary_key ,\r\n                                indexType = i.type_desc ,\r\n                                participatingColNames = c.name\r\n                       FROM     StagingDB.sys.indexes i\r\n                                JOIN StagingDB.sys.index_columns AS ic ON i.object_id = ic.object_id\r\n                                                              AND i.index_id = ic.index_id\r\n                                JOIN StagingDB.sys.columns AS c ON ic.object_id = c.object_id\r\n                                                              AND c.column_id = ic.column_id\r\n                                JOIN StagingDB.sys.tables t ON c.object_id = t.object_id\r\n                                JOIN StagingDB.sys.schemas s ON t.schema_id = s.schema_id\r\n                                JOIN INFORMATION_SCHEMA.TABLES tt ON tt.TABLE_SCHEMA = s.name\r\n                                                              AND tt.TABLE_NAME = OBJECT_NAME(i.object_id)\r\n                       WHERE    t.type = 'U'\r\n                                AND s.name = @Target_DB_Schema_Name\r\n                                AND tt.TABLE_CATALOG = @Target_DB_Name\r\n                     )\r\n            INSERT  INTO [Temp_Staging_PKsIndexes_MetaData]\r\n                    ( [DBName] ,\r\n                      [SchemaName] ,\r\n                      [TableName] ,\r\n                      [IndexOrPKName] ,\r\n                      [IndexType] ,\r\n                      [IsUnique] ,\r\n                      [IsPK] ,\r\n                      [PKColNames] ,\r\n                      [IndexColNames] ,\r\n                      [Indx_Options] ,\r\n                      [From_MetaData]\r\n                    )\r\n                    SELECT DISTINCT\r\n                            OutTab.dbName ,\r\n                            OutTab.schemaName ,\r\n                            OutTab.tableName ,\r\n                            OutTab.indexOrPKName ,\r\n                            OutTab.indexType ,\r\n                            CASE WHEN OutTab.isUnique = 1 THEN 'UNIQUE'\r\n                                 ELSE ''\r\n                            END AS 'IsUnique' ,\r\n                            CASE WHEN OutTab.isPK = 1 THEN 'PRIMARY KEY'\r\n                                 ELSE ''\r\n                            END AS 'IsPK' ,\r\n                            PKColNames = COALESCE(STUFF((SELECT\r\n                                                              ','\r\n                                                              + InrTab.colNames\r\n                                                         FROM TempTbl InrTab\r\n                                                         WHERE\r\n                                                              InrTab.tableName = OutTab.tableName\r\n                                                              AND InrTab.indexOrPKName = OutTab.indexOrPKName\r\n                                                              AND IsPK = 1\r\n                                                         ORDER BY InrTab.colNames\r\n                                                  FOR   XML PATH('') ,\r\n                                                            TYPE).value('.',\r\n                                                              'VARCHAR(MAX)'),\r\n                                                        1, 1, SPACE(0)), '') ,\r\n                            IndexColNames = COALESCE(STUFF((SELECT\r\n                                                              ','\r\n                                                              + InrTab.colNames\r\n                                                            FROM\r\n                                                              TempTbl InrTab\r\n                                                            WHERE\r\n                                                              InrTab.tableName = OutTab.tableName\r\n                                                              AND InrTab.indexOrPKName = OutTab.indexOrPKName\r\n                                                              AND IsPK <> 1\r\n                                                            ORDER BY InrTab.colNames\r\n                                                     FOR   XML\r\n                                                              PATH('') ,\r\n                                                              TYPE).value('.',\r\n                                                              'VARCHAR(MAX)'),\r\n                                                           1, 1, SPACE(0)), '') ,\r\n                            '' ,\r\n                            1\r\n                    FROM    ( SELECT DISTINCT\r\n                                        tr.dbName ,\r\n                                        tr.schemaName ,\r\n                                        tr.tableName ,\r\n                                        tr.indexOrPKName ,\r\n                                        tr.indexType ,\r\n                                        tr.colNames ,\r\n                                        tr.isUnique ,\r\n                                        tr.isPK\r\n                              FROM      TempTbl tr\r\n                            ) AS OutTab;\r\n\r\n        INSERT  INTO Temp_Staging_PKsIndexes_MetaData\r\n                ( DBName ,\r\n                  SchemaName ,\r\n                  TableName ,\r\n                  IndexOrPKName ,\r\n                  IndexType ,\r\n                  IsUnique ,\r\n                  IsPK ,\r\n                  PKColNames ,\r\n                  IndexColNames ,\r\n                  Indx_Options ,\r\n                  From_MetaData\r\n                )\r\n                SELECT  [Database_Name] ,\r\n                        [Schema_Name] ,\r\n                        [Table_Name] ,\r\n                        [Index_or_PKName] ,\r\n                        [Index_Type] ,\r\n                        [Is_Unique] ,\r\n                        [Is_PK] ,\r\n                        [PK_ColNames] ,\r\n                        [Indx_ColNames] ,\r\n                        [Indx_Options] ,\r\n                        0\r\n                FROM    [ControlDB].[dbo].[Ctrl_INDXandPKs2Process]\r\n                WHERE   Database_Name = @Target_DB_Name\r\n                        AND [Schema_Name] = @Target_DB_Schema_Name; \r\n\r\n\r\n        IF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                SELECT  *\r\n                FROM    Temp_Staging_PKsIndexes_MetaData\r\n                WHERE   DBName = @Target_DB_Name\r\n                        AND SchemaName = @Target_DB_Schema_Name;\r\n            END;\r\n\r\n\r\n        IF @Create_Drop_Idxs = LTRIM(RTRIM(UPPER('DROP')))\r\n            AND ( SELECT    COUNT(1) AS ct\r\n                  FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                  WHERE     From_MetaData = 1\r\n                            AND DBName = @Target_DB_Name\r\n                            AND SchemaName = @Target_DB_Schema_Name\r\n                            AND ( IsPK IS NULL\r\n                                  OR IsPK = ''\r\n                                )\r\n                            AND IndexColNames IS NOT NULL\r\n                ) > 0\r\n            BEGIN\r\n\t\t\t\t\t                      \r\n                IF CURSOR_STATUS('global', 'db_idxcursor') >= -1\r\n                    BEGIN\r\n                        DEALLOCATE db_idxcursor;\r\n                    END;\r\n                DECLARE db_idxcursor CURSOR FORWARD_ONLY\r\n                FOR\r\n                    SELECT  IndexOrPKName ,\r\n                            SchemaName ,\r\n                            TableName ,\r\n                            ID\r\n                    FROM    Temp_Staging_PKsIndexes_MetaData\r\n                    WHERE   From_MetaData = 1\r\n                            AND ( IsPK IS NULL\r\n                                  OR IsPK = ''\r\n                                )\r\n                            AND IndexColNames IS NOT NULL\r\n                            AND DBName = @Target_DB_Name\r\n                            AND SchemaName = @Target_DB_Schema_Name;\r\n                           \r\n                SELECT  @err = @@error;\r\n                IF @err <> 0\r\n                    BEGIN\r\n                        DEALLOCATE db_idxcursor;\r\n                        RETURN @err;\r\n                    END;\r\n                OPEN db_idxcursor;\r\n                SELECT  @err = @@error;\r\n                IF @err <> 0\r\n                    BEGIN\r\n                        DEALLOCATE db_idxcursor;\r\n                        RETURN @err;\r\n                    END;\r\n                FETCH NEXT FROM db_idxcursor INTO @Index_Or_PKName,\r\n                    @Target_DB_Schema_Name, @Tgt_Object_Name, @ID;\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\r\n                        SET @SQL = N'DROP INDEX  ' + @Index_Or_PKName + ''\r\n                            + CHAR(10);\r\n                        SET @SQL = @SQL + 'ON ' + @Target_DB_Schema_Name\r\n                            + '.[' + @Tgt_Object_Name + '];';\r\n                        IF @Is_Debug_Mode = 1\r\n                            BEGIN\r\n                                PRINT @SQL;\r\n                            END;\r\n                        BEGIN TRY\r\n                            BEGIN TRANSACTION;  \r\n                            EXEC (@SQL);\r\n                            COMMIT TRANSACTION;\r\n                            SET @Is_All_OK = 1;\r\n                            SET @Error_Message = 'All Good!';\r\n                        END TRY\r\n                        BEGIN CATCH\r\n                            IF @@TRANCOUNT > 0\r\n                                ROLLBACK TRANSACTION;\r\n                            SET @Is_All_OK = 0;\r\n                            SET @Error_Message = 'This operation has been unexpectandly terminated due to error: '''\r\n                                + ERROR_MESSAGE() + ''' at line '\r\n                                + CAST(ERROR_LINE() AS VARCHAR);\r\n                            THROW;\r\n                        END CATCH;\r\n                        FETCH NEXT FROM db_idxcursor INTO @Index_Or_PKName,\r\n                            @Target_DB_Schema_Name, @Tgt_Object_Name, @ID;\r\n                    END;\r\n                CLOSE db_idxcursor;\r\n                DEALLOCATE db_idxcursor;                            \r\n            END;\r\n\r\n\r\n\r\n        IF @Create_Drop_Idxs = LTRIM(RTRIM(UPPER('DROP')))\r\n            AND ( SELECT    COUNT(1) AS ct\r\n                  FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                  WHERE     From_MetaData = 1\r\n                            AND ( IsPK IS NULL\r\n                                  OR IsPK = ''\r\n                                )\r\n                            AND IndexColNames IS NOT NULL\r\n                            AND DBName = @Target_DB_Name\r\n                            AND SchemaName = @Target_DB_Schema_Name\r\n                ) = 0\r\n            BEGIN \r\n                SET @Is_All_OK = 1;\r\n                SET @Error_Message = 'All Good! No indexes (outside of Primary Keys) have been found on participating tables '\r\n                    + CHAR(10);\r\n                SET @Error_Message = @Error_Message\r\n                    + 'therefore tables have not been altered by the cursor.';\r\n            END;\t\t\t\t\r\n\r\n\r\n        IF @Create_Drop_Idxs = LTRIM(RTRIM(UPPER('CREATE')))\r\n            AND EXISTS ( SELECT TOP 1\r\n                                *\r\n                         FROM   ( SELECT    [DBName] ,\r\n                                            [SchemaName] ,\r\n                                            [TableName] ,\r\n                                            [IndexOrPKName]\r\n                                  FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                                  WHERE     From_MetaData = 0\r\n                                            AND ( IsPK IS NULL\r\n                                                  OR IsPK = ''\r\n                                                )\r\n                                            AND IndexColNames IS NOT NULL\r\n                                            AND DBName = @Target_DB_Name\r\n                                            AND SchemaName = @Target_DB_Schema_Name\r\n                                  EXCEPT\r\n                                  SELECT    [DBName] ,\r\n                                            [SchemaName] ,\r\n                                            [TableName] ,\r\n                                            [IndexOrPKName]\r\n                                  FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                                  WHERE     From_MetaData = 1\r\n                                            AND ( IsPK IS NULL\r\n                                                  OR IsPK = ''\r\n                                                )\r\n                                            AND IndexColNames IS NOT NULL\r\n                                            AND DBName = @Target_DB_Name\r\n                                            AND SchemaName = @Target_DB_Schema_Name\r\n                                ) a\r\n                                JOIN ( SELECT   t.name AS TblName\r\n                                       FROM     sys.tables t\r\n                                                JOIN sys.schemas s ON t.schema_id = s.schema_id\r\n                                       WHERE    s.name = @Target_DB_Schema_Name\r\n                                                AND t.name NOT LIKE 'Temp%'\r\n                                     ) b ON a.TableName = b.TblName )\r\n            BEGIN      \r\n                IF CURSOR_STATUS('global', 'db_fkcursor') >= -1\r\n                    BEGIN\r\n                        DEALLOCATE db_idxcursor;\r\n                    END;\r\n                DECLARE db_idxcursor CURSOR FORWARD_ONLY\r\n                FOR\r\n                    SELECT DISTINCT\r\n                            a.[ID] ,\r\n                            a.[DBName] ,\r\n                            a.[SchemaName] ,\r\n                            a.[TableName] ,\r\n                            a.[IndexOrPKName] ,\r\n                            a.[indexType] ,\r\n                            a.[IndexColNames] ,\r\n                            a.[PkColNames] ,\r\n                            a.[Indx_Options] ,\r\n                            a.[IsUnique]\r\n                    FROM    StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData a\r\n                            INNER JOIN ( SELECT a.*\r\n                                         FROM   ( SELECT    [DBName] ,\r\n                                                            [SchemaName] ,\r\n                                                            [TableName] ,\r\n                                                            [IndexOrPKName]\r\n                                                  FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                                                  WHERE     From_MetaData = 0\r\n                                                            AND ( IsPK IS NULL\r\n                                                              OR IsPK = ''\r\n                                                              )\r\n                                                            AND IndexColNames IS NOT NULL\r\n                                                            AND DBName = @Target_DB_Name\r\n                                                            AND SchemaName = @Target_DB_Schema_Name\r\n                                                  EXCEPT\r\n                                                  SELECT    [DBName] ,\r\n                                                            [SchemaName] ,\r\n                                                            [TableName] ,\r\n                                                            [IndexOrPKName]\r\n                                                  FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                                                  WHERE     From_MetaData = 1\r\n                                                            AND ( IsPK IS NULL\r\n                                                              OR IsPK = ''\r\n                                                              )\r\n                                                            AND IndexColNames IS NOT NULL\r\n                                                            AND DBName = @Target_DB_Name\r\n                                                            AND SchemaName = @Target_DB_Schema_Name\r\n                                                ) a\r\n                                                JOIN ( SELECT t.name AS TblName\r\n                                                       FROM   sys.tables t\r\n                                                              JOIN sys.schemas s ON t.schema_id = s.schema_id\r\n                                                       WHERE  s.name = @Target_DB_Schema_Name\r\n                                                     ) b ON a.TableName = b.TblName\r\n                                       ) d ON d.[TableName] = a.[TableName]\r\n                                              AND d.[IndexOrPKName] = a.[IndexOrPKName]\r\n                                              AND d.[SchemaName] = a.[SchemaName]\r\n                                              AND d.DBName = a.DBName;                                      \r\n                SELECT  @err = @@error;\r\n                IF @err <> 0\r\n                    BEGIN\r\n                        DEALLOCATE db_idxcursor;\r\n                        RETURN @err;\r\n                    END;\r\n                OPEN db_idxcursor;\r\n                SELECT  @err = @@error;\r\n                IF @err <> 0\r\n                    BEGIN\r\n                        DEALLOCATE db_idxcursor;\r\n                        RETURN @err;\r\n                    END;\r\n                FETCH NEXT\r\n\t\t\t\t\tFROM db_idxcursor \r\n\t\t\t\t\tINTO @ID, @Target_DB_Name, @Target_DB_Schema_Name,\r\n                    @Tgt_Object_Name, @Index_Or_PKName, @Index_Type,\r\n                    @Index_ColNames, @PkColNames, @Indx_Options, @Is_Unique;\r\n                WHILE @@FETCH_STATUS = 0\r\n                    BEGIN\t\t\t\t\t\t\r\n                        SET @SQL = 'CREATE ' + @Index_Type + ' INDEX '\r\n                            + @Index_Or_PKName + ' ON [' + @Target_DB_Name\r\n                            + '].[' + @Target_DB_Schema_Name + '].['\r\n                            + @Tgt_Object_Name + ']' + CHAR(10);\r\n                        SET @SQL = @SQL\r\n                            + CASE WHEN @Index_Type = 'CLUSTERED'\r\n                                   THEN ' (' + @PkColNames + ')'\r\n                                   WHEN @Index_Type = 'CLUSTERED COLUMNSTORE'\r\n                                   THEN ''\r\n                                   ELSE ' (' + @Index_ColNames + ')'\r\n                              END + CHAR(10);\r\n                        SET @SQL = @SQL + '' + @Indx_Options + '' + CHAR(10);\r\n\t\t\t\t\t\t\t\t\t\t\t\r\n                        BEGIN TRY\r\n                            IF @Is_Debug_Mode = 1\r\n                                BEGIN\r\n                                    PRINT @SQL;\r\n                                END;\r\n                            BEGIN TRANSACTION;\t\t\t\t\t\t\t \r\n                            EXEC (@SQL);\t\t\t\t\t\t\t\r\n                            COMMIT TRANSACTION;\r\n                            SET @Is_All_OK = 1;\r\n                            SET @Error_Message = 'All Good!';\r\n                        END TRY\r\n                        BEGIN CATCH\r\n                            IF @@TRANCOUNT > 0\r\n                                ROLLBACK TRANSACTION;\r\n                            SET @Is_All_OK = 0;\r\n                            SET @Error_Message = 'This operation has been unexpectandly terminated due to error: '''\r\n                                + ERROR_MESSAGE() + ''' at line '\r\n                                + CAST(ERROR_LINE() AS VARCHAR);\r\n\t\t\t\t\t\t--THROW;\r\n                        END CATCH;\t\r\n                        FETCH NEXT\tFROM db_idxcursor \r\n\t\t\t\t\t\tINTO @ID, @Target_DB_Name, @Target_DB_Schema_Name,\r\n                            @Tgt_Object_Name, @Index_Or_PKName, @Index_Type,\r\n                            @Index_ColNames, @PkColNames, @Indx_Options,\r\n                            @Is_Unique;\r\n                    END;\r\n                CLOSE db_idxcursor;\r\n                DEALLOCATE db_idxcursor;\t\t\t\t\t\t\t\t\r\n                    --END\r\n            END;     \r\n\r\n\r\n\r\n        IF @Create_Drop_Idxs = LTRIM(RTRIM(UPPER('CREATE')))\r\n            AND NOT EXISTS ( SELECT TOP 1\r\n                                    *\r\n                             FROM   ( SELECT    [DBName] ,\r\n                                                [SchemaName] ,\r\n                                                [TableName] ,\r\n                                                [IndexOrPKName]\r\n                                      FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                                      WHERE     From_MetaData = 0\r\n                                                AND ( IsPK IS NULL\r\n                                                      OR IsPK = ''\r\n                                                    )\r\n                                                AND IndexColNames IS NOT NULL\r\n                                                AND DBName = @Target_DB_Name\r\n                                                AND SchemaName = @Target_DB_Schema_Name\r\n                                      EXCEPT\r\n                                      SELECT    [DBName] ,\r\n                                                [SchemaName] ,\r\n                                                [TableName] ,\r\n                                                [IndexOrPKName]\r\n                                      FROM      StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData\r\n                                      WHERE     From_MetaData = 1\r\n                                                AND ( IsPK IS NULL\r\n                                                      OR IsPK = ''\r\n                                                    )\r\n                                                AND IndexColNames IS NOT NULL\r\n                                                AND DBName = @Target_DB_Name\r\n                                                AND SchemaName = @Target_DB_Schema_Name\r\n                                    ) a\r\n                                    JOIN ( SELECT   t.name AS TblName\r\n                                           FROM     sys.tables t\r\n                                                    JOIN sys.schemas s ON t.schema_id = s.schema_id\r\n                                           WHERE    s.name = @Target_DB_Schema_Name\r\n                                                    AND t.name NOT LIKE 'Temp%'\r\n                                         ) b ON a.TableName = b.TblName )\r\n            BEGIN     \r\n                SET @Is_All_OK = 1;\r\n                SET @Error_Message = 'All Good! All indexes have already been created on the referenced tables.';                    \r\n            END;\t   \r\n\r\n\r\n        IF @Reorg_PKs = LTRIM(RTRIM(UPPER('YES')))\r\n            AND EXISTS ( SELECT *\r\n                         FROM   INFORMATION_SCHEMA.TABLES\r\n                         WHERE  TABLE_NAME = 'Temp_Staging_PKsIndexes_MetaData'\r\n                                AND TABLE_SCHEMA = 'dbo' )\r\n            BEGIN\r\n                BEGIN      \r\n                    IF CURSOR_STATUS('global', 'db_pkreorgcursor') >= -1\r\n                        BEGIN\r\n                            DEALLOCATE db_pkreorgcursor;\r\n                        END;\r\n                    DECLARE db_pkreorgcursor CURSOR FORWARD_ONLY\r\n                    FOR\r\n                        SELECT DISTINCT\r\n                                temp.ID ,\r\n                                temp.DBName ,\r\n                                temp.SchemaName ,\r\n                                temp.TableName ,\r\n                                i.name AS IndexName\r\n                        FROM    sys.indexes AS i\r\n                                JOIN sys.index_columns AS ic ON i.object_id = ic.object_id\r\n                                                              AND i.index_id = ic.index_id\r\n                                JOIN StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData temp ON temp.IndexOrPKName = i.name\r\n                                JOIN INFORMATION_SCHEMA.TABLES t ON t.TABLE_SCHEMA = temp.SchemaName\r\n                                                              AND t.TABLE_NAME = temp.TableName\r\n                                                              AND t.TABLE_CATALOG = temp.DBName\r\n                        WHERE   i.is_primary_key = 1\r\n                                AND temp.From_MetaData = 1\r\n                                AND temp.SchemaName = @Target_DB_Schema_Name\r\n                                AND temp.DBName = @Target_DB_Name;                                  \r\n                    SELECT  @err = @@error;\r\n                    IF @err <> 0\r\n                        BEGIN\r\n                            DEALLOCATE db_pkreorgcursor;\r\n                            RETURN @err;\r\n                        END;\r\n                    OPEN db_pkreorgcursor;\r\n                    SELECT  @err = @@error;\r\n                    IF @err <> 0\r\n                        BEGIN\r\n                            DEALLOCATE db_pkreorgcursor;\r\n                            RETURN @err;\r\n                        END;\r\n                    FETCH NEXT\r\n\t\t\t\t\t\tFROM db_pkreorgcursor INTO @ID, @Target_DB_Name,\r\n                        @Target_DB_Schema_Name, @Tgt_Object_Name,\r\n                        @Index_Or_PKName; \r\n                    WHILE @@FETCH_STATUS = 0\r\n                        BEGIN\t\t\t\t\t\t\r\n                            SET @SQL = N'ALTER INDEX  [' + @Index_Or_PKName\r\n                                + ']' + CHAR(10);\r\n                            SET @SQL = @SQL + 'ON  [' + @Target_DB_Name\r\n                                + '].[' + @Target_DB_Schema_Name + '].['\r\n                                + @Tgt_Object_Name + '] ';\r\n                            SET @SQL = @SQL\r\n                                + 'REBUILD PARTITION = ALL WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON);';\r\n                            BEGIN TRY\r\n                                IF @Is_Debug_Mode = 1\r\n                                    BEGIN \r\n                                        PRINT @SQL; \r\n                                    END; \r\n                                BEGIN TRANSACTION;\t\t\t\t\t\t\t\r\n                                EXEC (@SQL);\t\t\t\t\t\t\t\r\n                                COMMIT TRANSACTION;\r\n                                SET @Is_All_OK = 1;\r\n                                SET @Error_Message = 'All Good!';\r\n                            END TRY\r\n                            BEGIN CATCH\r\n                                IF @@TRANCOUNT > 0\r\n                                    ROLLBACK TRANSACTION;\r\n                                SET @Is_All_OK = 0;\r\n                                SET @Error_Message = 'This operation has been unexpectandly terminated due to error: '''\r\n                                    + ERROR_MESSAGE() + ''' at line '\r\n                                    + CAST(ERROR_LINE() AS VARCHAR);\r\n                            END CATCH;\t\r\n                            FETCH NEXT\tFROM db_pkreorgcursor INTO @ID,\r\n                                @Target_DB_Name, @Target_DB_Schema_Name,\r\n                                @Tgt_Object_Name, @Index_Or_PKName;\r\n                        END;\r\n                    CLOSE db_pkreorgcursor;\r\n                    DEALLOCATE db_pkreorgcursor;\t\t\t\t\t\t\t\t\r\n                END; \r\n            END;\r\n\r\n\r\n        IF @Drop_TempTbls = LTRIM(RTRIM(UPPER('YES')))\r\n            BEGIN \r\n                IF EXISTS ( SELECT  *\r\n                            FROM    INFORMATION_SCHEMA.TABLES\r\n                            WHERE   TABLE_NAME = 'Temp_Staging_PKsIndexes_MetaData'\r\n                                    AND TABLE_SCHEMA = 'dbo' )\r\n                    BEGIN\r\n                        DROP TABLE StagingDB.dbo.Temp_Staging_PKsIndexes_MetaData;\r\n                    END;\r\n            END;\r\n    END;\r\nGO\r\n<\/pre>\n
Large\u00a0Tables Acquisition Tasks Overview and Code<\/h3>\n
Most environments can be characterised as having a mixture of large and small tables. Even though a single approach can be employed, sometimes a two-pronged approach can be more beneficial i.e. break up the source schema into groups of small and large tables and handle those two by two separate processes, in this case two distinct stored procedures.<\/p>\n
In case of large tables, with at least hundreds of thousands of records, we can take advantage of a parallel load, spinning up multiple SQL Server Agent jobs responsible for simultaneous reads and inserts. I have written a blog post about this approach previously HERE<\/a>. In case of smaller tables e.g. hundreds to few thousands records, an automated row-for-row MERGE may be a better solution. Let\u2019s explore those two eventualities in more detail.<\/p>\n
When synchronising larger tables, reading and inserting data in a sequential order is a rather slow process. Breaking it up into multiple parallel batches, in my experience increases the speed considerably and unless we have an option to drop the target table and use SELECT\u2026INTO SQL Server 2014 or higher functionality, which executes it in parallel mode by default, spooling up multiple SQL Server Agent jobs may be a good alternative. The code below creates a \u2018master\u2019 and \u2018slave\u2019 stored procedures which achieve just that. The \u2018master\u2019 version queries the source data metadata, looking for the primary key column, using a simple algorithm subdivides or partitions the primary key field into a number of ranges and finally assigns each range into its own SQL Server Agent job, which in turn executes the \u2018slave\u2019 stored procedure. The number of ranges is controlled by the variable assigned from the package directly and the whole process is monitored to ensure that if the job does not finish within an allocated time, it is terminated. The second \u2018slave\u2019 stored procedure reconciles any reserved words that may exist across the schemas and further breaks up the batches into smaller, more manageable INSERT statements.<\/p>\n
Looking at the \u2018master\u2019 stored procedure, you may notice a few interesting things about it. Firstly, it uses ControlDB tables to validate which objects are to be queried and replicated across the source and target database instances. This allows for easy access and control of any tables we may wish to exclude from the job, without having to hardcode their names directly into the procedure itself. Secondly, it uses as simple algorithm which subdivides the primary key field into smaller, manageable \u2018buckets\u2018 of data, each handled by its own SQL Agent process. This allows for much faster source reads and target writes and an additional level of customisation since the number of SQL Agent jobs is driven by the variable value. Another interesting feature is the provision of a simple termination endpoint for the jobs which were supposed to be executed a predefined number of times but for some reason failed to transfer the data and went beyond the threshold allocated. This is achieved by querying \u2018sysjobs_view\u2019 and \u2018sysjobactivity\u2019 system objects every 10 seconds to validate execution status. In the event the jobs are still active and the iteration number has been exceeded, the jobs are terminated using \u2018sp_stop_jobs\u2019 system stored procedure. Finally, I added an error capturing code to allow execution issues to be logged in the AdminDBA database (see Part 1 or a full write-up HERE<\/a>) and reference to the view storing MySQL reserved words to reconcile any naming conventions incompatibility (\u2018slave\u2019 stored procedure). This makes meaningful notifications possible and allows for any naming inconstancies across the two vendors to be rectified at runtime.<\/p>\n
The code for creating \u2018master\u2019 and \u2018slave\u2019 stored procedures, responsible for loading larger tables, is as per below.<\/p>\n
\r\n\/*=========================================================================\r\nCreate 'MASTER' usp_runRemoteSvrDBSchemaSyncBigTablesMaster stored \r\nprocedure used to truncate and re-populate 'large' target staging tables.  \r\n=========================================================================*\/\r\n\r\nUSE StagingDB\r\nGO\r\n\r\nCREATE  PROCEDURE [dbo].[usp_runRemoteSvrDBSchemaSyncBigTablesMaster]\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n(@Remote_Server_Name\t\t\t\tSYSNAME ,\r\n@Remote_Server_DB_Name\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Remote_Server_DB_Schema_Name\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Name\t\t\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Schema_Name\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Object_Name\t\t\t\tVARCHAR\t\t\t\t(256) ,\r\n@Exec_Instance_GUID\t\t\t\t\tUNIQUEIDENTIFIER\t\t  ,\r\n@Package_Name\t\t\t\t\t\tVARCHAR\t\t\t\t(256) ,\r\n@Proc_Name\t\t\t\t\t\t\tVARCHAR\t\t\t\t(256) ,\r\n@Proc_Exec_No\t\t\t\t\t\tVARCHAR\t\t\t\t(10) ,\r\n@Iterations_No\t\t\t\t\t\tVARCHAR\t\t\t\t(10)) \t\t\t\t\t\t\t\t\t\t\t\r\nAS\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\tBEGIN\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\tSET NOCOUNT ON\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n        DECLARE\t\t@SQL\t\t\t\t\tNVARCHAR(MAX)\r\n\t\tDECLARE\t\t@Error_Message\t\t\tVARCHAR (4000)\r\n\t\tDECLARE\t\t@PK_Col_Name\t\t\tVARCHAR (256)\r\n\t\tDECLARE\t\t@Is_Debug_Mode\t\t\tINT\t\t\t\t\t\t= 1\r\n\t\tDECLARE\t\t@Check_Count\t\t\tTINYINT\t\t\t\t\t= 1\r\n\t\tDECLARE\t\t@Max_Check_Count\t\tTINYINT\t\t\t\t\t= 250\r\n\t\tDECLARE \t@Remote_DB_Object_Name\tVARCHAR (128)\t\t\t= @Target_DB_Object_Name\r\n\r\n\tIF OBJECT_ID('tempdb..#Objects_List') IS NOT NULL\r\n        BEGIN\r\n            DROP TABLE [#Objects_List]\r\n        END  \r\n    CREATE TABLE #Objects_List\r\n        (\r\n          DatabaseName sysname ,\r\n          SchemaName sysname ,\r\n          ObjectName sysname ,\r\n\t\t  Is_Source_Target VARCHAR (56)\r\n        )\r\n \r\n    SET @SQL = 'SELECT table_catalog, table_schema, table_name, ''Target'' as Is_Source_Target\r\n\t\t\t\tFROM INFORMATION_SCHEMA.tables \r\n\t\t\t\tWHERE table_type = ''base table'' \r\n\t\t\t\tand table_catalog = '''+@Target_DB_Name+'''\r\n\t\t\t\tand table_schema = '''+@Target_DB_Schema_Name+'''\r\n\t\t\t\tand table_name = '''+@Target_DB_Object_Name+''''\t\r\n\tINSERT  INTO #Objects_List (DatabaseName, SchemaName, ObjectName, Is_Source_Target)\r\n\tEXEC (@SQL)\r\n\t\r\n\tIF @Is_Debug_Mode = 0\r\n\t\tBEGIN\r\n\t\t\tPRINT 'SQL statement for acquiring ''target'' table metadata into #Objects_List temp table:'\r\n\t\t\tPRINT '------------------------------------------------------------------------------------'\r\n\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\tSELECT * FROM #Objects_List \r\n\t\tEND\r\n\t\t\t\t\r\n\tSET @SQL =\t\t'INSERT  INTO #Objects_List (DatabaseName, SchemaName, ObjectName, Is_Source_Target)\r\n\t\t\t\t\tSELECT table_schema, table_schema, table_name, ''Source'' as Is_Source_Target\r\n\t\t\t\t\tFROM OPENQUERY ('+@Remote_Server_Name+', ''select table_schema as DatabaseName, table_schema as SchemaName, table_schema, table_name\r\n\t\t\t\t\tfrom information_schema.tables \r\n\t\t\t\t\tWHERE table_type = ''''base table'''' \r\n\t\t\t\t\tand table_name = '''''+@Remote_DB_Object_Name+'''''\r\n\t\t\t\t\tand table_schema ='''''+@Remote_Server_DB_Name+''''''')'\r\n\r\n\tINSERT  INTO #Objects_List (DatabaseName, SchemaName, ObjectName, Is_Source_Target)\r\n\tEXEC (@SQL)\r\n\tIF @Is_Debug_Mode = 1\r\n\t\tBEGIN\r\n\t\t\tPRINT 'SQL statement for acquiring ''source'' table metadata into #Objects_List temp table:'\r\n\t\t\tPRINT '------------------------------------------------------------------------------------'\r\n\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\tSELECT * FROM #Objects_List WHERE Is_Source_Target = 'Source'\r\n\t\tEND\t\r\n\r\n\r\n\r\n\tIF @Is_Debug_Mode = 1\r\n\t\tBEGIN\r\n\t\t\tSELECT\tSource_Server_Name\t\t= @Remote_Server_Name,\r\n\t\t\t\t\tSource_Server_DB_Name\t= @Remote_Server_DB_Name,\r\n\t\t\t\t\tSource_Object_Name\t\t= @Remote_DB_Object_Name,\r\n\t\t\t\t\tTarget_DB_Name\t\t\t= @Target_DB_Name,\r\n\t\t\t\t\tTarget_DB_Schema_Name\t= @Target_DB_Schema_Name,\r\n\t\t\t\t\tTarget_DB_Object_Name\t= @Target_DB_Object_Name\r\n\t\tEND\r\n    IF NOT EXISTS ( SELECT  TOP 1 1\r\n                    FROM    #Objects_List a\r\n                    WHERE   a.DatabaseName = @Remote_Server_DB_Name AND a.Is_Source_Target = 'Source' )\r\n        BEGIN\r\n            SET @Error_Message = 'Source database cannot be found. You nominated "'\r\n                + @Remote_Server_DB_Name + '". \r\n                Check that the database of that name exists on the instance'\r\n            RAISERROR (\r\n        @Error_Message  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\n            RETURN\r\n        END   \r\n \r\n    IF NOT EXISTS ( SELECT  1\r\n                    FROM    #Objects_List a\r\n                    WHERE   a.DatabaseName = @Target_DB_Name AND a.Is_Source_Target = 'Target')\r\n        BEGIN\r\n            SET @Error_Message = 'Target database cannot be found. You nominated "'\r\n                + @Target_DB_Name + '". \r\n                Check that the database of that name exists on the instance'\r\n            RAISERROR (\r\n        @Error_Message  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\n            RETURN\r\n        END   \r\n \r\n    IF NOT EXISTS ( SELECT TOP 1 1\r\n                    FROM    #Objects_List a\r\n                    WHERE   a.SchemaName = @Remote_Server_DB_Schema_Name AND  a.Is_Source_Target = 'Source' )\r\n        BEGIN\r\n            SET @Error_Message = 'Source schema cannot be found. You nominated "'\r\n                + @Remote_Server_DB_Schema_Name + '". \r\n                Check that the schema of that name exists on the database'\r\n            RAISERROR (\r\n        @Error_Message  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\n        END  \r\n \r\n    IF NOT EXISTS ( SELECT  TOP 1 1\r\n                    FROM    #Objects_List a\r\n                    WHERE   a.SchemaName = @Target_DB_Schema_Name AND a.Is_Source_Target = 'Target' )\r\n        BEGIN\r\n            SET @Error_Message = 'Target schema cannot be found. You nominated "'\r\n                + @Target_DB_Schema_Name + '". \r\n                Check that the schema of that name exists on the database'\r\n            RAISERROR (\r\n        @Error_Message  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\n            RETURN\r\n        END \r\n \r\n    IF NOT EXISTS ( SELECT TOP 1 1\r\n                    FROM    #Objects_List a\r\n                    WHERE   a.ObjectName = @Remote_DB_Object_Name AND a.Is_Source_Target = 'Source')\r\n        BEGIN\r\n            SET @Error_Message = 'Source object cannot be found. You nominated "'\r\n                + @Remote_DB_Object_Name + '". \r\n                Check that the object of that name exists on the database'\r\n            RAISERROR (\r\n        @Error_Message  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\n            RETURN\r\n        END\r\n \r\n    IF NOT EXISTS ( SELECT  1\r\n                    FROM    #Objects_List a\r\n                    WHERE   a.ObjectName = @Target_DB_Object_Name AND a.Is_Source_Target = 'Target')\r\n        BEGIN\r\n            SET @Error_Message = 'Target object cannot be found. You nominated "'\r\n                + @Target_DB_Object_Name + '". \r\n                Check that the object of that name exists on the database'\r\n            RAISERROR (\r\n        @Error_Message  -- Message text.\r\n        ,16 -- Severity.\r\n            ,1 -- State.\r\n  )\r\n            RETURN\r\n        END\r\n\t\t\t\t\r\n\r\n        IF OBJECT_ID('tempdb..#Temp_Tbl_Metadata') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #Temp_Tbl_Metadata;\r\n            END; \r\n        CREATE TABLE #Temp_Tbl_Metadata\r\n            (\r\n              ID SMALLINT IDENTITY(1, 1) ,\r\n\t\t\t  Table_Name VARCHAR (256) ,\r\n              Column_Name VARCHAR(128) ,\r\n              Column_DataType VARCHAR(56) ,\r\n              Is_PK_Flag BIT\r\n            );\r\n\r\n\r\n        IF OBJECT_ID('tempdb..#Ids_Range') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #Ids_Range;\r\n            END; \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n        CREATE TABLE #Ids_Range\r\n            (\r\n              id SMALLINT IDENTITY(1, 1) ,\r\n              range_FROM BIGINT ,\r\n              range_TO BIGINT\r\n            );\t\r\n\t\t\t\r\n\t\t\t\tSET @SQL =\t\t\t\t'INSERT INTO #Temp_Tbl_Metadata (Table_Name, Column_Name, Column_DataType, Is_PK_Flag)'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'SELECT a.table_name, a.column_name, a.data_type, CASE WHEN a.column_key = ''PRI'' '\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'THEN 1 ELSE 0 END FROM OPENQUERY('+@Remote_Server_Name+', '\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'''SELECT table_name, table_schema, column_name, data_type, column_key from information_schema.columns'\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'WHERE table_name = '''''+@Target_DB_Object_Name+''''''') a '\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'JOIN ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process b '\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'ON a.Table_Name\t\t= b.Remote_Table_Name and'\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'a.Table_Schema\t\t\t= '''+@Remote_Server_DB_Schema_Name+''' and '\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'b.Remote_Schema_Name\t= '''+@Remote_Server_DB_Schema_Name+''' and '\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'b.Remote_DB_Name\t\t= '''+@Remote_Server_DB_Name+''' and'\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'b.Remote_Server_Name\t= '''+@Remote_Server_Name+''''\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'WHERE b.Is_Active = 1'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'AND NOT EXISTS (SELECT 1 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'FROM  ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process_ColumnExceptions o'\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'WHERE o.Is_Active = 1 AND '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Field_Name\t\t= a.Column_Name AND '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Table_Name\t\t= a.Table_Name AND '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Schema_Name\t\t= '''+@Remote_Server_DB_Schema_Name+''' AND '\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_DB_Name\t\t\t= '''+@Remote_Server_DB_Name+''' AND'\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Server_Name\t\t= '''+@Remote_Server_Name+''')'\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tPRINT 'SQL statement for populating ''metadata'' #Temp_Tbl_Metadata temp table:'\r\n\t\t\t\tPRINT '------------------------------------------------------------------------'\r\n\t\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\tEND\r\n\t\t\t\t\r\n\t\tEXEC (@SQL)\r\n\r\n\t\tIF NOT EXISTS ( SELECT  TOP 1 1\r\n                    FROM    #Temp_Tbl_Metadata\r\n                    WHERE   Is_PK_Flag = 1 AND ISNUMERIC(Is_PK_Flag)=1)\r\n        BEGIN\r\n            SET @Error_Message = 'Primary key column in "Temp_Tbl_Metadata" table not found. Please troubleshoot!'\r\n            RAISERROR\t(\r\n        @Error_Message\t-- Message text\r\n        ,16\t\t\t-- Severity\r\n        ,1\t\t\t-- State\r\n\t\t\t\t\t\t)\r\n            RETURN\r\n        END\r\n\r\n\r\n\t\tSET @PK_Col_Name  = (\tSELECT TOP 1 Column_Name \r\n\t\t\t\t\t\t\t\t\t\t\t\tFROM #Temp_Tbl_Metadata \r\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE Is_PK_Flag = 1 \r\n\t\t\t\t\t\t\t\t\t\t\t\tAND Column_DataType IN ('INT', 'SMALLINT', 'BIGINT', 'TINYINT'))\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\tSET @SQL =\t\t\t\t'DECLARE @R1 INT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'''SELECT MIN('+@PK_Col_Name+') as id from '+@Target_DB_Object_Name+''')) '\t\t\t\t\t\t+CHAR(13) \r\n\t\tSET @SQL = @SQL +\t\t'DECLARE @R2 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'''SELECT (MAX('+@PK_Col_Name+')-MIN('+@PK_Col_Name+')+1)\/'+@Proc_Exec_No+' as id FROM'\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t''+@Target_DB_Object_Name+'''))'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'DECLARE @R3 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'''SELECT MAX('+@PK_Col_Name+') as id from '+@Target_DB_Object_Name+''')) '\t\t\t\t\t\t+CHAR(13) \r\n\t\tSET @SQL = @SQL +\t\t'DECLARE @t int = @r2+@r2+2 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'INSERT INTO #Ids_Range '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'(range_FROM, range_to) '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'SELECT @R1, @R2 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'UNION ALL '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'SELECT @R2+1, @R2+@R2+1 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'WHILE @t <= @r3 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'BEGIN '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'INSERT INTO #Ids_Range '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'(range_FROM, range_to) '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'SELECT @t, CASE WHEN (@t+@r2) >= @r3 THEN @r3 ELSE @t+@r2 END '\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'SET @t = @t+@r2+1 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'END'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tPRINT 'SQL statement for populating ''range'' variables and #Ids_Range temp table:'\r\n\t\t\t\tPRINT '---------------------------------------------------------------------------'\r\n\t\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\tEND\r\n\t\t\t\t\r\n\t\tEXEC(@SQL)\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tSELECT * FROM #Ids_Range\r\n\t\t\tEND\r\n\r\n\t\tSET @SQL =\t\t\t'IF EXISTS (SELECT TOP 1 1 FROM '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t''+@Target_DB_Name+'.'+@Target_DB_Schema_Name+'.'+@Target_DB_Object_Name+''\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t'WHERE '+@PK_Col_Name+' IS NOT NULL) BEGIN '\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t'TRUNCATE TABLE '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t''+@Target_DB_Name+'.'+@Target_DB_Schema_Name+'.'+@Target_DB_Object_Name+' END'\t\t\t+CHAR(13)\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tPRINT 'SQL statement for truncating '''+@Target_DB_Object_Name+''' table:'\r\n\t\t\t\tPRINT '------------------------------------------------------------------'\r\n\t\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2)\r\n\t\t\tEND\r\n\t\t\t\t\r\n\t\tEXEC(@SQL)\r\n\r\n\t\tIF OBJECT_ID('tempdb..#Temp_Tbl_AgentJob_Stats') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #Temp_Tbl_AgentJob_Stats;\r\n            END; \r\n        CREATE TABLE #Temp_Tbl_AgentJob_Stats\r\n            (\r\n              ID SMALLINT IDENTITY(1, 1) ,\r\n              Job_Name VARCHAR(256) ,\r\n              Job_Exec_Start_Date DATETIME\r\n            );\r\n\r\n\t\tIF CURSOR_STATUS('global', 'sp_cursor') >= -1\r\n                    BEGIN\r\n                        DEALLOCATE sp_cursor\r\n                    END\r\n\t\t\tDECLARE @z INT\r\n\t\t\tDECLARE @err INT\r\n\t\t\tDECLARE sp_cursor CURSOR \r\n\t\t\tFOR\r\n\t\t\tSELECT id FROM #ids_range\r\n\t\t\tSELECT  @err = @@error\r\n                IF @err <> 0\r\n                    BEGIN\r\n                        DEALLOCATE sp_cursor\r\n\t\t\t\t\t\tRETURN @err\r\n                    END\r\n\t\t\tOPEN sp_cursor\r\n\t\t\tFETCH NEXT\r\n\t\t\tFROM sp_cursor INTO @z\r\n\t\t\tWHILE @@FETCH_STATUS = 0\r\n\t\t\t\tBEGIN\r\n\t\t\t\t\tDECLARE \r\n\t\t\t\t\t@range_from\t\tVARCHAR(10)\t\t= (SELECT CAST(range_FROM AS VARCHAR(10)) FROM #ids_range where id = @z),\r\n\t\t\t\t\t@range_to\t\tVARCHAR(10)\t\t= (SELECT CAST(range_TO AS VARCHAR(10)) FROM #ids_range where id = @z),\r\n\t\t\t\t\t@job_name\t\tVARCHAR (256)\t= 'Temp_'+UPPER(LEFT(@Target_DB_Object_Name,1))+LOWER(SUBSTRING(@Target_DB_Object_Name,2,LEN(@Target_DB_Object_Name)))+'_TableSync_'+'AsyncJob'+'_'+CAST(@z AS VARCHAR (20)),\r\n\t\t\t\t\t@job_owner\t\tVARCHAR (256)\t= 'sa'\r\n\t\t\t\t\tDECLARE\r\n\t\t\t\t\t@delete_job_sql VARCHAR (400)\t= 'EXEC msdb..sp_delete_job @job_name='''''+@job_name+''''''\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\tDECLARE\r\n\t\t\t\t\t@sql_job\t\tNVARCHAR(MAX)\t=\t\t\r\n\t\t\t\t\t'USE [StagingDB]\r\n\t\t\t\t\tEXEC\t[dbo].['+@Proc_Name+']\r\n\t\t\t\t\t@Remote_Server_Name = '+@Remote_Server_Name+',\r\n\t\t\t\t\t@Remote_Server_DB_Name = '''''+@Remote_Server_DB_Name+''''',\r\n\t\t\t\t\t@Remote_Server_DB_Schema_Name = '''''+@Remote_Server_DB_Schema_Name+''''',\r\n\t\t\t\t\t@Target_DB_Name = N'''''+@Target_DB_Name+''''',\r\n\t\t\t\t\t@Target_DB_Schema_Name = '''''+@Target_DB_Schema_Name+''''',\r\n\t\t\t\t\t@Target_DB_Object_Name = '''''+@Target_DB_Object_Name+''''',\r\n\t\t\t\t\t@Iterations_No\t='''''+@Iterations_No+''''' ,\r\n\t\t\t\t\t@Min_Value\t='''''+@range_from+''''',\r\n\t\t\t\t\t@Max_Value\t='''''+@range_to+''''',\r\n\t\t\t\t\t@Exec_Instance_GUID\t='''''+CAST(@Exec_Instance_GUID AS VARCHAR(128))+''''',\r\n\t\t\t\t\t@Package_Name='''''+@Package_Name+''''' '\r\n\t\t\t\t\t\r\n\t\t\t\t\tSET @SQL =\t\t\t'IF EXISTS'\r\n\t\t\t\t\tSET @SQL = @SQL +\t'(SELECT TOP 1 1 FROM msdb..sysjobs_view job JOIN msdb.dbo.sysjobactivity activity'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'ON job.job_id = activity.job_id WHERE job.name = N'''+@job_name+''''\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'AND activity.start_execution_date IS NOT NULL AND activity.stop_execution_date IS NULL)'\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'BEGIN'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_stop_job @job_name=N'''+@job_name+''';'\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_delete_job @job_name=N'''+@job_name+''', @delete_unused_schedule=1'\t\t\t\t\t+CHAR(13)\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\tSET @SQL = @SQL +\t'END'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'IF EXISTS'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'(SELECT TOP 1 1 FROM msdb..sysjobs_view job JOIN msdb.dbo.sysjobactivity activity'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'ON job.job_id = activity.job_id WHERE job.name = N'''+@job_name+''''\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'AND activity.start_execution_date IS NULL AND activity.stop_execution_date IS NOT NULL)'\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'BEGIN'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_delete_job @job_name=N'''+@job_name+''', @delete_unused_schedule=1'\t\t\t\t\t+CHAR(13)\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\tSET @SQL = @SQL +\t'END'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_job '''+@job_name+''', @owner_login_name= '''+@job_owner+''';'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_jobserver @job_name= '''+@job_name+''';'\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\t\t\t\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_jobstep @job_name='''+@job_name+''', @step_name= ''Step1'', '\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'@command = '''+@sql_job+''', @database_name = '''+@Target_DB_Name+''', @on_success_action = 3;'\t+CHAR(13)\t\t\t\t\t\t\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_add_jobstep @job_name = '''+@job_name+''', @step_name= ''Step2'','\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +   '@command = '''+@delete_job_sql+''''\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_start_job @job_name= '''+@job_name+''''\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+REPLICATE(CHAR(13),4)\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t                \r\n\t\t\t\t\tEXEC (@SQL)\r\n\r\n\t\t\t\t\tWAITFOR DELAY '00:00:01'\t\t\t\t\t\r\n\r\n\t\t\t\t\tINSERT INTO #Temp_Tbl_AgentJob_Stats\r\n\t\t\t\t\t(Job_Name, Job_Exec_Start_Date)\r\n\t\t\t\t\tSELECT job.Name, activity.start_execution_date\r\n\t\t\t\t\tFROM msdb.dbo.sysjobs_view job \r\n\t\t\t\t\tINNER JOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\t\tWHERE job.name = @job_name    \r\n\r\n\t\t\t\t\t\r\n\t\t\t\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\t\tPRINT 'SQL Server agent job execution and deletion SQL statement for job '''+@job_name+''':'\r\n\t\t\t\t\t\t\tPRINT '------------------------------------------------------------------------------------------------------'\r\n\t\t\t\t\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\t\t\t\tEND\r\n\t\t\t\t\t\t\t\t\t\t                  \r\n                    FETCH NEXT\r\n\t\t\t\t\tFROM sp_cursor INTO @z\r\n\t\t\t\tEND\r\n\t\t\tCLOSE sp_cursor\r\n\t\t\tDEALLOCATE sp_cursor \r\n\r\n\t\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\t\tBEGIN\r\n\t\t\t\t\tSELECT * FROM #Temp_Tbl_AgentJob_Stats\r\n\t\t\t\tEND\r\n\r\n\t\t\r\n\t\tIF @Is_Debug_Mode = 1\r\n            BEGIN\r\n                PRINT 'Iterating through agent job(s) execution status for '''+@Target_DB_Object_Name+''' table:'\r\n\t\t\t\tPRINT '----------------------------------------------------------------------------------------' +REPLICATE(CHAR(13),2)\r\n            END\r\n        Start:\t\t\t\r\n\t\tIF EXISTS (\tSELECT TOP 1 1 \r\n\t\t\t\t\tFROM msdb.dbo.sysjobs_view job \r\n\t\t\t\t\tJOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\t\tJOIN #Temp_Tbl_AgentJob_Stats agent \r\n\t\t\t\t\tON agent.Job_Name = job.name)\r\n\t\t\tAND @Check_Count <= @Max_Check_Count\r\n\t\t\tBEGIN \r\n\t\t\t\t\tIF @Is_Debug_Mode = 1\r\n\t\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\t\tSELECT agent.id, job.*\r\n\t\t\t\t\t\t\tFROM msdb.dbo.sysjobs_view job \r\n\t\t\t\t\t\t\tJOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\t\t\t\tJOIN #Temp_Tbl_AgentJob_Stats agent \r\n\t\t\t\t\t\t\tON agent.Job_Name = job.name\r\n\t\t\t\t\t\t\tORDER BY agent.id ASC\r\n\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\tDECLARE @Running_Jobs VARCHAR(2000) = NULL\r\n\t\t\t\t\t\t\tSELECT @Running_Jobs = COALESCE(@Running_Jobs + CHAR(13), '')  + CAST(id AS VARCHAR) +') ' + job_name\r\n\t\t\t\t\t\t\tFROM\r\n\t\t\t\t\t\t\t(SELECT agent.id, agent.Job_Name\r\n\t\t\t\t\t\t\tFROM msdb.dbo.sysjobs_view job \r\n\t\t\t\t\t\t\tJOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\t\t\t\tJOIN #Temp_Tbl_AgentJob_Stats agent \r\n\t\t\t\t\t\t\tON agent.Job_Name = job.name) a\r\n\t\t\t\t\t\t\tORDER BY a.id\r\n\r\n\t\t\t\t\t\t\tPRINT '--> Status:'\r\n\t\t\t\t\t\t\tPRINT 'Iteration number: '+CAST(@Check_Count AS VARCHAR(10))+' out of '+CAST(@Max_Check_Count AS VARCHAR(10))+''\t\r\n\t\t\t\t\t\t\tPRINT 'Remaining jobs currently executing are:'\r\n\t\t\t\t\t\t\tPRINT ''+@Running_Jobs+''\t\t\t\t\t\t\t\t+CHAR(13)\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\tPRINT 'Waiting for 10 seconds before next attempt...'\t+REPLICATE(CHAR(13),2)\t\t\t\t\t\t\t\t\r\n\t\t\t\t\tEND\t\t\t\t\t\t\t\t\t \t\t\t\t\t\t\t\r\n\t\t\t\t\tSET @Check_Count = @Check_Count + 1;\r\n\t\t\t\t\tWAITFOR DELAY '00:00:10';\t\t\t\t\r\n\t\t\t\t\tGOTO Start\t\t\t\t\t\t\r\n\t\t\tEND\r\n\r\n\t\tIF EXISTS (\tSELECT TOP 1 1 \r\n\t\t\t\t\tFROM msdb.dbo.sysjobs_view job \r\n\t\t\t\t\tJOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\t\tJOIN #Temp_Tbl_AgentJob_Stats agent \r\n\t\t\t\t\tON agent.Job_Name = job.name)\r\n\t\t\t\t\tAND @Check_Count > @Max_Check_Count\r\n            BEGIN \r\n\t\t\t\tDECLARE @i\t\t\tTINYINT\r\n\t\t\t\tDECLARE @Failed_Job VARCHAR (256)\r\n\t\t\t\t  \t\t\t\t\r\n\t\t\t\tIF CURSOR_STATUS('global', 'sp_killjob') >= -1\r\n                    BEGIN\r\n                        DEALLOCATE sp_killjob\r\n                    END\t\t\t\t\t\r\n\r\n\t\t\t\t\tDECLARE sp_killjob\tCURSOR LOCAL FORWARD_ONLY\r\n\t\t\t\t\t\tFOR\r\n\t\t\t\t\t\t\tSELECT agent.ID, agent.Job_Name\r\n\t\t\t\t\t\t\tFROM msdb.dbo.sysjobs_view job \r\n\t\t\t\t\t\t\tJOIN msdb.dbo.sysjobactivity activity\r\n\t\t\t\t\t\t\tON job.job_id = activity.job_id\r\n\t\t\t\t\t\t\tJOIN #Temp_Tbl_AgentJob_Stats agent \r\n\t\t\t\t\t\t\tON agent.Job_Name = job.name\r\n\r\n\t\t\t\t\t\t\tOPEN sp_killjob\r\n\t\t\t\t\t\t\tFETCH NEXT\r\n\t\t\t\t\t\t\tFROM sp_killjob INTO @i, @Failed_Job\r\n\t\t\t\t\t\t\tWHILE @@FETCH_STATUS = 0\r\n\t\t\t\t\t\t\t\tBEGIN  \r\n\t\t\t\t\t\t\t\t\tSET @SQL =\t\t\t'EXEC msdb..sp_stop_job @job_name=N'''+@Failed_Job+''''  \r\n\t\t\t\t\t\t\t\t\tSET @SQL = @SQL +\t'EXEC msdb..sp_delete_job @job_name=N'''+@Failed_Job+''', '\r\n\t\t\t\t\t\t\t\t\tSET @SQL = @SQL +\t'@delete_unused_schedule=1'\r\n\t\t\t\t\t\t\t\t\tIF @Is_Debug_Mode = 1\r\n\t\t\t\t\t\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\t\t\t\t\t\tPRINT 'Error encountered! Stoping and deleting failed job '''+@Failed_Job+''''\t\t\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\t\t\t\t\t\tEND \r\n\t\t\t\t\t\t\t\t\tEXEC (@SQL)   \r\n\t\t\t\t\t\t\t\t\tFETCH NEXT FROM sp_killjob INTO @i, @Failed_Job   \r\n\t\t\t\t\t\t\t\tEND\t\t\t\t\t\t\t\t\t\r\n\t\t\t\t\tCLOSE sp_killjob\r\n\t\t\t\t\tDEALLOCATE sp_killjob\r\n\t\t\tEND\r\n\r\n\tIF OBJECT_ID('tempdb..#Objects_List') IS NOT NULL\r\n\t\t\tBEGIN\r\n\t\t\t\tDROP TABLE [#Objects_List]\r\n\t\t\tEND\r\n\tIF OBJECT_ID('tempdb..#Temp_Tbl_Metadata') IS NOT NULL\r\n\t\t\t\tBEGIN\r\n\t\t\t\t\tDROP TABLE #Temp_Tbl_Metadata;\r\n\t\t\t\tEND; \r\n\tIF OBJECT_ID('tempdb..#Ids_Range') IS NOT NULL\r\n\t\t\t\tBEGIN\r\n\t\t\t\t\tDROP TABLE #Ids_Range;\r\n\t\t\t\tEND;\r\nEND\r\nGO\r\n<\/pre>\n
\r\n\/*=========================================================================\r\nCreate 'SLAVE' usp_runRemoteSvrDBSchemaSyncBigTablesSlave stored \r\nprocedure used to re-populate 'small' target staging tables via \r\na dynamic MERGE SQL functionality.  \r\n=========================================================================*\/\r\n\r\nUSE [StagingDB]\r\nGO\r\n\r\nCREATE PROCEDURE [dbo].[usp_runRemoteSvrDBSchemaSyncBigTablesSlave]\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n(@Remote_Server_Name\t\t\t\tSYSNAME\t\t\t\t\t  ,\r\n@Remote_Server_DB_Name\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Remote_Server_DB_Schema_Name\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Name\t\t\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Schema_Name\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Target_DB_Object_Name\t\t\t\tVARCHAR\t\t\t\t(256) ,\r\n@Iterations_No\t\t\t\t\t\tVARCHAR\t\t\t\t(10)  ,\r\n@Min_Value\t\t\t\t\t\t\tVARCHAR\t\t\t\t(20)  ,\r\n@Max_Value\t\t\t\t\t\t\tVARCHAR\t\t\t\t(20)  ,\r\n@Exec_Instance_GUID\t\t\t\t\tVARCHAR\t\t\t\t(128) ,\r\n@Package_Name\t\t\t\t\t\tVARCHAR\t\t\t\t(256))\t\t\t\t\t\t\t\t\t\t\t\r\nAS\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\tBEGIN\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\tSET NOCOUNT ON\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n        DECLARE @SQL\t\t\t\t\t\t\tNVARCHAR(MAX)\r\n\t\tDECLARE @Err_Msg\t\t\t\t\t\tVARCHAR (4000)\r\n\t\tDECLARE @PK_Col_Name\t\t\t\t\tVARCHAR (256)\r\n\t\tDECLARE @Remote_DB_Object_Name\t\t\tVARCHAR (128)\t= @Target_DB_Object_Name\r\n\t\tDECLARE @Is_Debug_Mode\t\t\t\t\tBIT\t\t\t\t= 1\r\n\t\t\r\n\t\tSET @Exec_Instance_GUID = REPLACE(@Exec_Instance_GUID, '-', '')\t\t\r\n\r\n        IF OBJECT_ID('tempdb..#Temp_Tbl_Metadata') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #Temp_Tbl_Metadata;\r\n            END; \r\n        CREATE TABLE #Temp_Tbl_Metadata\r\n            (\r\n              ID SMALLINT IDENTITY(1, 1) ,\r\n\t\t\t  Table_Name VARCHAR (128) ,\r\n              Column_Name VARCHAR(128) ,\r\n              Column_DataType VARCHAR(56) ,\r\n              Is_PK_Flag BIT\r\n            );\r\n\t\t\t\r\n\r\n\t\t\t\tSET @SQL =\t\t\t\t'INSERT INTO #Temp_Tbl_Metadata (Table_Name, Column_Name, Column_DataType, Is_PK_Flag)'\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'SELECT a.table_name, a.column_name, a.data_type, CASE WHEN a.column_key = ''PRI'' '\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'THEN 1 ELSE 0 END FROM OPENQUERY('+@Remote_Server_Name+', '\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'''SELECT table_name, table_schema, column_name, data_type, column_key from information_schema.columns'\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'WHERE table_name = '''''+@Target_DB_Object_Name+''''''') a '\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'JOIN ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process b '\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'ON a.Table_Name\t\t= b.Remote_Table_Name and'\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'a.Table_Schema\t\t\t= '''+@Remote_Server_DB_Schema_Name+''' and '\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'b.Remote_Schema_Name\t= '''+@Remote_Server_DB_Schema_Name+''' and '\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'b.Remote_DB_Name\t\t= '''+@Remote_Server_DB_Name+''' and'\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'b.Remote_Server_Name\t= '''+@Remote_Server_Name+''''\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'WHERE b.Is_Active = 1'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'AND NOT EXISTS (SELECT 1 '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'FROM  ControlDB.dbo.Ctrl_RemoteSvrs_Tables2Process_ColumnExceptions o'\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'WHERE o.Is_Active = 1 AND '\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Field_Name\t\t= a.Column_Name AND '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Table_Name\t\t= a.Table_Name AND '\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Schema_Name\t\t= '''+@Remote_Server_DB_Schema_Name+''' AND '\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_DB_Name\t\t\t= '''+@Remote_Server_DB_Name+''' AND'\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\tSET @SQL = @SQL +\t\t'o.Remote_Server_Name\t\t= '''+@Remote_Server_Name+''')'\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tPRINT 'SQL statement for populating ''Temp_Tbl_Metadata'' temp table:'\r\n\t\t\t\tPRINT '---------------------------------------------------------------------------'\r\n\t\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\tEND\r\n\r\n\t\tEXEC (@SQL)\r\n\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tSELECT * FROM #Temp_Tbl_Metadata\r\n\t\t\tEND\r\n\r\n\t\tIF NOT EXISTS ( SELECT  TOP 1 1\r\n                    FROM    #Temp_Tbl_Metadata\r\n                    WHERE   Is_PK_Flag = 1 AND ISNUMERIC(Is_PK_Flag)=1)\r\n        BEGIN\r\n            SET @Err_Msg = 'Primary key column "Temp_Tbl_Metadata" table not found. Please troubleshoot!'\r\n            RAISERROR (\r\n        @Err_Msg\t-- Message text\r\n        ,16\t\t\t-- Severity\r\n        ,1\t\t\t-- State\r\n\t\t\t\t\t)\r\n            RETURN\r\n        END\r\n\r\n\t\t\r\n        IF OBJECT_ID('tempdb..#Ids_Range') IS NOT NULL\r\n            BEGIN\r\n                DROP TABLE #Ids_Range;\r\n            END; \t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n        CREATE TABLE #Ids_Range\r\n            (\r\n              id SMALLINT IDENTITY(1, 1) ,\r\n              range_FROM BIGINT ,\r\n              range_TO BIGINT\r\n            );\t\r\n\r\n\t\tSET @PK_Col_Name  = (\tSELECT TOP 1 Column_Name \r\n\t\t\t\t\t\t\t\t\t\t\t\tFROM #Temp_Tbl_Metadata \r\n\t\t\t\t\t\t\t\t\t\t\t\tWHERE Is_PK_Flag = 1 \r\n\t\t\t\t\t\t\t\t\t\t\t\tAND Column_DataType IN ('INT', 'SMALLINT', 'TINYINT', 'BIGINT'))\r\n\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\r\n\t\tSET @SQL =\t\t\t\t'DECLARE @R1 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'''SELECT MIN('+@PK_Col_Name+') as id from '+@Target_DB_Object_Name+''\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t' WHERE '+@PK_Col_Name+' >= '+@Min_Value+' AND '+@PK_Col_Name+' <= '+@Max_Value+' ''))'\t\t\t+CHAR(13) \r\n\t\tSET @SQL = @SQL +\t\t'DECLARE @R2 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'''SELECT (MAX('+@PK_Col_Name+')-MIN('+@PK_Col_Name+')+1)\/'+@Iterations_No+' as id FROM'\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t''+@Target_DB_Object_Name+''\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t' WHERE '+@PK_Col_Name+' >= '+@Min_Value+' AND '+@PK_Col_Name+' <= '+@Max_Value+' ''))'\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'DECLARE @R3 BIGINT = (SELECT id FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'''SELECT MAX('+@PK_Col_Name+') as id from '+@Target_DB_Object_Name+''\t\t\t\t\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t' WHERE '+@PK_Col_Name+' >= '+@Min_Value+' AND '+@PK_Col_Name+' <= '+@Max_Value+' ''))'\t\t\t+CHAR(13)\r\n\t\tSET @SQL = @SQL +\t\t'DECLARE @t int = @R1+@R2+1\r\n\t\t\t\t\t\t\t\tINSERT INTO #Ids_Range\r\n\t\t\t\t\t\t\t\t(range_FROM, range_to)\r\n\t\t\t\t\t\t\t\tSELECT @R1, @R1+@R2 \r\n\t\t\t\t\t\t\t\tWHILE @t < @r3 \r\n\t\t\t\t\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\t\t\t\t\tINSERT INTO #Ids_Range\r\n\t\t\t\t\t\t\t\t\t\t(range_FROM, range_to)\r\n\t\t\t\t\t\t\t\t\t\tSELECT @t, CASE WHEN (@t+@r2)>@r3 THEN @r3 ELSE @t+@r2 END\r\n\t\t\t\t\t\t\t\t\t\tSET @t = @t+@r2+1\r\n\t\t\t\t\t\t\t\t\tEND'\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tPRINT 'SQL statement for populating ''Ids_Range'' temp table:'\r\n\t\t\t\tPRINT '---------------------------------------------------------------------------'\r\n\t\t\t\tPRINT @SQL+REPLICATE(CHAR(13),2) \r\n\t\t\tEND\r\n\t\t\t\t\t\t\t\t\t\r\n\t\tEXEC (@SQL)\t\r\n\t\t\r\n\t\tIF @Is_Debug_Mode = 1 \r\n\t\t\tBEGIN\r\n\t\t\t\tSELECT * FROM #Ids_Range\r\n\t\t\tEND\t\r\n\r\n\t\tDECLARE @col_list_MSSQL VARCHAR(1000) = (\r\n\t\t\tSELECT \r\n\t\t\tDISTINCT \r\n\t\t\tSTUFF((\r\n\t\t\tSELECT ',' +CHAR(10) + COALESCE(vw.mssql_version, u.column_name)\r\n\t\t\tFROM #Temp_Tbl_Metadata u \r\n\t\t\tLEFT JOIN StagingDB.dbo.vw_MssqlReservedWords vw\r\n\t\t\tON LTRIM(RTRIM(UPPER(u.Column_Name))) = vw.reserved_word\r\n\t\t\tWHERE u.column_name = column_name\r\n\t\t\tORDER BY u.column_name\r\n\t\t\tFOR XML PATH('')\r\n\t\t\t),1,1,'') AS columns_list\r\n\t\t\tFROM #Temp_Tbl_Metadata\r\n\t\t\tGROUP BY column_name)\r\n\r\n\t\tDECLARE @col_list_MYSQL VARCHAR(1000) = (\r\n\t\t\tSELECT \r\n\t\t\tDISTINCT \r\n\t\t\tSTUFF((\r\n\t\t\tSELECT ',' +CHAR(10) + COALESCE(vw.mysql_version, u.column_name)\r\n\t\t\tFROM #Temp_Tbl_Metadata u \r\n\t\t\tLEFT JOIN StagingDB.dbo.vw_MysqlReservedWords vw\r\n\t\t\tON LTRIM(RTRIM(UPPER(u.Column_Name))) = vw.reserved_word\r\n\t\t\tWHERE u.column_name = column_name\r\n\t\t\tORDER BY u.column_name\r\n\t\t\tFOR XML PATH('')\r\n\t\t\t),1,1,'') AS columns_list\r\n\t\t\tFROM #Temp_Tbl_Metadata\r\n\t\t\tGROUP BY column_name)\r\n\r\n\r\n\t\tIF CURSOR_STATUS('global', 'db_cursor') >= -1\r\n                    BEGIN\r\n                        DEALLOCATE db_cursor\r\n                    END\r\n\t\t\tDECLARE @z INT\r\n\t\t\tDECLARE @err INT\r\n\t\t\tDECLARE db_cursor CURSOR LOCAL FORWARD_ONLY\r\n\t\t\tFOR\r\n\t\t\tSELECT id FROM #ids_range\r\n\t\t\tSELECT  @err = @@error\r\n                IF @err <> 0\r\n                    BEGIN\r\n                        DEALLOCATE db_cursor\r\n\t\t\t\t\t\tRETURN @err\r\n                    END\r\n\t\t\tOPEN db_cursor\r\n\t\t\tFETCH NEXT\r\n\t\t\tFROM db_cursor INTO @z\r\n\t\t\tWHILE @@FETCH_STATUS = 0\r\n\t\t\t\tBEGIN\r\n\t\t\t\t\tBEGIN TRY\r\n\t\t\t\t\tBEGIN TRANSACTION\r\n\t\t\t\t\tDECLARE \r\n\t\t\t\t\t@range_from    BIGINT = (SELECT range_FROM FROM #Ids_Range where id = @z),\r\n\t\t\t\t\t@range_to      BIGINT = (SELECT range_TO FROM #Ids_Range where id = @z)\t\t\r\n\r\n\r\n\t\t\t\t\t\r\n\r\n\t\t\t\t\t\t\tSET @SQL =\t\t\t'INSERT INTO '+@Target_DB_Name+'.'+@Target_DB_Schema_Name+'.'+@Target_DB_Object_Name+''\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t' ('+@col_list_MSSQL+')'\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t'SELECT '+@col_list_MSSQL+''\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t'FROM OPENQUERY ('+@Remote_Server_Name+', '\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t'''SELECT '+@col_list_MYSQL+''\t\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t'FROM '+@Target_DB_Object_Name+''\t\t\t\t\t\t\t\t\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t'WHERE '+@PK_Col_Name+' >= '+cast(@range_FROM as varchar (20))+''\t\t\t\t\t\t+CHAR(13)\r\n\t\t\t\t\t\t\tSET @SQL = @SQL +\t'AND '+@PK_Col_Name+' <= '+CAST(@range_to AS VARCHAR(20))+''')'\t\t\t\t\t\t\t+CHAR(13)\r\n\r\n\t\t\t\t\r\n\t\t\t\t\t\r\n\t\t\t\t\tIF @Is_Debug_Mode = 1\r\n\t\t\t\t\t\tBEGIN\r\n\t\t\t\t\t\t\tPRINT 'SQL statement for populating target table '+@Target_DB_Schema_Name+'.'+@Target_DB_Object_Name+'. Iteration no: '+CAST(@z AS VARCHAR(10))\r\n\t\t\t\t\t\t\tPRINT '---------------------------------------------------------------------------------------------------------------------------------------'\r\n\t\t\t\t\t\t\tPRINT @SQL +REPLICATE(CHAR(13),2) \r\n\t\t\t\t\t\tEND\t\r\n\r\n\t\t\t\t\tEXEC (@SQL);\r\n\t\t\t\t\t\t\t\t\t\t                                      \t\t\t\t\t\r\n\t\t\t\t\tCOMMIT TRANSACTION\r\n\t\t\t\t\tFETCH NEXT\r\n\t\t\t\t\tFROM db_cursor INTO @z\r\n\t\t\t\t\tEND TRY\r\n\r\n\t\t\t\t\tBEGIN CATCH\r\n            ROLLBACK TRANSACTION\r\n\t\t\t;WITH    TempErr ( [ErrorNumber], \r\n\t\t\t\t\t\t\t[ErrorSeverity], \r\n\t\t\t\t\t\t\t[ErrorState], \r\n\t\t\t\t\t\t\t[ErrorLine], \r\n\t\t\t\t\t\t\t[ErrorMessage], \r\n\t\t\t\t\t\t\t[ErrorDateTime], \r\n\t\t\t\t\t\t\t[LoginName], \r\n\t\t\t\t\t\t\t[UserName], \r\n\t\t\t\t\t\t\t[PackageName], \r\n\t\t\t\t\t\t\t[ObjectID], \r\n\t\t\t\t\t\t\t[ProcessID], \r\n\t\t\t\t\t\t\t[ExecutionInstanceGUID], \r\n\t\t\t\t\t\t\t[DBName] )\r\n                      AS ( SELECT   ERROR_NUMBER()\t\t\tAS ErrorNumber ,\r\n                                    ERROR_SEVERITY()\t\tAS ErrorSeverity ,\r\n                                    ERROR_STATE()\t\t\tAS ErrorState ,\r\n                                    ERROR_LINE()\t\t\tAS ErrorLine ,\r\n                                    ERROR_MESSAGE()\t\t\tAS ErrorMessage ,\r\n                                    SYSDATETIME()\t\t\tAS ErrorDateTime ,\r\n                                    SYSTEM_USER\t\t\t\tAS LoginName ,\r\n                                    USER_NAME()\t\t\t\tAS UserName ,\r\n                                    @Package_Name ,\r\n                                    OBJECT_ID('' + @Target_DB_Name + '.'\r\n                                              + @Target_DB_Schema_Name + '.'\r\n                                              + @Target_DB_Object_Name + '') AS ObjectID ,\r\n                                    ( SELECT    a.objectid\r\n                                      FROM      sys.dm_exec_requests r\r\n                                                CROSS   APPLY sys.dm_exec_sql_text(r.sql_handle) a\r\n                                      WHERE     session_id = @@spid\r\n                                    ) AS ProcessID ,\r\n                                    CAST(SUBSTRING(@Exec_Instance_GUID\t, 1, 8) + '-' + SUBSTRING(@Exec_Instance_GUID\t, 9, 4) + '-' + SUBSTRING(@Exec_Instance_GUID\t, 13, 4) + '-' +\r\n\t\t\t\t\t\t\t\t\tSUBSTRING(@Exec_Instance_GUID\t, 17, 4) + '-' + SUBSTRING(@Exec_Instance_GUID\t, 21, 12) AS UNIQUEIDENTIFIER) AS ExecutionInstanceGUID,\r\n                                    DB_NAME() AS DatabaseName\r\n                         )\r\n                INSERT  INTO AdminDBA.dbo.LogSSISErrors_Error\r\n                        ( [ErrorNumber] ,\r\n                          [ErrorSeverity] ,\r\n                          [ErrorState] ,\r\n                          [ErrorLine] ,\r\n                          [ErrorMessage] ,\r\n                          [ErrorDateTime] ,\r\n                          [FKLoginID] ,\r\n                          [FKUserID] ,\r\n                          [FKPackageID] ,\r\n                          [FKObjectID] ,\r\n                          [FKProcessID] ,\r\n                          [ExecutionInstanceGUID]\r\n                        )\r\n                        SELECT  ErrorNumber\t\t\t\t= COALESCE(err.ErrorNumber, -1) ,\r\n                                ErrorSeverity\t\t\t= COALESCE(err.[ErrorSeverity], -1) ,\r\n                                ErrorState\t\t\t\t= COALESCE(err.[ErrorState], -1) ,\r\n                                ErrorLine\t\t\t\t= COALESCE(err.[ErrorLine], -1) ,\r\n                                ErrorMessage\t\t\t= COALESCE(err.[ErrorMessage], 'Unknown') ,\r\n                                ErrorDateTime\t\t\t= ErrorDateTime ,\r\n                                FKLoginID\t\t\t\t= src_login.ID ,\r\n                                FKUserID\t\t\t\t= src_user.ID ,\r\n                                [FKPackageID]\t\t\t= src_package.ID ,\r\n                                [FKObjectID]\t\t\t= src_object.ID ,\r\n                                [FKProcessID]\t\t\t= src_process.ID ,\r\n                                [ExecutionInstanceGUID] = err.ExecutionInstanceGUID\r\n                        FROM    TempErr err\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Login src_login ON err.LoginName = src_login.LoginName\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_User src_user ON err.UserName = src_user.UserName\r\n                                                              AND src_user.FKDBID = ( SELECT\r\n                                                              ID\r\n                                                              FROM\r\n                                                              AdminDBA.dbo.LogSSISErrors_DB db\r\n                                                              WHERE\r\n                                                              db.DBName = err.DBName\r\n                                                              )\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Package src_package ON err.PackageName = ( LEFT(src_package.PackageName,\r\n                                                              CHARINDEX('.',\r\n                                                              src_package.PackageName)\r\n                                                              - 1) )\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Object src_object ON err.ObjectID = src_object.ObjectID\r\n                                LEFT JOIN AdminDBA.dbo.LogSSISErrors_Process src_process ON err.ProcessID = src_process.ProcessID\r\n                        WHERE   src_login.CurrentlyUsed = 1\r\n                                AND src_user.CurrentlyUsed = 1\r\n                                --AND src_package.CurrentlyUsed = 1\r\n                                AND src_object.CurrentlyUsed = 1\r\n                                AND src_process.CurrentlyUsed = 1   \t\t\t\t\t\t\t                                                 \r\n\t\t\t\tEND CATCH\r\n\t\t\t\tEND\r\n\t\t\tCLOSE db_cursor\r\n\t\t\tDEALLOCATE db_cursor \r\n\tEND\r\nGO\r\n<\/pre>\n
Thanks to each table\u2019s DML operation that the above code executes being wrapped up in a explicit transaction and error rollback process, a robust reporting platform can be created to log and account for any issues that might have been raised during runtime. Below is an image of a simple Tableau report which may be embedded into the error notification message, giving the operator a quick, high-level view of any issues that might have been logged in AdminDBA database during package execution.<\/p>\n
\"Data_Acquisition_Framework_Part2_Sample_Tableau_Report\"<\/a><\/p>\n
In the next post (part 3<\/a>) I will look into how smaller tables can be handled using a dynamic MERGE SQL statement creation, data acquisition validation mechanisms as well as the error notifications process triggered by a failed package\/procedure.<\/p>\n","protected":false},"excerpt":{"rendered":"
Note: Part 1 to this series can be found HERE, Part 3 HERE, Part 4 HERE and all the code and additional files for this post can be downloaded from my OneDrive folder HERE. Continuing on from part 1 to this series, having set up all the scaffolding and support databases\/objects, we are now ready […]<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[38,5,46,50],"tags":[10,49,19,13],"class_list":["post-2888","post","type-post","status-publish","format-standard","hentry","category-data-modelling","category-sql","category-sql-server","category-ssis","tag-data","tag-sql","tag-sql-server","tag-ssis"],"aioseo_notices":[],"jetpack_featured_media_url":"","_links":{"self":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2888","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=2888"}],"version-history":[{"count":20,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2888\/revisions"}],"predecessor-version":[{"id":2983,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/posts\/2888\/revisions\/2983"}],"wp:attachment":[{"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/media?parent=2888"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/categories?post=2888"},{"taxonomy":"post_tag","embeddable":true,"href":"http:\/\/bicortex.com\/bicortex\/wp-json\/wp\/v2\/tags?post=2888"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}