I have several articles in a SQL Server 2000 merge publication where I need to do one of the following:
1. Delete a existing key and its column, and create a new one with a new column.
2. Add a new key using existing columns.
3. Add a new key using a new column to be added.
Since this is merge replication, I cannot drop the articles involved using sp_droparticle and then use sp_addarticle after making the modifications.
The publication has several active subscriptions.
Any suggestions and/or procedures for above 3 items would be very much appreciated.
Thanks.
Bill
Bill,
have a look at these three procedures to do what you require:
sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
For your three cases, something like:
1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
sp_repladdcolumn
2. sp_addscriptexec (alter table x add constraint ...)
3. sp_repladdcolumn
For the sp_addscriptexecs don't forget to do the same process on the
publisher, and be sure to get the syntax correct, otherwise there'll be a
continuous error.
HTH,
Paul Ibison
|||Thanks you, Paul! I will give these solutions a try.
"Paul Ibison" wrote:
> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Having done one of these (my situation calls for nuber 1) would I have to
reinitialise the snapshot?
On Mon, 5 Jul 2004 20:04:21 +0100, Paul Ibison <Paul.Ibison@.Pygmalion.Com>
wrote:
> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn
> ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/
|||"Paul Ibison" wrote:
> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||Paul:
I tried using sp_addscriptexec to test the deletion of an existing key. However,
when the subscriber synchronizes, the script fails to run. It keeps saying the
script file or directory does not exists. However, it clearly does exist in the
path that is shown in the error.
The synchronization takes place over the internet. When I have run sp_addscripexec
I have tried each of the following in specifying the location of the script file on
the publisher/distributor:
-- "C:\<directory>\<sub directory>\<script file name>"
-- "\\<server name>\<share name>\<sub directory>\<script file name>"
-- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
-- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
The last one does not work when I run sp_addscriptexec. However, the first three
do. After sp_addscriptexec runs, it adds the script file to the snapshot location
on the server. I am running sp_addscriptexec at the publisher through a remote
desktop connection.
When the subscriber synchronizes, in the merge agent session details, the
synchronization is successful and data changes are merged, however, one of the
details shows the error that "osql" cannot find the file or directory at:
"\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
string>\<script file name>". The "<server name>" is the name of the
publisher\distributor.
I must be missing some step but I find nothing in BOL that helps. I also searched
Micorsoft.com and used Google to look for the osql error, but find nothing.
Do you or does anyone else have any suggestions or ideas to try?
Thanks in advance.
Bill
"Paul Ibison" wrote:
> Bill,
> have a look at these three procedures to do what you require:
> sp_repladdcolumn, sp_repldropcolumn and sp_addscriptexec.
> For your three cases, something like:
> 1. sp_addscriptexec (alter table x drop constraint y), sp_repldropcolumn ,
> sp_repladdcolumn
> 2. sp_addscriptexec (alter table x add constraint ...)
> 3. sp_repladdcolumn
> For the sp_addscriptexecs don't forget to do the same process on the
> publisher, and be sure to get the syntax correct, otherwise there'll be a
> continuous error.
> HTH,
> Paul Ibison
>
>
|||are you on the network? If you are not connected to the network or the
internet you will get this error.
If you are running workstation or professional you may have exceeded the
number of simultaneous connections for your os.
You may need to issue a net stop server and then a net start server and try
again.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Bill" <Bill@.discussions.microsoft.com> wrote in message
news:43B07A06-4DBC-48C1-A815-E11BA7103C04@.microsoft.com...
> Paul:
> I tried using sp_addscriptexec to test the deletion of an existing key.
However,
> when the subscriber synchronizes, the script fails to run. It keeps
saying the
> script file or directory does not exists. However, it clearly does exist
in the
> path that is shown in the error.
> The synchronization takes place over the internet. When I have run
sp_addscripexec
> I have tried each of the following in specifying the location of the
script file on
> the publisher/distributor:
> -- "C:\<directory>\<sub directory>\<script file name>"
> -- "\\<server name>\<share name>\<sub directory>\<script file name>"
> -- "\\<IP of the server>\<share name>\<sub directory>\<script file name>"
> -- "\\<URL of the server>\<share name>\<sub directory>\<script file name>"
> The last one does not work when I run sp_addscriptexec. However, the
first three
> do. After sp_addscriptexec runs, it adds the script file to the snapshot
location
> on the server. I am running sp_addscriptexec at the publisher through a
remote
> desktop connection.
> When the subscriber synchronizes, in the merge agent session details, the
> synchronization is successful and data changes are merged, however, one of
the
> details shows the error that "osql" cannot find the file or directory at:
> "\\<server name>\C$\<share name>\<snapshots folder>\<some long date & time
> string>\<script file name>". The "<server name>" is the name of the
> publisher\distributor.
> I must be missing some step but I find nothing in BOL that helps. I also
searched
> Micorsoft.com and used Google to look for the osql error, but find
nothing.[vbcol=seagreen]
> Do you or does anyone else have any suggestions or ideas to try?
> Thanks in advance.
> Bill
>
> "Paul Ibison" wrote:
,[vbcol=seagreen]
a[vbcol=seagreen]
