add new article to the publisher

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.

All Forums

SQL Server 2005 Forums

Replication (2005)

add new article to the publisher

Author

Topic

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-19 : 00:44:18

Dear All,
i'm in the transactional replication environment. we need to add one new table to the publisher. it is sql server 2005 environment. please explain me the steps

and in another table, i need to change the data type of a table.

please guide me

Arnav
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-19 : 01:39:38

Haven't you already askedthis question? To add an article, use sp_addarticle (look it up in BOL).

As for changing a datatype. I have explained that already.

http://www.replicationanswers.com/

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-19 : 01:57:33

i've tried to implement this RickD, but i'm getting error. and even checked the article also.
the error is

Server: Msg 20607, Level 16, State 1, Procedure sp_MSreinit_article, Line 99
Cannot make the change because a snapshot is already generated. Set @force_invalidate_snapshot to 1 to force the change and invalidate the existing snapshot.

please let me know will it affect any thing wrong? or replication stops with that set command?

Arnav
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-19 : 03:09:26

No, that command invalidates the snapshot. This will have the effect of sending out the snapshot again to the subscriber and the subscriber will be unavailable until it has re-populated. This is an option on the sp_changearticle and sp_addarticle stored procedures, look in BOL to find out what it does.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-19 : 04:51:12

Dear RickD,
can you please tell me the complete way of doing all these things? as it is very first time forme, i dont even know how to take the snap shot of the database. i dont know how to use the snapshot.

please correct me where ever i was wrong
1)i'l take the script to create a new table, and run that on publisher
2)now i'l use the script to add the table with the below script
EXEC sp_addarticle @publication = 'PublicationName'
, @article = 'NewTableName'
, @source_table = 'NewTableName'
is this enough or need to do some more?

Dear RockD, please help me in this regard...and dont treat this like a home work. as this is a simple concept to you but for me at this stage that is Greek and Latin.(as that is a production server)

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-19 : 08:16:07

Just add article in Publisher and restart the snapshot agent.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-19 : 08:30:15

As sodeep said. It really isn't rocket science. It is all there in front of you with replication monitor.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-19 : 14:24:36

Yes I Understood RickD. but if something goes wrong in prod environment, it costs my job

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-19 : 15:24:39

Sunsanvin,
I have been working with replication for almost 6 years. I never had a problem with it.
Question is:
Why do you post if you don't trust us?

And Good feature about SQL 2005 is if you add article, start snapshot agent, it won't run snapshot for whole articles like in SQL 2000.It runs only for one article,which is faster.

rmiao
Master Smack Fu Yak Hacker

7266 Posts

Posted - 2008-06-19 : 23:08:44

>> if something goes wrong in prod environment, it costs my job

Just test the process on testing machines, then go to prod servers once you know how.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-24 : 04:54:29

quote:
Originally posted by sodeep

Sunsanvin,
I have been working with replication for almost 6 years. I never had a problem with it.
Question is:
Why do you post if you don't trust us?

And Good feature about SQL 2005 is if you add article, start snapshot agent, it won't run snapshot for whole articles like in SQL 2000.It runs only for one article,which is faster.

Dear Sodeep, sorry if my words are in nagative manner.
but sure i've never say that i dont have belief on this forum experts.....because of you....and tara....i've implemented successfully on production server. twodays back, i'm very new to replication and dont know even what is a snapshot, and how to start that. after you and tara gave the support that midnight(in india), i got confidence, so that i've finished the task. i want to tell you one more thing, i'm a big fan of sqlteam.....this is my native place for my job. i've got a good job because of sql team. i've changed the previous company which is development side to the production environment you know why? i've very good confidence on this forum, and within 10 min, i'l get solution to any doubt.

thank you very much Sodeep and TKIZER. today i'm a successful person here because of your support two days back.

Arnav
Even you learn 1%, Learn it with 100% confidence.

RickD
Slow But Sure Yak Herding Master

3608 Posts

Posted - 2008-06-24 : 05:12:19

quote:
Originally posted by sodeep

Sunsanvin,
I have been working with replication for almost 6 years. I never had a problem with it.
Question is:
Why do you post if you don't trust us?

And Good feature about SQL 2005 is if you add article, start snapshot agent, it won't run snapshot for whole articles like in SQL 2000.It runs only for one article,which is faster.

sodeep - SQL 2000 would only run the snapshot for the article you added as well?!?

sunsanvin - Never run something straight into production, always make sure it is fully run on a test server in the first place.

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-24 : 05:46:08

Thank you RickD

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-24 : 10:12:28

quote:
Originally posted by RickD

quote:
Originally posted by sodeep

Sunsanvin,
I have been working with replication for almost 6 years. I never had a problem with it.
Question is:
Why do you post if you don't trust us?

And Good feature about SQL 2005 is if you add article, start snapshot agent, it won't run snapshot for whole articles like in SQL 2000.It runs only for one article,which is faster.

sodeep - SQL 2000 would only run the snapshot for the article you added as well?!?

sunsanvin - Never run something straight into production, always make sure it is fully run on a test server in the first place.

RickD,
I have already tested that but it doesn't come as default as in SQL 2005. Yes , you can do it but you have to tweak something .

sunsanvin
Master Smack Fu Yak Hacker

1274 Posts

Posted - 2008-06-25 : 02:19:11

can we generate the snapshot only for those added articles? or we need to generate for complete database?

Arnav
Even you learn 1%, Learn it with 100% confidence.

sodeep
Master Smack Fu Yak Hacker

7174 Posts

Posted - 2008-06-25 : 11:56:01

As you add new articles, it generates snapshot only for those articles.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources