Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 635
  • Last Modified:

Sql2k Replication: Adding tables, Adding / editing sprocs

I have two servers merge replicating.

I have seen a lot written about the sp_repladdcolumn and sp_repldropcolumn - though not much about what happens if you want to create a new table (on the publisher) and apply that to the replication. Same for editing or adding a sproc.

Are there way to do this without having to kill replication and start again?

Thanks!
0
jon100
Asked:
jon100
  • 15
  • 11
1 Solution
 
crescendoCommented:
Stored procedures aren't part of replication, so you can just add them to the subscriber.

Tables have to be published, but you can add a table to an existing publication with sp_addarticle.

It's easier to do all this in Enterprise Manager unless it's a dynamic process.
0
 
jon100Author Commented:
Great thanks.

Its strange though - I tried to run a sql script that dropped and recreated a sproc and it came up with the error:
"Cannot drop the procedure 'dbo.usp_myNewSproc' because it is being used for replication"
0
 
jon100Author Commented:
So I can just add a sproc to either end as normal?

Also - how can I add an article in the Enterprise Manager?

thanks..
0
What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

 
crescendoCommented:
Stored procedures can be marked FOR REPLICATION, which means that when the sp is called on the publisher, replication causes the same sp to be called on the subscriber, with the same parameters. The sp will have been copied as part of the initial snapshot, so you will have to redo the snapshot or add it manually.

In Enterprise Manager on the Publisher, right-click on the publication and it should be an option in there. I can't check at the moment, as I don't have access to a replicated server.

If you look in Books Online, type "articles, modifying" in the index tab, there are details of how to do it.
0
 
jon100Author Commented:
thanks again

so if, for example, i have a sproc that calls a local dts package that imports a csv file - i would want to have  it market NOT FOR REPLICATION if the same dts package & csv file did not exist on the subscriber?
0
 
crescendoCommented:
Yes
0
 
jon100Author Commented:
are sprocs, by default, FOR or NOT FOR REPLICATION? How can I change that setting? Is it possible to change a sproc to 'not for' if its already been published as 'for'

(please excuse my ignorance)
0
 
crescendoCommented:
I think NOT is the default, so unless you explicitly marked it FOR REPLICATION you should be OK. Could this be the problem in your other question? I'll nip over and post an answer... :)
0
 
jon100Author Commented:
lol thanks :)

i never set that yet it came up with the 'cant change sproc because its used for replication' error - so perhaps FOR is the default?

in any case - how can I set this (sorry cant see it) - do you know if its possible to change that option after replication has been setup?
0
 
crescendoCommented:
Use ALTER PROCEDURE to change the setting.
0
 
jon100Author Commented:
thinking about it - when i set replication up using the wizard - i just selected all objects (all tables and sprocs) - so I guess that automatically makes them all 'for replication'?!
0
 
crescendoCommented:
Can't  be sure, Enterprise Manager does a massive amount of work setting up replication. You can generate the scripts to create the replication and take a look through those if you have a week or two to spare! :)
0
 
jon100Author Commented:
lol think ill give that a miss :)

ok only one thing left - how can i remove a sproc / table from being replicated? Ive gone into publication properties and added articles fine - though it wont let me remove them.
0
 
jon100Author Commented:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sp_repl2_1a3p.asp

In that link it appears to imply that you cannot drop a merge article without first dropping the subscriber - is there another way to do it (ie drop a merge article *without* dropping the subscriber - and effectively restarting replication?).
0
 
crescendoCommented:
Yep, you basically have to drop the subscription, make the changes, then re-publish. Not a quick process if the database is large, as you'll have to send another snapshot.
0
 
jon100Author Commented:
woh - yeah its a big database - thats a big job.

ok thanks

can i just check one thing - I have an office server and a web server - the two are replicated. The office server has an admin system on and the web server has a website on. Both need to run sprocs.

You say that if stored procedures are 'FOR REPLICATION' then they are executed on the publisher and subscriber (if its run on subscriber its auto run on publisher, and visa versa?).

so if I republish, and *do not select any* sprocs for replication - then go to the subscriber and run a sql script to setup the sprocs - it will then not replicate sproc actions - ie if a sproc runs on publisher OR subscriber it will not also be run on the other. Instead it will just propagate any changes in the tables etc.

Is that right? One problem I can see is that, say there is a create customer sproc on one of the boxes - in that sproc it creates a customer using newid() for the customers id - if its replicated it will create two different customers on either box.

sorry to stretch this out - I really appreaciate your help!
0
 
crescendoCommented:
If a sproc is marked FOR REPLICATION, when you execute it on the publisher any changes to tables in the same publication are not sent to the subscriber. Instead, it sends the same parameters and executes the sproc at the subscriber via RPC.

The question with NewID is whether you do it as part of a new customer sproc, or in an insert trigger. If you do it in a sproc you shouldn't have a problem. If you do it in a trigger, the subscriber will get the new row and try to execute the same trigger. Guess how you stop this happening? Mark the trigger NOT FOR REPLICATION, which stops its triggering where the incoming data is sourced from a replication rather than a local user.

The books make replication seem ever so easy. Just click here, then here, and bingo you have a replicated system. Hmmm....
0
 
jon100Author Commented:
Thanks for all your help!

Yeah you're not wrong, the books really should go into more depth - have you considered writing one? :)
0
 
jon100Author Commented:
By the way - can you recommend a good book that covers SQL Server 2k replication?
0
 
crescendoCommented:
<<can you recommend a good book>>

Sadly no. I struggled to find out what I know, mainly by reading Books Online and solving lots of problems myself, very slowly. As I said before, it turns out to be more complicated than the books suggest. I spent over a year implementing a replication schme for a client, and had to write several programs to simplify setup, monitoring, password changes etc. The idea was to leave the client able to manage it themselves but I suspect they still struggle to grasp the concepts.
0
 
jon100Author Commented:
lol yes - i expected it to be a lot simpler at the outset. Im sure people would pay even for a small book on replication if it contains invaluable information - so if possible I think you should consider it.

Ive just got another quick point - Ive imported something into one of the databases using a DTS package (from a csv file). Ive noticed that the changes are not propagated across to the other database (though if I manually edit any of the records they are).

I assume there is a trigger somewhere that needs to be fired that is not being fired when the DTS import runs - do you know any easy ways to get round this and make the imported data automatically copy to the other database?

thanks..
0
 
crescendoCommented:
DTS is probably using BULK INSERT which is not logged and therefore won't trigger replication. Try importing to a temporary file and inserting into the live table from there.
0
 
jon100Author Commented:
thanks..

when you say 'file' - if I import to an 'intermediate' table first - then copy from that table to the correct ones, that should be ok yes?
0
 
crescendoCommented:
Sorry, yes, that's exactly what I meant. You see, I'd be no good at writing a book!
0
 
jon100Author Commented:
lol - dont sell yourself short....
0
 
jon100Author Commented:
ill edit it if you like :)
0

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

  • 15
  • 11
Tackle projects and never again get stuck behind a technical roadblock.
Join Now