Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
ranvir_2k
Posting Yak Master
180 Posts |
 Posted - 2011-06-30 : 10:44:04
|
| Hi all,I'm curious as to how transactional replication works.On my subscriber side I can see that the same stored procedures that are being executed on the publisher are being executed on the subscriber.I thought that replication only replicates the physical changes made on the publisher, i.e. each insert, update, delete. |
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-30 : 10:50:30
|
| Should take the updates from the tr log copy them to the distributor then call SP's to execute them on the subscriber.Could be that you have another subscription on the publsher so it gets executed there too.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-06-30 : 11:47:29
|
| what if your stored procedure has get_date() in it. For example your stored procedure does this:insert into col1 (getdate())When the stored procedure get's executed on the subscriber, it will produce a different value than on the publisher. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-30 : 11:56:11
|
| The values sent t othe subscriber are the values updated in the table so it will be the value of getdate() on the publisher that was inserted.The sp to do the insert on the subscriber has all the columns of the table as parameters and the values are passed in.You should be able to see all the SPs on the subscriber.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
ranvir_2k
Posting Yak Master
180 Posts |
Posted - 2011-06-30 : 12:07:02
|
| So that means that the stored procedures have to exist on the subscriber side? |
|
|
|
russell
Pyro-ma-ni-yak
5072 Posts |
Posted - 2011-06-30 : 12:17:52
|
| The subscriber doesn't execute the SPs that were executed on the publisher.There are replication stored procedures that do the updating on the subsscribers.If you look on the subscriber, you'll see SPs prefixed "sp_MSDel_" and "sp_MSins_" and "sp_MSupd_" those are what're performing the updates. |
|
|
|
nigelrivett
Master Smack Fu Yak Hacker
3385 Posts |
Posted - 2011-06-30 : 12:22:54
|
quote:
Originally posted by ranvir_2k
So that means that the stored procedures have to exist on the subscriber side?
You know which SPs are being executed - have a look at them and you will probably understand what is going on.3 SPs were created for each table involved on the subscriber when you set up replication.==========================================Cursors are useful if you don't know sql.SSIS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
|
|
|
How does transactional replication work