CHECKSUM Table Synchronization

Table Synchronization Demo using CHECKSUM

Prerequisites
1) Microsoft's Demo AdventureWorks database.
2) A second copy of AdventureWorks,, restored as AdventureSync

Step 1 : Create the usp_SyncTables procedure -

USE AdventureWorks
GO

CREATE PROCEDURE dbo.usp_SyncTables AS
/*

 
  dbo.usp_SyncTables
 
 
  Procedure to update Person table from AdventureWorks
 
 
  For each table perform 3 operations >
  1. Insert missing records
  2. Remove deleted records
  3. Update ammended records
 
 
  
   1
   20/05/2007
   SQL Solace
   Original Version
  
  
*/

-- sync tables in tsql
SET IDENTITY_INSERT [AdventureSync].[Person].[Contact] ON

INSERT INTO [AdventureSync].[Person].[Contact]
          ([ContactID]
     ,[NameStyle]
          ,[Title]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
          ,[Suffix]
          ,[EmailAddress]
          ,[EmailPromotion]
          ,[Phone]
          ,[PasswordHash]
          ,[PasswordSalt]
          ,[rowguid]
          ,[ModifiedDate])
SELECT source.[ContactID]
     ,source.[NameStyle]
     ,source.[Title]
     ,source.[FirstName]
     ,source.[MiddleName]
     ,source.[LastName]
     ,source.[Suffix]
     ,source.[EmailAddress]
     ,source.[EmailPromotion]
     ,source.[Phone]
     ,source.[PasswordHash]
     ,source.[PasswordSalt]
     ,source.[rowguid]
     ,source.[ModifiedDate]
 FROM [AdventureWorks].[Person].[Contact] source
LEFT OUTER JOIN [AdventureSync].[Person].[Contact] target
ON source.[ContactID] = target.[ContactID]
WHERE target.[ContactID] is null

SET IDENTITY_INSERT [AdventureSync].[Person].[Contact] OFF

-- items to delete
DELETE [AdventureSync].[Person].[Contact]
FROM [AdventureWorks].[Person].[Contact] source 
RIGHT OUTER JOIN [AdventureSync].[Person].[Contact] target
ON source.[ContactID] = target.[ContactID]
WHERE source.[ContactID] is null

-- items to update
UPDATE [AdventureSync].[Person].[Contact]
  SET [NameStyle] = source.[NameStyle]
     ,[Title] = source.[Title]
     ,[FirstName] = source.[FirstName]
     ,[MiddleName] = source.[MiddleName]
     ,[LastName] = source.[LastName]
     ,[Suffix] = source.[Suffix]
     ,[EmailAddress] = source.[EmailAddress]
     ,[EmailPromotion] = source.[EmailPromotion]
     ,[Phone] = source.[Phone]
     ,[PasswordHash] = source.[PasswordHash]
     ,[PasswordSalt] = source.[PasswordSalt]
     ,[rowguid] = source.[rowguid]
     ,[ModifiedDate] = source.[ModifiedDate]
from [AdventureWorks].[Person].[Contact] source
join [AdventureSync].[Person].[Contact] target
on source.[ContactID] = target.[ContactID]
and CHECKSUM(source.[NameStyle]
   ,source.[Title]
   ,source.[FirstName]
   ,source.[MiddleName]
   ,source.[LastName]
   ,source.[Suffix]
   ,source.[EmailAddress]
   ,source.[EmailPromotion]
   ,source.[Phone]
   ,source.[PasswordHash]
   ,source.[PasswordSalt]
   ,source.[rowguid]
   ,source.[ModifiedDate]) <> CHECKSUM(target.[NameStyle]
            ,target.[Title]
            ,target.[FirstName]
            ,target.[MiddleName]
            ,target.[LastName]
            ,target.[Suffix]
            ,target.[EmailAddress]
            ,target.[EmailPromotion]
            ,target.[Phone]
            ,target.[PasswordHash]
            ,target.[PasswordSalt]
            ,target.[rowguid]
            ,target.[ModifiedDate])

GO

Step 2 : Create some work for the sync procedure by messing up the data in the AdventureSync database -

-- Insert an extra 3770 rows in AdventureSync by duping rows where EmailPromotion = 2
INSERT INTO [AdventureSync].[Person].[Contact]
          ([NameStyle]
          ,[Title]
          ,[FirstName]
          ,[MiddleName]
          ,[LastName]
          ,[Suffix]
          ,[EmailAddress]
          ,[EmailPromotion]
          ,[Phone]
          ,[PasswordHash]
          ,[PasswordSalt]
          ,[ModifiedDate])
SELECT [NameStyle]
     ,[Title]
     ,[FirstName]
     ,[MiddleName]
     ,[LastName]
     ,[Suffix]
     ,[EmailAddress]
     ,[EmailPromotion]
     ,[Phone]
     ,[PasswordHash]
     ,[PasswordSalt]
     ,[ModifiedDate]
 FROM [AdventureWorks].[Person].[Contact]
WHERE [EmailPromotion] = 2

-- Update 468 rows
UPDATE [AdventureSync].[Person].[Contact]
SET [LastName] = 'Fred'
WHERE [LastName] LIKE 'Z%'

GO

Step 3 : Test the procedure -

use AdventureWorks;

exec usp_SyncTables

The Output -

(0 row(s) affected)
(3770 row(s) affected)
(468 row(s) affected)

The output of the procedure shows -

0 rows inserted (correct, we didnt delete any)
3770 rows deleted (correct, these were the ones we inserted)
468 rows updated (correct, the LastName values have been corrected).

Observation : SSIS Errors

In SSIS, we get cascading errors displayed.


All this is generated by a network failure >

- Copying to [Saved_Audit_Tables].[dbo].[PreCIS_WO_PAYMENT_CERTIFICATE_recalculation] (Error)
Messages
Error 0xc0202009: Data Flow Task: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Protocol error in TDS stream".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "Communication link failure".
An OLE DB record is available. Source: "Microsoft SQL Native Client" Hresult: 0x80004005 Description: "TCP Provider: An existing connection was forcibly closed by the remote host.
".
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 14 - WO_PAYMENT_CERTIFICATE_ReverseSTGMIgrationBefore" (2603) returned error code 0xC0202009. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread4" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread3" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread3" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc0047039: Data Flow Task: SSIS Error Code DTS_E_THREADCANCELLED. Thread "WorkThread4" received a shutdown signal and is terminating. The user requested a shutdown, or an error in another thread is causing the pipeline to shutdown. There may be error messages posted before this with more information on why the thread was cancelled.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "WorkThread4" has exited with error code 0xC0047039. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

Error 0xc02020c4: Data Flow Task: The attempt to add a row to the Data Flow task buffer failed with error code 0xC0047020.
(SQL Server Import and Export Wizard)

Error 0xc0047038: Data Flow Task: SSIS Error Code DTS_E_PRIMEOUTPUTFAILED. The PrimeOutput method on component "Source 3 - PreCIS_WO_PAYMENT_CERTIFICATE" (115) returned error code 0xC02020C4. The component returned a failure code when the pipeline engine called PrimeOutput(). The meaning of the failure code is defined by the component, but the error is fatal and the pipeline stopped executing. There may be error messages posted before this with more information about the failure.
(SQL Server Import and Export Wizard)

Error 0xc0047021: Data Flow Task: SSIS Error Code DTS_E_THREADFAILED. Thread "SourceThread3" has exited with error code 0xC0047038. There may be error messages posted before this with more information on why the thread has exited.
(SQL Server Import and Export Wizard)

CHECKSUM Column compatibility

" Msg 527, Level 16, State 2, Line 1
Implicit conversion between XML types constrained by different XML schema collections is not allowed. Use the CONVERT function to run this query. "

I was comparing tables using CHECKSUM on the adventureworks database.
The Person.Contact table has a XML column which caused this (incompatible with CHECKSUM).

Column : AdditionalContactInfo
Datatype : xml(CONTENT Person.AdditionalContactInfoSchemaCollection)

AdminPak : Cool Remote Desktops Tool for XP

Remote Desktops allows you to save RDP connnections and connect from the comfort of a MMC session. You can find it in AdminPak.

AdminPak for Windows XP -
http://www.microsoft.com/downloads/details.aspx?familyid=c16ae515-c8f4-47ef-a1e4-a8dcbacff8e3&displaylang=en

Creating and Enabling a CLR Function.

As a very simple example of CLR integration I create a CLR Assembly function to be called from SQL here.

Prerequisite to this exercise > Enabling CLR Integration on SQL server.

Step 1 : Create a basic class in .NET -

• Start Visual Studio 2005
• File , New , Project
• Select 'Visual Basic' (left hand navigation pane) and 'Class Library' (right hand navigation pane).
• Select OK.

You are presented with a blank class template , which looks like this -


Way too much .net explanation would have to go here, but basic steps are >

• Name your class
• Name and code a function to be called from SQL (i've chosen a Roman Numerals one here).
• Save it!
• Give it a more sensible name -
  Rename ClassLibrary1 to SQL_CLR_Project
  (Do this in Solution Explorer on the right hand side)
• File , Save All

Step 2 : Compile the class to a .DLL file >

Change Project properties -

• Project menu , SQL_CLR_Project Properties
• Change Assembly Name - I chose 'CLRFunctionLibrary' (This will match the .dll name i.e CLRFunctionLibrary.dll will be generated).
• Change the Root Name Space. I changed this to 'SQLCLROne' to match the project.

Compile it -

• Build Menu - 'Build SQLCLROne'

Find the dll just created (CLRFunctionLibrary.dll). -

C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\obj\Debug


Step 3 : Register the assembly within SQL Server -

In TSQL -

CREATE ASSEMBLY [CLRFunctionLibrary]
FROM 'C:\!RD\Visual Studio 2005\Projects\Class Libraries\SQLCLROne\ClassLibrary1\bin\Debug\CLRFunctionLibrary.dll'
WITH PERMISSION_SET = SAFE
GO

These 2 queries verify the Assembly has been created, and will show other assemblies present -

SELECT * FROM sys.assemblies

SELECT * FROM sys.assembly_files

Step 4: Register the method within the assembly. -

CREATE FUNCTION functionName
 (@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName

CREATE FUNCTION dbo.fn_RomanNumeral
 (@number AS INTEGER)
RETURNS NVARCHAR(20)
AS EXTERNAL NAME CLRFunctionLibrary.[SQLCLROne.SQLCLROne].RomanNumeral

Notes :

1. I had to declare the Namespace when calling from the Assembly i.e the declaration became >
CREATE FUNCTION functionName
(@sqlVariable AS sqlDataType)
RETURNS sqlDataType
AS EXTERNAL NAME Assembly.[NameSpace.ClassName].FunctionName


2. 2 Use the type of NVARCHAR not VARCHAR to match with the output STRING declared in vb.net.

Now the function is created, you can see it like this -

SELECT * FROM sys.assembly_modules

and test it like this -

select dbo.fn_RomanNumeral(1066)
select dbo.fn_RomanNumeral(1976)
select dbo.fn_RomanNumeral(2007)

If you experience compatibility issues you can determine which version of .NET is installed by querying the sys.dm_os_loaded_modules DMV (dynamic management view).

SELECT *
FROM sys.dm_os_loaded_modules
WHERE [name] LIKE N'%\MSCOREE.DLL'

TSQL : Show Available CLR Objects

TSQL to show available CLR objects -

SELECT  objects.object_id
  ,schema_name(objects.schema_id) + '.' + objects.[name] AS [CLRObjectName]
  ,objects.type_desc as [CLRType]
  ,objects.create_date as [Created]
  ,objects.modify_date as [Modified]
  ,assemblies.permission_set_desc AS [CLRPermission]
FROM sys.objects objects
INNER JOIN
  sys.module_assembly_usages module_assembly_usages
       ON objects.object_id = module_assembly_usages.object_id
INNER JOIN
  sys.assemblies AS assemblies
       ON module_assembly_usages.assembly_id = assemblies.assembly_id

SQL 2005 : Using ORDER BY in a view.

The ORDER BY clause is not allowed in a view. It is ignored by the query parser.

To get round this prior to SQL 2005, a TOP 100 PERCENT predicate can be specified in the view definition, e.g. -

CREATE VIEW vw_AlphabeticalEmployees AS 
SELECT TOP 100 PERCENT 
            Contact.LastName, 
            Contact.FirstName, 
            Employee.Title 
    FROM Person.Contact Contact 
            INNER JOIN HumanResources.Employee Employee 
            ON Contact.ContactID = Employee.ContactID 
    ORDER BY LastName, FirstName   

This no longer works in SQL 2005. The view returns data, but the ordering is not applied.

To get round this we can specify TOP 2147483647.
2147483647 is the largest integer that can be passed to the statement and should safely cover most OLTP recordsets!

The SQL 2005 version is therefore -

ALTER VIEW vw_AlphabeticalEmployees AS 
SELECT TOP 2147483647 
            Contact.LastName, 
            Contact.FirstName, 
            Employee.Title 
    FROM Person.Contact Contact 
            INNER JOIN HumanResources.Employee Employee 
            ON Contact.ContactID = Employee.ContactID 
    ORDER BY LastName, FirstName   

20/06/2007 - A colleague has just alerted me to the fact that this feature has now been addressed by a hotfix http://support.microsoft.com/kb/926292

OakLeaf Systems: SQL Server 2005 Ordered View and Inline Function Problems

Management Studio Speedup : Managed Code

SQL Server Management Studio Speedup (Prevent SQL taking ages to open)

1. Close SSMS.
2. Go into IE, select Tools | Internet Options | Advanced
3. If “Check publisher’s certificate revocation” under the security node is checked, then uncheck it.
4. Close IE, Reopen SSMS.

Why all this bother? What has Internet Explorer got to do with SQL Management Studio?

Basically, Managment Studio contains 'Managed code'. It attempts to contact crl.microsoft.com on startup to check for a certificate for the code when this setting is allowed.
Bear in mind this setting will affect other apps too.