September 17, 2003 at 10:53 am
I would like to know if anyone knows a way to replicate stored procedures between servers.
September 17, 2003 at 11:24 am
The procs themselves or the execution? You can select views and stored procs as part of a pub and they'll be sent over with the snapshot, but changes to the code after that are not replicated. If you want to replicate stored proc execution, look at your config options.
Andy
September 18, 2003 at 2:41 am
hi!
have to add something to andy's posting: changes to stored procedures are not replicated withing merge replication, but within transactional replication (sql server 2000).
best regards,
chris.
September 18, 2003 at 6:25 am
Using Enterprise Manager, right click on the stored procedure, select All Tasks, then General SQL Script. Press OK to create a script file which you can run on another server to create the store procedure.
September 18, 2003 at 11:41 am
I've found the best way for me to make sure that my subscribers have the correct SPs is to use a DTS Package to copy the stored procedures and functions. This way if I add a subscriber I can simply copy the connection in my DTS Package from another subscriber and change the connection. Then I can simply execute that step to copy the objects to the subscriber. Anytime I make a change to an object I can simply run the whole package and it gets rolled out to all my subscribers.
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
Gary Johnson
Microsoft Natural Language Group
DBA, Sr. DB Engineer
This posting is provided "AS IS" with no warranties, and confers no rights. The opinions expressed in this post are my own and may not reflect that of my employer.
September 18, 2003 at 11:50 am
I usually use a DTS package to move any db objects (and small amounts of data) from one server to another. The task to use is called "Copy SQL Server Objects Task". Works fine for procs as well as tables, functions, constraints, etc. It does screw up the procedures formatting..strips all tabs and empty lines out.
Signature is NULL
September 24, 2003 at 2:23 am
This is what I did for my stored procedures, used in multiserver jobs:
I created a publication, named multi_server_procs and replicated it to other servers with snapshot replication. When I need an immediate changes on target servers, I run a snapshot agent manualy and then distribution agents.
September 25, 2003 at 4:55 am
That's a nice solution, using the snapshot. Would handle views as well.
Andy
September 25, 2003 at 5:10 pm
What would the DTS package look like that's been described? Would it just be copying the appropriate syscomment and sysobject rows to the new database?
Any code samples would REALLY be helpful.
TIA
Andre
September 25, 2003 at 5:11 pm
On another note, the DTS doesn't let me select the system tables in the destination (its SQL 7). Have I just missed something here?
September 26, 2003 at 9:17 am
Instead of running the snapshot, for me it is easier to run sp_addscriptexec, passing the script file that modifies the sp.
Then the next time the distribution agents, runs, the execution of the script is replicated to all the subscribers.
September 26, 2003 at 10:44 am
Is sp_addscriptexec only visible in 2000? I don't see it in my SQL 7.
October 17, 2003 at 6:34 am
Hi!
I would like to create a snapshot replication, and I would like to replicate stored procedures and functions also. I add these articles to the snapshot, but when I try to apply this snapshot to the subscriber it will fail because there is a precedence between the sp-s (sp_a use sp_b and when sp_a would be created sp_b is not in the DB). How can I define this precedence for the articles (which one should be replicated first..).
Thanks for the help!
Bye : Peter
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply