Solved

Sql2k Replication: Adding tables, Adding / editing sprocs

Posted on 2004-07-31
26
624 Views
Last Modified: 2007-12-19
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
Comment
Question by:jon100
  • 15
  • 11
26 Comments
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
Yes
0
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
Use ALTER PROCEDURE to change the setting.
0
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Accepted Solution

by:
crescendo earned 70 total points
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
Thanks for all your help!

Yeah you're not wrong, the books really should go into more depth - have you considered writing one? :)
0
 

Author Comment

by:jon100
Comment Utility
By the way - can you recommend a good book that covers SQL Server 2k replication?
0
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
<<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
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
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
 

Author Comment

by:jon100
Comment Utility
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
 
LVL 9

Expert Comment

by:crescendo
Comment Utility
Sorry, yes, that's exactly what I meant. You see, I'd be no good at writing a book!
0
 

Author Comment

by:jon100
Comment Utility
lol - dont sell yourself short....
0
 

Author Comment

by:jon100
Comment Utility
ill edit it if you like :)
0

Featured Post

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Having an SQL database can be a big investment for a small company. Hardware, setup and of course, the price of software all add up to a big bill that some companies may not be able to absorb.  Luckily, there is a free version SQL Express, but does …
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

728 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now