I am using Replication wizard and created Publisher with
name "Services3 and Subscriber is HOffice with
Subscription database is GinFactory1 and using Windows
authentication Mode. Subscription is Push Subscription.
It create sanapshot and also transfer data in GinFactory1
as soon as Wizard has been completed. But when i try to
synchronize Subscription using option start sunchronizing
it returns error "Publication is invalid" .What can be
reason of it?Please give me detail description if u have.
Thanks in advance
Normally the publication is invalid error message means that your snapshot
files are too old. Can you regenerate your snapshot (right click on your
snapshot agent and select start agent), and then restart your distribution
agent?
You may get a message telling you the no snapshot was generated as
subscriptions needed reinitialization.
If you get this message right click on your publication, publication
properties, go to the subscribers tab, located your subscriber and click
reinitialize. Then restart your snapshot agent.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Saima" <ucoxk@.yahoo.com> wrote in message
news:3fa701c47308$49893d40$a601280a@.phx.gbl...
> I am using Replication wizard and created Publisher with
> name "Services3 and Subscriber is HOffice with
> Subscription database is GinFactory1 and using Windows
> authentication Mode. Subscription is Push Subscription.
> It create sanapshot and also transfer data in GinFactory1
> as soon as Wizard has been completed. But when i try to
> synchronize Subscription using option start sunchronizing
> it returns error "Publication is invalid" .What can be
> reason of it?Please give me detail description if u have.
> Thanks in advance
|||It is not too old . i just do it after 5 minutes
>--Original Message--
>Normally the publication is invalid error message means
that your snapshot
>files are too old. Can you regenerate your snapshot
(right click on your
>snapshot agent and select start agent), and then restart
your distribution
>agent?
>You may get a message telling you the no snapshot was
generated as
>subscriptions needed reinitialization.
>If you get this message right click on your publication,
publication
>properties, go to the subscribers tab, located your
subscriber and click[vbcol=seagreen]
>reinitialize. Then restart your snapshot agent.
>--
>Hilary Cotter
>Looking for a book on SQL Server replication?
>http://www.nwsu.com/0974973602.html
>
>"Saima" <ucoxk@.yahoo.com> wrote in message
>news:3fa701c47308$49893d40$a601280a@.phx.gbl...
with[vbcol=seagreen]
GinFactory1[vbcol=seagreen]
sunchronizing[vbcol=seagreen]
have.
>
>.
>
|||can you script out your publication and post it here?
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Saima" <ucoxk@.yahoo.com> wrote in message
news:44de01c4733d$c1287570$a601280a@.phx.gbl...[vbcol=seagreen]
> It is not too old . i just do it after 5 minutes
> that your snapshot
> (right click on your
> your distribution
> generated as
> publication
> subscriber and click
> with
> GinFactory1
> sunchronizing
> have.
Showing posts with label created. Show all posts
Showing posts with label created. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Replication creates new triggers?
I have just created a Publication and now most or all of my triggers have
been duplicated and given a name like ins_03FA9EAD39A54B18B6276581AA1F0C42.
Is this doe in replication setup? Thanks.
David
These are merge replication specific triggers. They are created in the
snapshot process on the publisher, and also when the snapshot is applied on
the subscriber.
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:uXbEBbRHGHA.528@.TK2MSFTNGP12.phx.gbl...
>I have just created a Publication and now most or all of my triggers have
>been duplicated and given a name like ins_03FA9EAD39A54B18B6276581AA1F0C42.
>Is this doe in replication setup? Thanks.
> David
>
been duplicated and given a name like ins_03FA9EAD39A54B18B6276581AA1F0C42.
Is this doe in replication setup? Thanks.
David
These are merge replication specific triggers. They are created in the
snapshot process on the publisher, and also when the snapshot is applied on
the subscriber.
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:uXbEBbRHGHA.528@.TK2MSFTNGP12.phx.gbl...
>I have just created a Publication and now most or all of my triggers have
>been duplicated and given a name like ins_03FA9EAD39A54B18B6276581AA1F0C42.
>Is this doe in replication setup? Thanks.
> David
>
Tuesday, March 20, 2012
replication and log shrinking
Since I created a replication (snapshot basic - push) when I'm running the backup log ... with truncate_ only....the size of the transaction log don't lessens. why? (sql2005 without sp)I set to simple the recovery mode and back to full....nothing
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||
I run and and this is the result:
So this is a bug? ( the db running without any sp)
the usedPages value is very large...estimatedpages is very slow....
but not shrinking....|||
check of any open transaction . DBCC Opentran
Madhu
|||thnxI run and and this is the result:
Replicated Transaction Information:
Oldest distributed LSN : (0:0:0)
Oldest non-distributed LSN : (4990:4944:1)
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
What can i do with this?|||
From BOL
If neither database_name nor database_id is specified, the default is the current database.
Use DBCC OPENTRAN to determine whether an open transaction exists within the log. When using the BACKUP LOG statement, only the inactive portion of the log can be truncated; an open transaction can cause the log to not truncate completely. In earlier versions of Microsoft? SQL Server?, either all users had to log off or the server had to be shut down and restarted to clear uncommitted transactions from the log. With DBCC OPENTRAN, an open transaction can be identified (by obtaining the system process ID from the sp_who output) and terminated, if necessary.
you need to termintate this open transaction
http://msdn2.microsoft.com/en-us/library/ms182792.aspx
http://www.sql-server-performance.com/dbcc_commands.asp
google it you will fine many
Madhu
|||I stopped the replication (snapshot) and after I could shrink the log....So this is a bug? ( the db running without any sp)
I tried....I re-set the replication and the symptom is occurred again....
so when I set a replication on this db, I can't shrink the transaction log anymore...
Monday, March 12, 2012
Replication and FK Constraint
Let me preface this w/ the fact that I may be stupid...
In trying to test replication in SQL 7 I created a staging database to hold one table (tblStaff) that I would try to replicate into the "live" version of tblStaff in another DB (on the same server).
The "live" version of tblStaff obviously (perhaps) has a number of dependent tables (and stored procedures - which are really only select queries) that rely on the StaffID key. I have turned off the "Enable key for replication" in all the dependent tables and, for good measure, in tblStaff. I then created a publication of the stagingDB tblStaff and a pull subscription on the live DB. But...(snapshot) replication *still* fails w/ an error that dropping the live tblStaff would violate foreign key constraints.
Thanks in advance.What was the error?|||"Could not drop/truncate (depending on the option chosen earlier in the process - tried it both ways) table due to foreign key constraint."
As an (inelegant) workaround I wrote two scripts to drop and add the constraints and then synch. in the middle. It works and the table only has to be replicated every now and again but... I would still like to know the "real" way to do it.|||Use the NOT FOR REPLICATION Option and refer to BOL for more information.|||I already did that.|||May check this Article (http://www.windowsitlibrary.com/Content/77/11/1.html) about do's and dont's.
In trying to test replication in SQL 7 I created a staging database to hold one table (tblStaff) that I would try to replicate into the "live" version of tblStaff in another DB (on the same server).
The "live" version of tblStaff obviously (perhaps) has a number of dependent tables (and stored procedures - which are really only select queries) that rely on the StaffID key. I have turned off the "Enable key for replication" in all the dependent tables and, for good measure, in tblStaff. I then created a publication of the stagingDB tblStaff and a pull subscription on the live DB. But...(snapshot) replication *still* fails w/ an error that dropping the live tblStaff would violate foreign key constraints.
Thanks in advance.What was the error?|||"Could not drop/truncate (depending on the option chosen earlier in the process - tried it both ways) table due to foreign key constraint."
As an (inelegant) workaround I wrote two scripts to drop and add the constraints and then synch. in the middle. It works and the table only has to be replicated every now and again but... I would still like to know the "real" way to do it.|||Use the NOT FOR REPLICATION Option and refer to BOL for more information.|||I already did that.|||May check this Article (http://www.windowsitlibrary.com/Content/77/11/1.html) about do's and dont's.
Replication and distribution: Agents
Hi all replication experts...
I have created transactional replication on my servers (production server and backup server) and created pull subscription at the backup server. On my production server that also is a distributor and publisher there exist two agents, Snapshot agent and Log reader in the Publishers folder under replication Monitor.
There are also another agent with the backup server name and database name: BackServ:DBName having the type Pull and 'No replicated transactions are available' as Last Action. My question is: What is this? Must it be there?
Grateful for answers
Best,
/M
Its hard to say exactly what this is, but it appears to be your distribution agent. You appear to have a pull agent configured on your subscriber.
It is essential for your transactional replication topology. Do not remove it.
Hilary
|||OK, thanks :)Saturday, February 25, 2012
Replication
Hello all,
I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I got
a problem after replication created
Example:
Table1 in DB_A, i tried to add a new data field. but DB_C can't see new data
field after snopshot done
Is any method can synchronizate new scheme and new table to subscriber(DB_C)
automatically?
Thanks in advanced.Schema changes are replicated in sql server 2005 (there are some
restrictions)
In sql server 2000 you can add/drop columns using system stored procedures
(sp_addmergecolumn and sp_dropmergecolumn) so they get replicated.
If this doesnt answer your question, please LMK
MC
"beachboy" <stanley@.javacatz.com> wrote in message
news:OxM2RVWLGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I
> got
> a problem after replication created
> Example:
> Table1 in DB_A, i tried to add a new data field. but DB_C can't see new
> data
> field after snopshot done
> Is any method can synchronizate new scheme and new table to
> subscriber(DB_C)
> automatically?
> Thanks in advanced.
>
>|||Assuming we're talking about SQL 2000, the correct stored procedures to call
are sp_repladdcolumn and sp_repldropcolumn.
Using these procs, there is no need to create a snapshot for the existing
subscribers - the column changes will occur as you synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Urgh.
Thank you for correcting me, it serves me right for not checking before
posting.
My apologies beachboy.
MC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||Thank you for your informations
Sorry that I am not a professional of sql server
Yes. I am using SQL 2000 standard edition. How to I call those procedure
(sp_repladdcolumn and sp_repldropcolumn)'
from DB_A or from DB_C?
How about if new table added?
Thanks in advanced.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||You should execute the procedures on publisher. When you synch databases,
changes will be propagated to the subscriber.
Adding new table to replication means you must generate new snapshot and
re-initialize subscriptions.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your informations
> Sorry that I am not a professional of sql server
> Yes. I am using SQL 2000 standard edition. How to I call those procedure
> (sp_repladdcolumn and sp_repldropcolumn)'
> from DB_A or from DB_C?
> How about if new table added?
> Thanks in advanced.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the existing
>> subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>|||oic
for new scheme modification: execute store procedure, and corrected scheme
will send at next push
for new table on database: set a new "Push New Publcation" and then
re-initialize subscription
but do I need to remove the old snopshot?
Thanks.
"MC" <marko_culo#@.#yahoo#.#com#> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
> You should execute the procedures on publisher. When you synch databases,
> changes will be propagated to the subscriber.
> Adding new table to replication means you must generate new snapshot and
> re-initialize subscriptions.
> MC
> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>|||Adding a new table doesn't require a reinitialization. The new snapshot will
contain just the new table (transactional) or all articles (merge) but in
either case synchronising will just propagate the new table and not all the
other articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||There is a difference between adding an article (table) to the existing
replication (publication) and adding a new publication to the setup. If you
add the article, run the snapshot and then synch if you left the option to
recreate objects. If you dont recreate objects then youll need to do some
more work. LMK if thats the case.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:uZZu0GZLGHA.1124@.TK2MSFTNGP10.phx.gbl...
> oic
> for new scheme modification: execute store procedure, and corrected scheme
> will send at next push
> for new table on database: set a new "Push New Publcation" and then
> re-initialize subscription
> but do I need to remove the old snopshot?
> Thanks.
>
> "MC" <marko_culo#@.#yahoo#.#com#>
> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
>> You should execute the procedures on publisher. When you synch databases,
>> changes will be propagated to the subscriber.
>> Adding new table to replication means you must generate new snapshot and
>> re-initialize subscriptions.
>> MC
>> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
>> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
>> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you
>> synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>>
>
I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I got
a problem after replication created
Example:
Table1 in DB_A, i tried to add a new data field. but DB_C can't see new data
field after snopshot done
Is any method can synchronizate new scheme and new table to subscriber(DB_C)
automatically?
Thanks in advanced.Schema changes are replicated in sql server 2005 (there are some
restrictions)
In sql server 2000 you can add/drop columns using system stored procedures
(sp_addmergecolumn and sp_dropmergecolumn) so they get replicated.
If this doesnt answer your question, please LMK
MC
"beachboy" <stanley@.javacatz.com> wrote in message
news:OxM2RVWLGHA.720@.TK2MSFTNGP14.phx.gbl...
> Hello all,
> I am setup a replication from DB_A(publisher) to DB_C(subscriber), but I
> got
> a problem after replication created
> Example:
> Table1 in DB_A, i tried to add a new data field. but DB_C can't see new
> data
> field after snopshot done
> Is any method can synchronizate new scheme and new table to
> subscriber(DB_C)
> automatically?
> Thanks in advanced.
>
>|||Assuming we're talking about SQL 2000, the correct stored procedures to call
are sp_repladdcolumn and sp_repldropcolumn.
Using these procs, there is no need to create a snapshot for the existing
subscribers - the column changes will occur as you synchronize.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||Urgh.
Thank you for correcting me, it serves me right for not checking before
posting.
My apologies beachboy.
MC
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||Thank you for your informations
Sorry that I am not a professional of sql server
Yes. I am using SQL 2000 standard edition. How to I call those procedure
(sp_repladdcolumn and sp_repldropcolumn)'
from DB_A or from DB_C?
How about if new table added?
Thanks in advanced.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Assuming we're talking about SQL 2000, the correct stored procedures to
> call are sp_repladdcolumn and sp_repldropcolumn.
> Using these procs, there is no need to create a snapshot for the existing
> subscribers - the column changes will occur as you synchronize.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>|||You should execute the procedures on publisher. When you synch databases,
changes will be propagated to the subscriber.
Adding new table to replication means you must generate new snapshot and
re-initialize subscriptions.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
> Thank you for your informations
> Sorry that I am not a professional of sql server
> Yes. I am using SQL 2000 standard edition. How to I call those procedure
> (sp_repladdcolumn and sp_repldropcolumn)'
> from DB_A or from DB_C?
> How about if new table added?
> Thanks in advanced.
>
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the existing
>> subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>|||oic
for new scheme modification: execute store procedure, and corrected scheme
will send at next push
for new table on database: set a new "Push New Publcation" and then
re-initialize subscription
but do I need to remove the old snopshot?
Thanks.
"MC" <marko_culo#@.#yahoo#.#com#> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
> You should execute the procedures on publisher. When you synch databases,
> changes will be propagated to the subscriber.
> Adding new table to replication means you must generate new snapshot and
> re-initialize subscriptions.
> MC
> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>|||Adding a new table doesn't require a reinitialization. The new snapshot will
contain just the new table (transactional) or all articles (merge) but in
either case synchronising will just propagate the new table and not all the
other articles.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)|||There is a difference between adding an article (table) to the existing
replication (publication) and adding a new publication to the setup. If you
add the article, run the snapshot and then synch if you left the option to
recreate objects. If you dont recreate objects then youll need to do some
more work. LMK if thats the case.
MC
"beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
news:uZZu0GZLGHA.1124@.TK2MSFTNGP10.phx.gbl...
> oic
> for new scheme modification: execute store procedure, and corrected scheme
> will send at next push
> for new table on database: set a new "Push New Publcation" and then
> re-initialize subscription
> but do I need to remove the old snopshot?
> Thanks.
>
> "MC" <marko_culo#@.#yahoo#.#com#>
> ¼¶¼g©ó¶l¥ó·s»D:eS2rOAYLGHA.536@.TK2MSFTNGP09.phx.gbl...
>> You should execute the procedures on publisher. When you synch databases,
>> changes will be propagated to the subscriber.
>> Adding new table to replication means you must generate new snapshot and
>> re-initialize subscriptions.
>> MC
>> "beachboy" <jpsteambun@.yahoo.com.hk> wrote in message
>> news:O4DtH8XLGHA.1676@.TK2MSFTNGP09.phx.gbl...
>> Thank you for your informations
>> Sorry that I am not a professional of sql server
>> Yes. I am using SQL 2000 standard edition. How to I call those procedure
>> (sp_repladdcolumn and sp_repldropcolumn)'
>> from DB_A or from DB_C?
>> How about if new table added?
>> Thanks in advanced.
>>
>> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com>
>> ¼¶¼g©ó¶l¥ó·s»D:eg2RR1WLGHA.3164@.TK2MSFTNGP11.phx.gbl...
>> Assuming we're talking about SQL 2000, the correct stored procedures to
>> call are sp_repladdcolumn and sp_repldropcolumn.
>> Using these procs, there is no need to create a snapshot for the
>> existing subscribers - the column changes will occur as you
>> synchronize.
>> Cheers,
>> Paul Ibison SQL Server MVP, www.replicationanswers.com
>> (recommended sql server 2000 replication book:
>> http://www.nwsu.com/0974973602p.html)
>>
>>
>>
>
Replicating to SSCE tables with "create statistics" doesn't works: how to avoid
When merge replicating SQL Server tables with created Statistics, the SSCE
subscription to that table fails. To avoid this problem I manually deleted
the lines with CREATE STATISTICS statements from the *.dri archives that
contains the script for index creation on the snapshot. The *.dri archives
are generated by the Snapshot Agent and uploaded into the IIS that acts as a
replication gateway between SS and SSCE.
My concerns are how safe is doing this?
It's highly recommended in SS to enable statistics for tables. Our database
without them turns to be too slowly. On the other hand when enabling
statistics we can't synchronize with SSCE users. Then this incompatibility
with SSCE when replicating is highly restrictive for the usability of SSCE.
Why it isn't an "official" solution to this problem, when the solution looks
to be so simple? Or may be it is not so simple...
Any comments will be highly appreciated
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
Faustino,
you could use sp_addscriptexec with the SkipError bit set to 1.
This will enable you to apply the create statistics command to all
subscribers regardless of the edition they have.
HTH,
Paul Ibison
subscription to that table fails. To avoid this problem I manually deleted
the lines with CREATE STATISTICS statements from the *.dri archives that
contains the script for index creation on the snapshot. The *.dri archives
are generated by the Snapshot Agent and uploaded into the IIS that acts as a
replication gateway between SS and SSCE.
My concerns are how safe is doing this?
It's highly recommended in SS to enable statistics for tables. Our database
without them turns to be too slowly. On the other hand when enabling
statistics we can't synchronize with SSCE users. Then this incompatibility
with SSCE when replicating is highly restrictive for the usability of SSCE.
Why it isn't an "official" solution to this problem, when the solution looks
to be so simple? Or may be it is not so simple...
Any comments will be highly appreciated
Thanks in advance
Faustino Dina
If my email address starts with two 'f'
drop the first 'f' when mailing me.
Faustino,
you could use sp_addscriptexec with the SkipError bit set to 1.
This will enable you to apply the create statistics command to all
subscribers regardless of the edition they have.
HTH,
Paul Ibison
Monday, February 20, 2012
replicating the resultset of a view
hello all,
I created a view and I want the result set(records) of the view to be replicated to another server. i've tried replicating the view by checking the view in the replication wizard but it seems that only the definition of the view is being replicated. Is there a way that i can fullfill the desired result through replication. That to store the result of the view to a new table in the replication destination. I'm using snapshot replication
thanks in advance
joey
Hi Joey,
I saw that Paul Ibison has already suggested that you publish your view as an indexed view to table article (or the slightly misnamed 'indexed view logbased' type article) on the microsoft.public.sqlserver.replication newsgroup. This is probably the easiest solution to your problem although creating a unique index on your view may impair update performance at your publisher. If your view is defined to return a subset of columns for one of your tables, you may want to publish the underlying table with horizontal\vertical filters that match your view definition. We will also consider to simply allow the scenario that you described in a future release.
Thanks for your feedback.
-Raymond
Replicating Stored Procedure Alterations
Hi,
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry
I've created two publications. One publication contains the tables...the second publication contains that stored procedures that work with the tables of the first publication. In another database I subscribe to the two publications...the table publicati
on first followed by the stored procedure publication. I'd like to now make an alteration to the stored procedure on the publisher using ALTER PROC and have that schema change replicated over to the subscriber. I perform the ALTER PROC on the publisher
but the change does not appear on the subscriber. How can I get the ALTER PROC to take affect on the subscriber?
Thanks
Jerry
have a look at sp_addscriptexec for this
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hillary,
Thanks. There is an item that discusses this to some degree at the SQL Mag site instant doc #26344 but I'm out of town and do not know my login info. Looks like it recommends reinitalization. Which sounds ok for the proc subscription but a default of a
drop for the proc would delete the permissions as well...agree? That doesn't sound like the best solution to me. I had thought of using the on-demand script execution as a workaround. Just wanted to know if I was missing something. Guess not?
Also, looks like you have some useful information in your books on replication. What are the costs and how are they obtainable? By the way, I'm a SQL instructor (in replication class this week). Are you willing to provide me with a copy of both replica
tion books no-fee? If so, I'd be willing to share them as a potential purchasable resource for my students in my replication classes.
Let me know.
Thanks again.
Jerry
|||You don't need to reinitialize when running sp_addscriptexec.
it will merely distribute and execute a script to all of your subscribers.
you can reinitialize if you want, but you don't have to
The book will be available on amazon eventually. It will also be available on nswu.com at some point in time as well. I'll contact you offline about your other request.
It has to be printed first
|||Right...wasn't implying that I needed to reinit when using the sp proc. Was comparing two different approaches to resolve the issue. I think the sp proc is the easiest to implement and work with.
Jerry
Replicating Primary Key value onto the Foreign key value
Hey Guys,
|||
|||Hi,
I am just new to SQL Server. I have just created 2 tables Table1 and Table2. Table1 has a primary key (Table1.ID), which has been set on identity values.Both tables have different columns except the ID columns.
I have also used Table1.ID as foreign key and primary key on Table2 as Table2.ID.
I am just wondering guys if it is possible that when I enter or insert a data on Table1, can the same value (Table1.ID) be instantaneously copied to Table2.ID for the next data entry for Table2.
Please could you show me how this can be done using SQL Server Express 2005.
After you do the insert into table 1
INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)
You can capture the newly created Identity value by doing the following
SET @.Variable = Scope_Identity()
You would then use this value for the ID column when you insert the row in Table2.
|||Thank you very much for your response. Like I expressed I am just new to these, and I hope you do pardon my ignorance as I am still learning.I do follow the first part of your response but kinda clueless from the
'SET @. ..........................
Thanks for your patience.
|||
The SET statement captures the identity created when data was inserted in table 1.
That is the value to use in Table2.ID.
Code Snippet
DECLARE @.Variable <datatype of ID column>
INSERT INTO Table1 (<ColumnList>) VALUES(<ValueList>)
SET @.Variable = Scope_Identity()
INSERT INTO Table2 (ID, <remaining ColumnList>) VALUES(@.Variable, <remaining values>)
|||Thank you very much for your response. I'm just wondering if there is a way the code you provided above can be incorporated into a trigger. Thanks once again.|||
If you want to do it in a trigger, there is a different method to use.
You would put the trigger on table1. Inside that insert trigger, you would use the virtual table named inserted which will contain a row for each row just inserted into table1, including the newly generated identity value for each row.
If you post your table create staements, and a descripttion of what you want to do, we can likely help you in writing the trigger.
|||
Code Snippet
IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL
DROP TRIGGER dbo.Table1InsertTrigger
GO
CREATE TRIGGER dbo.Table1InsertTrigger
ON dbo.Table1
AFTER INSERT
IF ( @.@.ROWCOUNT = 0 )
RETURN
INSERT INTO Table2 (ID, <remaining ColumnList>)
SELECT ID, <remaining ColumnList>
FROM INSERTED
GO
|||Hi,Here is the create statement for the tables.
CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO
CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO
ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO
Thanks for you help.
|||
(Unless I am confused, which does happen) What you are wanting to do doesn't really make sense in SQL Server storage. To do this, you would be creating partial rows. It would likely be a bad practice to automatically create a row with null values that might just end up as a useless row.
What you are describing sounds more of a task that might be done in a user interface, in order to display a new row for a person to edit. If you want to create a row in T1 and T2 simultaneously, you could use a stored procedure and build the rows together in the context of a transaction.
|||Well, Louis. How can I get T1.ID automatically available or copied into T2.ID when I have entered data into in T2.|||Hi,
Here is the create statement for the tables.
CREATE TABLE [T1] (
[ID] IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([T1_ID])
)
GO
CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO
ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO
I still have have not had this to work so far. My problem still remains how can I get T1.ID automatically available or copied into T2.ID, when I have opened the table, T2 for data entry.
Thanks you guys for your help.|||
Here ya go.
But like Louis, I wonder why you're creating a placeholder. I would think the application would have the ID of the associated T1 entry when the user is working on the T2 entry.
Code Snippet
CREATE TABLE [T1] (
[ID] int IDENTITY(1000, 2) NOT NULL,
[NAME] VARCHAR(100),
[SERV_TYPE] VARCHAR(40),
[NWT] VARCHAR(40),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [T1_ID] PRIMARY KEY ([ID])
)
GO
CREATE TABLE [T2] (
[ID] INT NOT NULL,
[CONTACT_TYPE] VARCHAR(1),
[CONTACT_NAME] VARCHAR(40),
[CONTACT_1] VARCHAR(100),
[CONTACT_2] VARCHAR(50),
[ROWVERSION] TIMESTAMP,
CONSTRAINT [PK_T2] PRIMARY KEY ([ID])
)
GO
ALTER TABLE [T2] ADD CONSTRAINT [T1_T2]
FOREIGN KEY ([ID]) REFERENCES [T1] ([ID])
GO
IF OBJECT_ID ('dbo.Table1InsertTrigger', 'TR') IS NOT NULL
DROP TRIGGER dbo.Table1InsertTrigger
GO
CREATE TRIGGER dbo.Table1InsertTrigger
ON dbo.T1
AFTER INSERT
AS
IF ( @.@.ROWCOUNT = 0 )
RETURN
INSERT INTO T2 (ID)
SELECT ID
FROM INSERTED
GO
insert into t1( [NAME] , [SERV_TYPE], [NWT] )
values ('Name1', 'Service1', 'what is nwt?')
select * from t1
select * from t2
Subscribe to:
Posts (Atom)