Hi
I am using merge replication to sync sqlserver 2000 sp3 database and sql
server ce 2.0 sp3 via PPC 2003. I received the following error.
"Sql CE Exception: Create subscription failed:
system.Data.Sqlserverce.sqlceException"
"Create subscription failed (27750 - 8004005)"
Please help.
80004005 is a generic access denied. Are you sure the account you are using
to pull the subscription is in the PAL of your merge publication?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Pcherlop" <Pcherlop@.discussions.microsoft.com> wrote in message
news:930ED762-1874-4513-B675-0FFC27A418E0@.microsoft.com...
> Hi
> I am using merge replication to sync sqlserver 2000 sp3 database and sql
> server ce 2.0 sp3 via PPC 2003. I received the following error.
> "Sql CE Exception: Create subscription failed:
> system.Data.Sqlserverce.sqlceException"
> "Create subscription failed (27750 - 8004005)"
> Please help.
Showing posts with label replication. Show all posts
Showing posts with label replication. Show all posts
Thursday, March 22, 2012
Monday, March 19, 2012
Create Replication in Code
Does anybody have any samples of how to enable replication and create
publication using sql-dmo objects? I am trying to create replication
in a program and all I can find are ways to create the subscription.
Anything that might get me started would be great.
Thanks.
Shane Lim
On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:
>Does anybody have any samples of how to enable replication and create
>publication using sql-dmo objects? I am trying to create replication
>in a program and all I can find are ways to create the subscription.
>Anything that might get me started would be great.
>Thanks.
>Shane Lim
Edit I am tring to do Merge Replication for Pull Subscriptions.
|||On Tue, 01 Feb 2005 11:03:52 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:
>On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
>wrote:
>
>Edit I am tring to do Merge Replication for Pull Subscriptions.
Ok I got it working. Thanks you very much Paul Ibison!!
'Paul Ibison is our Savior!!!! From
http://www.mcse.ms/archive95-2004-7-899774.html
Now all I need is a way to get a list of the current publications. So
I can give my user a list to select from to replicate too.
|||Shane,
my special powers might not be enough here - this is a
script for transactional, while you're after a merge one.
I have some similar scripts on
http://www.replicationanswers.com/Scripts.htm but they
aren't merge either. I think Hilary has one for merge - I
seem to remember him posting one up fairly recently. He's
the one.
Rgds,
Paul
|||On Wed, 2 Feb 2005 02:22:46 -0800, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Shane,
>my special powers might not be enough here - this is a
>script for transactional, while you're after a merge one.
>I have some similar scripts on
>http://www.replicationanswers.com/Scripts.htm but they
>aren't merge either. I think Hilary has one for merge - I
>seem to remember him posting one up fairly recently. He's
>the one.
>Rgds,
>Paul
Paul Actually it was quite simple to create the merge publication from
your transactional example. I pretty much just changed all the objects
to there equivilent in the merge objects. It created a publication for
me just fine. I am doing subscriptions today but in my initial testing
it seems to work just Dandy. Although I am sure I will be purchasing
the not while surfs up book as soon as its available. But since the
beta is due before then I will just have to make due like so. Thanks
again.
Shane Lim
publication using sql-dmo objects? I am trying to create replication
in a program and all I can find are ways to create the subscription.
Anything that might get me started would be great.
Thanks.
Shane Lim
On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:
>Does anybody have any samples of how to enable replication and create
>publication using sql-dmo objects? I am trying to create replication
>in a program and all I can find are ways to create the subscription.
>Anything that might get me started would be great.
>Thanks.
>Shane Lim
Edit I am tring to do Merge Replication for Pull Subscriptions.
|||On Tue, 01 Feb 2005 11:03:52 -0700, Shane Lim <gslim@.blizzardice.com>
wrote:
>On Tue, 01 Feb 2005 10:57:58 -0700, Shane Lim <gslim@.blizzardice.com>
>wrote:
>
>Edit I am tring to do Merge Replication for Pull Subscriptions.
Ok I got it working. Thanks you very much Paul Ibison!!
'Paul Ibison is our Savior!!!! From
http://www.mcse.ms/archive95-2004-7-899774.html
Now all I need is a way to get a list of the current publications. So
I can give my user a list to select from to replicate too.
|||Shane,
my special powers might not be enough here - this is a
script for transactional, while you're after a merge one.
I have some similar scripts on
http://www.replicationanswers.com/Scripts.htm but they
aren't merge either. I think Hilary has one for merge - I
seem to remember him posting one up fairly recently. He's
the one.
Rgds,
Paul
|||On Wed, 2 Feb 2005 02:22:46 -0800, "Paul Ibison"
<Paul.Ibison@.Pygmalion.Com> wrote:
>Shane,
>my special powers might not be enough here - this is a
>script for transactional, while you're after a merge one.
>I have some similar scripts on
>http://www.replicationanswers.com/Scripts.htm but they
>aren't merge either. I think Hilary has one for merge - I
>seem to remember him posting one up fairly recently. He's
>the one.
>Rgds,
>Paul
Paul Actually it was quite simple to create the merge publication from
your transactional example. I pretty much just changed all the objects
to there equivilent in the merge objects. It created a publication for
me just fine. I am doing subscriptions today but in my initial testing
it seems to work just Dandy. Although I am sure I will be purchasing
the not while surfs up book as soon as its available. But since the
beta is due before then I will just have to make due like so. Thanks
again.
Shane Lim
Create replication for SQL2000
Hi,
I've a SQL2000 server with an instance.
I wanna replicate this istance in another server on my lan.
How can I do this?
Thanks
We'd need to know a lot more to fully understand the requirements, but for
creating and maintaining a copy of user databases, have a look in BOL for log
shipping.
Cheers,
Paul Ibison
I've a SQL2000 server with an instance.
I wanna replicate this istance in another server on my lan.
How can I do this?
Thanks
We'd need to know a lot more to fully understand the requirements, but for
creating and maintaining a copy of user databases, have a look in BOL for log
shipping.
Cheers,
Paul Ibison
Create Publication Wizard bug
This appears to be a bug in SQL Server 2000 Replication, specifically in the "Create Publication Wizard".
In Enterprse Manger right-click on the Replication\Publications folder and select "New Publication...". Select "Next", any database, transactional publication/"Next","Next".
In the "Specify Articles" screen select "Article Defaults..." and "Table Articles/"OK" to get to the "Default Table Article Properties" screen.
On the "Commands" tab when replacing any of the 3 commands (INSERT, UPDATE and/or DELETE) with a stored procedure call, whatever you enter does not get saved or passed to the "Table Articles" screen for any table that you want to publish. You have to go i
n to each individual table articles property screen to modify the defaults.
Is this a bug or is there something else that you have to do to save the article defaults?
Hi,
Thanks for using Newsgroup.
From your descriptions, I understood that even you have change the SP name
in Default Article, Default SP name will still be created and you have to
change them individually.Have I understood you? If there is anything I
misunderstood, please feel free to let me konw
Based on my knowledge, it is a known issue for us and it is by design. From
Books Online, we could find the descriptions like this:
"Specify that no action will be taken at any Subscriber. Transactions of
that type are not replicated. For example, if you select Replace DELETE
statements with this stored procedure and enter NONE, DELETE statements are
not replicated for that article."
If you UNCHECK the "Use SP" option, then updates are made using TSQL
commands
instead of SP. To not replicate use keword NONE.
I am sorry for the inconvenience you may meet and thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Michael,
Thank you for your timely response.
Yes you understand me correctly, and if I understand you it means that I have to change the SP settings on each individual article and setting the defaults really doesn't do anything.
I may have close to 200+ articles that need to have the same settings and from what you are saying I will have to change all 200+ of them when I should be able to set the default and have that setting propagate to the other articles.
Is there a patch or hotfix that I can apply to fix this?
Is this an issue that I should raise as a service call to Microsoft?
I can’t to believe that this problem is listed as “functions as designed”; it just doesn’t work.
Thanks,
Keith B
|||Hi Keith,
I am sorry for the inconvenience you may meet, However, as I have said, it
is an known issue by desing and I don't think we will give hotfix pr patch
for that.
However, I think there is a more complicated one as a workaround. You could
try to use T-SQL statements make a replication. You should create a
replication scripts and then add what you need manually, which I think is
more complex than SQL Server Enterprise Manager. More detailed information
for this could be found at:
Scripting Replication
http://msdn.microsoft.com/library/de...us/replsql/rep
limpl_9sby.asp
Replication Stored Procedures
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_repl_6vad.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi Keith,
I am so sorry for the inconvience you may meet again
I'd recommend that you forward the recommendation to the Microsoft Wish
Program:
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed later in this article.
Let us know how we can improve our products. Product Enhancement
suggestions can include:
Improvements on existing products.
Suggestions for additional features.
Ways to make products easier to use.
All product enhancement suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
In Internet Explorer 6, click Send Feedback on the Help menu and then click
the link in the Product Suggestion section of the page that appears.
In Windows XP, click Help and Support on the Start menu. Click Send your
feedback to Microsoft, and then fill out the Product Suggestion page that
appears.
Visit the following Microsoft Web site:
http://www.microsoft.com/ms.htm
Click Microsoft.com Guide in the upper-right corner of the page and then
click Contact Us . Click the link in the Product Suggestion section of the
page that appears.
Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.
E-mail - To send comments or suggestions via e-mail, use the following
Microsoft Wish Program e-mail address, mswish@.microsoft.com.
FAX - To send comments or suggestions via FAX, use the following Microsoft
FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.
US Mail - To send comments or suggestions via US Mail, use the following
Microsoft mailing address:
Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team,
classified for easy access, and routed to the product or service team to
drive Microsoft product and/or service improvements. Because we receive an
abundance of suggestions (over 69,000 suggestions a year!) we can't
guarantee that each request makes it into a final product or service. But
we can tell you that each suggestion has been received and is being
reviewed by the team that is most capable of addressing it.
All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
Thank you for your patience and cooperation again. If you have any
questions or concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
In Enterprse Manger right-click on the Replication\Publications folder and select "New Publication...". Select "Next", any database, transactional publication/"Next","Next".
In the "Specify Articles" screen select "Article Defaults..." and "Table Articles/"OK" to get to the "Default Table Article Properties" screen.
On the "Commands" tab when replacing any of the 3 commands (INSERT, UPDATE and/or DELETE) with a stored procedure call, whatever you enter does not get saved or passed to the "Table Articles" screen for any table that you want to publish. You have to go i
n to each individual table articles property screen to modify the defaults.
Is this a bug or is there something else that you have to do to save the article defaults?
Hi,
Thanks for using Newsgroup.
From your descriptions, I understood that even you have change the SP name
in Default Article, Default SP name will still be created and you have to
change them individually.Have I understood you? If there is anything I
misunderstood, please feel free to let me konw
Based on my knowledge, it is a known issue for us and it is by design. From
Books Online, we could find the descriptions like this:
"Specify that no action will be taken at any Subscriber. Transactions of
that type are not replicated. For example, if you select Replace DELETE
statements with this stored procedure and enter NONE, DELETE statements are
not replicated for that article."
If you UNCHECK the "Use SP" option, then updates are made using TSQL
commands
instead of SP. To not replicate use keword NONE.
I am sorry for the inconvenience you may meet and thank you for your
patience and cooperation. If you have any questions or concerns, don't
hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Michael,
Thank you for your timely response.
Yes you understand me correctly, and if I understand you it means that I have to change the SP settings on each individual article and setting the defaults really doesn't do anything.
I may have close to 200+ articles that need to have the same settings and from what you are saying I will have to change all 200+ of them when I should be able to set the default and have that setting propagate to the other articles.
Is there a patch or hotfix that I can apply to fix this?
Is this an issue that I should raise as a service call to Microsoft?
I can’t to believe that this problem is listed as “functions as designed”; it just doesn’t work.
Thanks,
Keith B
|||Hi Keith,
I am sorry for the inconvenience you may meet, However, as I have said, it
is an known issue by desing and I don't think we will give hotfix pr patch
for that.
However, I think there is a more complicated one as a workaround. You could
try to use T-SQL statements make a replication. You should create a
replication scripts and then add what you need manually, which I think is
more complex than SQL Server Enterprise Manager. More detailed information
for this could be found at:
Scripting Replication
http://msdn.microsoft.com/library/de...us/replsql/rep
limpl_9sby.asp
Replication Stored Procedures
http://msdn.microsoft.com/library/de...us/tsqlref/ts_
sp_repl_6vad.asp
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Hi Keith,
I am so sorry for the inconvience you may meet again
I'd recommend that you forward the recommendation to the Microsoft Wish
Program:
Microsoft offers several ways for you to send comments or suggestions about
Microsoft products. If you have suggestions for product enhancements that
you would like to see in future versions of Microsoft products, please
contact us using one of the methods listed later in this article.
Let us know how we can improve our products. Product Enhancement
suggestions can include:
Improvements on existing products.
Suggestions for additional features.
Ways to make products easier to use.
All product enhancement suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
World Wide Web - To send a comment or suggestion via the Web, use one of
the following methods:
In Internet Explorer 6, click Send Feedback on the Help menu and then click
the link in the Product Suggestion section of the page that appears.
In Windows XP, click Help and Support on the Start menu. Click Send your
feedback to Microsoft, and then fill out the Product Suggestion page that
appears.
Visit the following Microsoft Web site:
http://www.microsoft.com/ms.htm
Click Microsoft.com Guide in the upper-right corner of the page and then
click Contact Us . Click the link in the Product Suggestion section of the
page that appears.
Visit the following Microsoft Product Feedback Web site
http://register.microsoft.com/mswish/suggestion.asp
and then complete and submit the form.
E-mail - To send comments or suggestions via e-mail, use the following
Microsoft Wish Program e-mail address, mswish@.microsoft.com.
FAX - To send comments or suggestions via FAX, use the following Microsoft
FAX number, (425) 936-7329.
NOTE : Address the FAX to the attention of the Microsoft Wish Program.
US Mail - To send comments or suggestions via US Mail, use the following
Microsoft mailing address:
Microsoft Corporation
Attn. Microsoft Wish Program
One Microsoft Way
Redmond, WA 98052-6399
MORE INFORMATION
Each product suggestion is read by a member of our product feedback team,
classified for easy access, and routed to the product or service team to
drive Microsoft product and/or service improvements. Because we receive an
abundance of suggestions (over 69,000 suggestions a year!) we can't
guarantee that each request makes it into a final product or service. But
we can tell you that each suggestion has been received and is being
reviewed by the team that is most capable of addressing it.
All product or service suggestions received become the sole property of
Microsoft. Should a suggestion be implemented, Microsoft is under no
obligation to provide compensation.
Thank you for your patience and cooperation again. If you have any
questions or concerns, don't hesitate to let me know.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Sunday, March 11, 2012
Create or Change Merge Articles
We are setting up a merge replication with laptops as subscribers. I read
an article about altering columns in a table but want to verify the correct
sequence of events to add or change articles after Publisher is created.
For a new table:
CREATE TABLE command
sp_addarticle
For a revised table, view or stored proc:
sp_droparticle
DROP ...
CREATE ...
sp_addarticle
For removing table, view or stored proc:
sp_droparticle
DROP ...
Also, I'm not real clear on where or when the snapshot needs to be
re-created. Does this have to be done before the new, changed or deleted
articles will get to the subscribers? Thank you.
David
David,
please take a look at this : http://www.replicationanswers.com/AddColumn.asp
When adding a table to a publication, you'll need to run the snapshot agent.
For TR this'll create the new article only in the snapshot. For merge
this'll create the whole snapshot. In either case only the new table will be
synchronised.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I read this, but no mention of what to do with a VIEW. There is a
sp_repladdcolumn but I think that only applies to TABLE objects. If that is
true, then do I have to drop the article, etc. as I mentioned? Thanks.
p.s. I only care about MERGE replication.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
> David,
> please take a look at this :
> http://www.replicationanswers.com/AddColumn.asp
> When adding a table to a publication, you'll need to run the snapshot
> agent. For TR this'll create the new article only in the snapshot. For
> merge this'll create the whole snapshot. In either case only the new table
> will be synchronised.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I don't replicate any other objects other than tables (and occasionally the
execution of stored procedures).
Normally schema changes on the other objects aren't replicated even when you
do a sp_refreshpublication. For UNC subscribers I use sp_addscriptexec, for
FTP subscriber I use ADO.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
> OK, I read this, but no mention of what to do with a VIEW. There is a
> sp_repladdcolumn but I think that only applies to TABLE objects. If that
> is true, then do I have to drop the article, etc. as I mentioned? Thanks.
> p.s. I only care about MERGE replication.
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
>
|||David,
there isn't really a distinction between tables and views - they're all
articles.
Actually, like Hilary, I don't have views mixed with other articles in a
publication. I sometimes use sp_addscriptexec but usually isolate the views
into another publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||damn you're good
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O0%23EGdqEGHA.1028@.TK2MSFTNGP11.phx.gbl...
> David,
> there isn't really a distinction between tables and views - they're all
> articles.
> Actually, like Hilary, I don't have views mixed with other articles in a
> publication. I sometimes use sp_addscriptexec but usually isolate the
> views into another publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I had a good mentor
|||
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBsuvYtEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>I had a good mentor
>
|||I am new at replication so I don't understand this. Doesn't the subscriber
(in this case the laptops) need to have the views and stored procs in order
to run the applications that I have with links to the views and using the
stored procedures? Or are they always part of the snapshot (or ?) that
the subscriber gets normally? I will only make changes to the views and
stored procedures at the Publisher, not at the Subscribers. Thank you for
any help on this. Let me know if my assumption was wrong.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I don't replicate any other objects other than tables (and occasionally the
>execution of stored procedures).
> Normally schema changes on the other objects aren't replicated even when
> you do a sp_refreshpublication. For UNC subscribers I use
> sp_addscriptexec, for FTP subscriber I use ADO.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
>
|||I am a little confused by your question. Merge replication will put in place
on the subscriber the procs and views it needs, sync or no sync
subscription.
If your application which will be accessing the subscriber database needs
views, procs, etc you are responsible for putting these in place. You can
use replication, a post snapshot command, sp_addscriptexec (for unc deployed
subscribers only), or another manual method. My least favorite way to do
this is using replication because of dependency issues and having the
publication pick up changes even after you refresh the publication.
So if you replicate a proc's schema, and you reinitialize the publication,
it won't replicate the changes to the proc- at least this was the case last
time I checked.
If I have misread your question please post back.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OLUBtp6EGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am new at replication so I don't understand this. Doesn't the subscriber
>(in this case the laptops) need to have the views and stored procs in order
>to run the applications that I have with links to the views and using the
>stored procedures? Or are they always part of the snapshot (or ?) that
>the subscriber gets normally? I will only make changes to the views and
>stored procedures at the Publisher, not at the Subscribers. Thank you for
>any help on this. Let me know if my assumption was wrong.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>
an article about altering columns in a table but want to verify the correct
sequence of events to add or change articles after Publisher is created.
For a new table:
CREATE TABLE command
sp_addarticle
For a revised table, view or stored proc:
sp_droparticle
DROP ...
CREATE ...
sp_addarticle
For removing table, view or stored proc:
sp_droparticle
DROP ...
Also, I'm not real clear on where or when the snapshot needs to be
re-created. Does this have to be done before the new, changed or deleted
articles will get to the subscribers? Thank you.
David
David,
please take a look at this : http://www.replicationanswers.com/AddColumn.asp
When adding a table to a publication, you'll need to run the snapshot agent.
For TR this'll create the new article only in the snapshot. For merge
this'll create the whole snapshot. In either case only the new table will be
synchronised.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||OK, I read this, but no mention of what to do with a VIEW. There is a
sp_repladdcolumn but I think that only applies to TABLE objects. If that is
true, then do I have to drop the article, etc. as I mentioned? Thanks.
p.s. I only care about MERGE replication.
David
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
> David,
> please take a look at this :
> http://www.replicationanswers.com/AddColumn.asp
> When adding a table to a publication, you'll need to run the snapshot
> agent. For TR this'll create the new article only in the snapshot. For
> merge this'll create the whole snapshot. In either case only the new table
> will be synchronised.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I don't replicate any other objects other than tables (and occasionally the
execution of stored procedures).
Normally schema changes on the other objects aren't replicated even when you
do a sp_refreshpublication. For UNC subscribers I use sp_addscriptexec, for
FTP subscriber I use ADO.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
> OK, I read this, but no mention of what to do with a VIEW. There is a
> sp_repladdcolumn but I think that only applies to TABLE objects. If that
> is true, then do I have to drop the article, etc. as I mentioned? Thanks.
> p.s. I only care about MERGE replication.
> David
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:uswWphhEGHA.336@.TK2MSFTNGP14.phx.gbl...
>
|||David,
there isn't really a distinction between tables and views - they're all
articles.
Actually, like Hilary, I don't have views mixed with other articles in a
publication. I sometimes use sp_addscriptexec but usually isolate the views
into another publication.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||damn you're good
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:O0%23EGdqEGHA.1028@.TK2MSFTNGP11.phx.gbl...
> David,
> there isn't really a distinction between tables and views - they're all
> articles.
> Actually, like Hilary, I don't have views mixed with other articles in a
> publication. I sometimes use sp_addscriptexec but usually isolate the
> views into another publication.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
|||I had a good mentor
|||
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:OBsuvYtEGHA.2380@.TK2MSFTNGP12.phx.gbl...
>I had a good mentor
>
|||I am new at replication so I don't understand this. Doesn't the subscriber
(in this case the laptops) need to have the views and stored procs in order
to run the applications that I have with links to the views and using the
stored procedures? Or are they always part of the snapshot (or ?) that
the subscriber gets normally? I will only make changes to the views and
stored procedures at the Publisher, not at the Subscribers. Thank you for
any help on this. Let me know if my assumption was wrong.
David
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>I don't replicate any other objects other than tables (and occasionally the
>execution of stored procedures).
> Normally schema changes on the other objects aren't replicated even when
> you do a sp_refreshpublication. For UNC subscribers I use
> sp_addscriptexec, for FTP subscriber I use ADO.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> Looking for a FAQ on Indexing Services/SQL FTS
> http://www.indexserverfaq.com
> "David Chase" <dlchase@.lifetimeinc.com> wrote in message
> news:%23y4s6NjEGHA.1816@.TK2MSFTNGP11.phx.gbl...
>
|||I am a little confused by your question. Merge replication will put in place
on the subscriber the procs and views it needs, sync or no sync
subscription.
If your application which will be accessing the subscriber database needs
views, procs, etc you are responsible for putting these in place. You can
use replication, a post snapshot command, sp_addscriptexec (for unc deployed
subscribers only), or another manual method. My least favorite way to do
this is using replication because of dependency issues and having the
publication pick up changes even after you refresh the publication.
So if you replicate a proc's schema, and you reinitialize the publication,
it won't replicate the changes to the proc- at least this was the case last
time I checked.
If I have misread your question please post back.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"David Chase" <dlchase@.lifetimeinc.com> wrote in message
news:OLUBtp6EGHA.1124@.TK2MSFTNGP10.phx.gbl...
>I am new at replication so I don't understand this. Doesn't the subscriber
>(in this case the laptops) need to have the views and stored procs in order
>to run the applications that I have with links to the views and using the
>stored procedures? Or are they always part of the snapshot (or ?) that
>the subscriber gets normally? I will only make changes to the views and
>stored procedures at the Publisher, not at the Subscribers. Thank you for
>any help on this. Let me know if my assumption was wrong.
> David
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:esi%234qlEGHA.2300@.TK2MSFTNGP15.phx.gbl...
>
Wednesday, March 7, 2012
Create local snapshot replication for sql 2005 failed
I tried to create a local snapshot replication from data A to database
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you make the admin account( 'DB4\Administrator' ) part of the sysadmin
role on the publisher?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168789205.120731.163890@.51g2000cwl.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
> Is there a detailed step-by=step guide to create a snapshot
> replication?
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>
B for sql 2005. I followed the wizard and it was created successfully.
But,nothing written to the replication folder and the job failed.
I manually executed the sqls and it always failed on
sp_addpublication_snapshot and the error was:
'DB4\Administrator' is a member of sysadmin server role and cannot be
granted to or revoked from the proxy. Members of sysadmin server role
are allowed to use any proxy.
I log in to windows 2003 as administrator and the replication account
id dbsnap. What I have to do to avoid the error?
Is there a detailed step-by=step guide to create a snapshot
replication?
Can someone provide a set of sqls that I can just use to create a local
(or remote) snapshot?
Thanks,
Andy
The scripts are:
use [T2]
exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
@.value = N'true'
GO
-- Adding the snapshot publication
use [T2]
exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
publication of
database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
@.retention = 0,
@.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
N'true',
@.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
@.compress_snapshot =
N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
@.allow_subscription_copy = N'false',
@.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
= N'active',
@.independent_agent = N'true', @.immediate_sync = N'true',
@.allow_sync_tran = N'false',
@.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
@.allow_dts = N'false',
@.replicate_ddl = 1
GO
exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
1,
@.frequency_interval = 0, @.frequency_relative_interval = 0,
@.frequency_recurrence_factor = 0,
@.frequency_subday = 0, @.frequency_subday_interval = 0,
@.active_start_time_of_day = 0,
@.active_end_time_of_day = 235959, @.active_start_date = 0,
@.active_end_date = 0, @.job_login =
N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
@.publisher_login =
N'sa', @.publisher_password = N'chang5911'
use [T2]
exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
@.source_owner =
N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
@.description = null,
@.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
0x000000000803509D,
@.identityrangemanagementoption = N'manual', @.destination_table =
N'RETURN_REASON',
@.destination_owner = N'dbo', @.vertical_partition = N'false'
GO
Can you make the admin account( 'DB4\Administrator' ) part of the sysadmin
role on the publisher?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"AH" <hhhsu7a@.yahoo.com> wrote in message
news:1168789205.120731.163890@.51g2000cwl.googlegro ups.com...
>I tried to create a local snapshot replication from data A to database
> B for sql 2005. I followed the wizard and it was created successfully.
> But,nothing written to the replication folder and the job failed.
> I manually executed the sqls and it always failed on
> sp_addpublication_snapshot and the error was:
> 'DB4\Administrator' is a member of sysadmin server role and cannot be
> granted to or revoked from the proxy. Members of sysadmin server role
> are allowed to use any proxy.
> I log in to windows 2003 as administrator and the replication account
> id dbsnap. What I have to do to avoid the error?
> Is there a detailed step-by=step guide to create a snapshot
> replication?
> Can someone provide a set of sqls that I can just use to create a local
> (or remote) snapshot?
> Thanks,
> Andy
>
> The scripts are:
> use [T2]
> exec sp_replicationdboption @.dbname = N'T2', @.optname = N'publish',
> @.value = N'true'
> GO
> -- Adding the snapshot publication
> use [T2]
> exec sp_addpublication @.publication = N'T2', @.description = N'Snapshot
> publication of
> database ''T2'' from Publisher ''DB4''.', @.sync_method = N'native',
> @.retention = 0,
> @.allow_push = N'true', @.allow_pull = N'true', @.allow_anonymous =
> N'true',
> @.enabled_for_internet = N'false', @.snapshot_in_defaultfolder = N'true',
> @.compress_snapshot =
> N'false', @.ftp_port = 21, @.ftp_login = N'anonymous',
> @.allow_subscription_copy = N'false',
> @.add_to_active_directory = N'false', @.repl_freq = N'snapshot', @.status
> = N'active',
> @.independent_agent = N'true', @.immediate_sync = N'true',
> @.allow_sync_tran = N'false',
> @.autogen_sync_procs = N'false', @.allow_queued_tran = N'false',
> @.allow_dts = N'false',
> @.replicate_ddl = 1
> GO
>
> exec sp_addpublication_snapshot @.publication = N'T2', @.frequency_type =
> 1,
> @.frequency_interval = 0, @.frequency_relative_interval = 0,
> @.frequency_recurrence_factor = 0,
> @.frequency_subday = 0, @.frequency_subday_interval = 0,
> @.active_start_time_of_day = 0,
> @.active_end_time_of_day = 235959, @.active_start_date = 0,
> @.active_end_date = 0, @.job_login =
> N'db4\dbsnap', @.job_password = N'wenhua', @.publisher_security_mode = 0,
> @.publisher_login =
> N'sa', @.publisher_password = N'chang5911'
>
> use [T2]
> exec sp_addarticle @.publication = N'T2', @.article = N'RETURN_REASON',
> @.source_owner =
> N'dbo', @.source_object = N'RETURN_REASON', @.type = N'logbased',
> @.description = null,
> @.creation_script = null, @.pre_creation_cmd = N'drop', @.schema_option =
> 0x000000000803509D,
> @.identityrangemanagementoption = N'manual', @.destination_table =
> N'RETURN_REASON',
> @.destination_owner = N'dbo', @.vertical_partition = N'false'
> GO
>
Tuesday, February 14, 2012
create database command in trigger
Hi,
As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.
I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.
I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger
CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'
Thanks,
IanPlease explain something more about your process|||Hi,
The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.
I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger
declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt
I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.
Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.
I hope this is some use.
Ian
As part of setting up automated replication between two servers, I need an insert trigger on a table in a database on server 1 to run a 'create database xxx' command on server 2. Once I've got that I'm sorted.
I tried using linked servers but didn't get anywhere. Finally, I tried creating a trigger on server 1 which ran a dts package (the dts package contained the SQL to create the database on server 2). The dts pacakge ran on its own (I ran it using dtsrun), but not as part of the trigger.
I know that SQL server doesn't support 'create database' commands in triggers, but I would have thought the dts approach would have got around that. Any suggestions? Here's my trigger
CREATE TRIGGER dblist_trigger
ON dblist
FOR INSERT
AS
commit work
EXEC master..xp_cmdshell 'dtsrun /s mbuksqltst03 /u sa /s /n createdb'
Thanks,
IanPlease explain something more about your process|||Hi,
The application I'm working with uses SQL Server and creates databases as part of its operation. So in essence, I'm trying to replicate an entire server rather than just a particular database. I have a script which will create a full set of replication objects for a given database. The problem I have is that when a database is created on the main server, I can't automatically create a blank database on the replicated server to run my replication objects script against.
I've got the replicated server to maintain a list of databases on the main server (a table called dblist - updated by a trigger on the main server). What I was trying to do was create some sort of trigger which will run a create database command when the dblist table on the replicated server has a row inserted in (indicating a new database has been created on the main server). This syntax works, but not when I use it in a trigger
declare @.sqltxt nvarchar (2000),
@.maxid int,
@.name varchar (256)
set @.maxid=(select max(id) from dblist)
set @.name=(select name from dblist where id=@.maxid)
set @.sqltxt=(select 'create database '+ @.name)
EXEC sp_executesql @.sqlTxt
I have even tried putting this sytax in a separate stored procedure and as a T-SQL object in a dts package. But I still can't get it triggered automatically.
Of course, if there is a more elegant way of setting up the replication, I'm open to suggestions.
I hope this is some use.
Ian
Subscribe to:
Posts (Atom)