Transactional Replication using T-SQL (custom SPs)

Question

Transactional Replication using T-SQL (custom SPs)

ramyakn14

SSC Enthusiast

Points: 118
More actions
April 8, 2015 at 10:46 am

#150335

Greetings Team, I want to setup transactional replication using T-SQL. I have the scripts working to stop and start the replication. I want to add new column to subscription table but want only 4 characters (say I want to add SSN column from publisher table to subscriber but should copy only last 4 digits to subscriber). I tried to use custom sp_MSIns_ and sp_MSUpd_ SPs but when I restart snapshot I'm getting all 9 digits of SSN not just 4 and those custom SPs are overwritten. Any suggestion/help to achieve this would be greatly appreciated.
Thanks

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Y.B. SSChampion Points: 11649 More actions · Answer 1

Why is it a requirement to only replicate part of the SSN column? If you are already replicating to the subscriber it can easily be extracted out in a query.

SELECT RIGHT(SSN,4) FROM Table

SELECT quote FROM brain WHERE original = 1
0 rows returned

ramyakn14 SSC Enthusiast Points: 118 More actions · Answer 2

Thank you for the quick reply. Requirement is: We want to replicate data from Production to test environments and want to publish selected columns and part of SSN to test environment. I did column filtering by selecting required columns. Now, on request I want to add SSN column (I'm using sp_articlecolumn to add new column) but not able to fetch only substring of data.

Y.B. SSChampion Points: 11649 More actions · Answer 3

So what does your modified SP's look like?

I'm still not sure if that is the best method since I'm pretty sure those SP's will get over written if you ever have to reinitialize.

You could alternatively...

Create a view/SP on the subscriber using said RIGHT function. That way the connecting client/app doesn't have to do the work.

OR

Create a computed column on the original table and replicate it. (technically you could probably achieve the same thing by creating a column on the subscriber but it could get blown away)

SELECT quote FROM brain WHERE original = 1
0 rows returned

ramyakn14 SSC Enthusiast Points: 118 More actions · Answer 4

Thank you for the pointers.

Creating a computed column on publication or subscriber to fetch just 4 digits will be my last option.

I would like to try your first option:

Create a view/SP on the subscriber using said RIGHT function. That way the connecting client/app doesn't have to do the work.

Do you have any suggestion on when and where to call this new SP? Because when I reinitialize the snapshot it fetches data and log reader will write to subscriber table all data.

Y.B. SSChampion Points: 11649 More actions · Answer 5

I'm a little confused by your question.

Do you have any suggestion on when and where to call this new SP? Because when I reinitialize the snapshot it fetches data and log reader will write to subscriber table all data.

You essentially don't worry about trimming the column during replication and just leave it as is. Then you create a view on the subscriber that is essentially identical to myTable but with the shortened SSN and call it v_myTable (for example). This essentially becomes your new data source and all you have to do is change the connection string. Simple to do and the string manipulation isn't done client side.

SELECT quote FROM brain WHERE original = 1
0 rows returned

ramyakn14 SSC Enthusiast Points: 118 More actions · Answer 6

Got your point. Thant will duplicate the table. I guess it is better to have extra column on same table and populate with RIGHT(SSN, 4).

One more quick question, can I use trigger to update same SSN column (without creating new one) to store last 4 digits in it?

ramyakn14 SSC Enthusiast Points: 118 More actions · Answer 7

Thank you, SSC Rookie. We decided to add new column on publisher table, add trigger to populate the new column with last 4 digits and replicate the new column.

Appreciate all your help.

Y.B. SSChampion Points: 11649 More actions · Answer 8

Rather than use a trigger I would have simply done:

ALTER TABLE dbo.myTable ADD ShortSSN AS RIGHT(SSN,4);

Cheers,

SELECT quote FROM brain WHERE original = 1
0 rows returned