Replication using custom mapping AND custom table create scripts for NON SQL subscriber - Applies to SQL server 2005 and 2008

Replication using custom mapping AND custom table create scripts for NON SQL subscriber - Applies to SQL server 2005 and 2008

The purpose of this document is to demonstrate how SQL Server replication can be directed to use custom mapping while copying data to NON-SQL database.

This document requires the audience to know SQL server management. Replication and types of replication. It also requires you to SQL programming knowledge of DB2 database. That will be our example NON –SQL database.

Sample database:

In our example we will replicate two columns from Categories table of NorthWind database. Northwind database can be downloaded from https://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46A0-8DA2-EEBC53A68034&displaylang=en

Original table definition for Categories table is like this:

Table create

USE [Northwind]

GO

/****** Object: Table [dbo].[Categories] Script Date: 08/03/2009 11:05:36 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE TABLE [dbo].[Categories](

[CategoryID] [int] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,

[CategoryName] [nvarchar](15) NOT NULL,

[Description] [ntext] NULL,

[Picture] [image] NULL,

CONSTRAINT [PK_Categories] PRIMARY KEY CLUSTERED

(

[CategoryID] ASC

)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

Create a Snapshot publication for Article Categories. Select only two columns from Categories as the columns to be replicated - those are CategoryID and Description. Create a NON-SQL subscriber using

Create a Snapshot publication and NON-SQL subscriber:

Following steps will guide you through creating simple snapshot publication. Note that the security method selected is not recommended one. Your organisation may need more strict security and please alter your publication accordingly.

Creating subscription:

DB2OLEDB provider connection string looks like this:

Provider=DB2OLEDB;User ID=XXXXXX;Password=******;Initial Catalog=YYYYYY;Network Transport Library=TCP;Host CCSID=37;PC Code Page=1252;Network Address=YYYYYY;Network Port=446;Package Collection=XXXXXX;Default Schema=XXXXXX;Process Binary as Character=True;Units of Work=RUW;DBMS Platform=DB2/AS400;Defer Prepare=False;Rowset Cache Size=0;Authentication=Server;Persist Security Info=True;Connection Pooling=True;Derive Parameters=False;

Determining supported mappings for your remote DBMS:

SQL server has a mappings table that keeps track of what all source data types can be mapped to what all destination data types on different DBMS platforms. For e.g. Following query retrieves all the mappings for DB2 as a destination dbms :

select * from msdb.dbo.sysdatatypemappings

where destination_dbms ='DB2'

And following query does find all the mappings for DB2 for source SQL data type = next:

select * from msdb.dbo.sysdatatypemappings

where source_type= 'ntext' AND destination_dbms ='DB2'

If desired custom mapping does not exist then use sp_adddatatypemapping to define one, something like in e.g. below:

--if above query does not return anything then add maping yourself using something like below

exec sp_adddatatypemapping

@source_dbms= 'MSSQLSERVER',

@destination_dbms='DB2', @source_type = 'ntext',

@destination_type='VARCHAR', @destination_length=32703

Setting the Article properties to use custom create TABLE script :

Once you verify or create required mapping, we need to tell replication as to how to use this mapping. SQL server allows this through stored procedure sp_changearticle.

More reference on sp_changearticle can be found at: https://msdn.microsoft.com/en-us/library/ms175980(SQL.90).aspx

--set the creation_script property of Article to point to custom Create Script

sp_changearticle @publication = SUBREPL,

@article = Categories,

@property = 'creation_script',

@value = "d:\TEMP\RFC4789\SUBREPL.sql",

@force_invalidate_snapshot = 1,

@force_reinit_subscription = 1

-- then change the Schema option.

DECLARE @option AS int;

SET @option = (SELECT CAST(0x00 AS int));

EXEC sp_changearticle @publication = SUBREPL,

@article = Categories,

@property = N'schema_option',

@value = @option,

@force_invalidate_snapshot = 1,

@force_reinit_subscription = 1

We have changed the Article properties by doing above two steps, now its time to discuss our e.g. Script SUBREPL.sql that creates the TABLE. Since we want this Article to be replicated on DB2, it is required to use DB2’s sql syntax.

e.g. our SUBREPL.sql contains following commands in it to create table and index on it. Note that this is DB2 syntax for OS/400. bypass_translation is required to be mentioned in the script so that publication will not translate the syntax of any queries that follows.

bypass_translation

DROP TABLE CATEGORIES

GO

CREATE TABLE CATEGORIES (

CATEGORYID INT NOT NULL,

DESCRIPTION VARCHAR (32703))

GO

CREATE UNIQUE INDEX PK_CATEGORIES ON CATEGORIES (CATEGORYID)

GO

Match cases on object names:

Because DB2 is case sensitive we will make sure that Article property "Destination Object Name" is set to correct one . It has to be in the same case as it appears in the SUBREPL.sql Script

See the following screenshot:

That’s it you replication is set to go from ntext to VARCHAR using custom CREATE script.