asked
Transactional Replication query
Hello Friends, I am working on Transactional Replciation and i have a query. Can someone help me please At publisher i have a table which has 5 columns. I am replicating this to a different server which would be my Subscriber My requirement is 1. I need 6 columns on subscriber : Which i can directly add one additional column at subscriber 2. The new column should get last 6 digits of column2 (PAN) everytime it does insert. 3. I have tried to amend the stored procedue that does insert ,but it is failing with an error message saying that @c6 is not valid parameter How can i achieve this ?
Maybe you could just add a computed non-persisted column instead of a physical column:
https://technet.microsoft.com/en-us/library/ms191250(v=sql.105).aspx
Hi Sjimmo. Thanks for the response. DO i need to add computed column at Publisher or Subscriber? If i add computed column at publisher does that replicate automatically to subscriber or do i need to amend any stored procedures?
Put it at the subscriber and you won't have to change the publication
Sjimmo/David Wimbush As suggested i have added computed column and it works fine. But my requirement is a bit different The new column should get last 6 digits of column2 (PAN) everytime it does insert. New column is getting 6 digits of column2 , but my column 2 is encrypted on my subscriber as it has sensitive
information.My computed column returns me 6 digits which is encrypted, but i rather want the 6 digits of unencrypted (which is the same as it is at publisher). I am happy to add additional column at subscriber , but struggling to amend the insert procedure to add that additional column in the sproc? Can guidance would be much helpful for me please. Hope i have made my requirement clear .
Hi sjimmo It is encrypted (after snapshot)while it is being replicated from Publisher to subscriber.I have customised insert stored procedure and it is working fine. It is failing when an update is running? Update sproc is complex . unable to amend it. Can you provide me your email so that i can share my code ?
Use this email address:
sclemens3@charter.net It isn't normally monitored, but I will watch it. But I'm curious, you say it is encrypted after the snapshot. Is that the replication snapshot or something else?
sjimmo, I have emailed you all the details. Can you please check when you have sometime.
OK Rehaan, I think I understand now. It appears that the data is encrypted on the subscriber side only, and not on the publisher side. I would a field on the publisher and have a calculation that takes a substring of each of the fields that you want to put together. Then simply replicate this field as a normal field. ie: [TestField] AS (SUBSTRING([field1],(1),(6))+SUBSTRING([field2],(1),(6))) You will not have to change the application that inserts the data. Once it is inserted/updated the field is recalculated and replicated. If you wish to do it on the subscriber side, you are going to have to be a lot more elaborate in your SP and calculate the field which I believe is @c19 before doing the encryption in order to populate that field. Thus after the begin in your SP, then calculate as select @c19 = substring(field1, 1, 6) + substring(field2, 1, 6). Then do the insert as it is written but get rid of everything after the @c19. Do the same with the update SP. In other words do your calculation before the encryption. Good luck
Thanks Sjimmo for your quick response. In my case i am encrypting only at subscriber which is @c1 and @c2. using a encryption function for @c1 and @c2 and substring of @c1 and @c2 for @c20 and @c21. Insert is working fine. I am having no issues with inserts and encryption. I am having issues with update SP. Having issues with update SP only .Struggling to amend this. I need to amend @c1,@c2,@c20 and @c21
The data coming into your SP from the publication is unencrypted. So do your concatenation of C1 and C2 right after the begins. This will give you what you want. ie: begin select substring (@c1, 1, 6) + substring(@c2, 1, 6) if ... begin ... end else begin end end This way when you are doing our case for that field, @c19 is already calculated. You cannot do this after the encryption has taken place. Otherwise you will need a trigger or another sp to decrypt the fields in question and then do the concatenation.
19 People are following this question.
