I've got 2 SQL 2000 servers within my company, and I've recently
changed some transaction replication objects on a SQL 2000
installation, but I now getting:
Error Code:
7137
MessageUPDATETEXT is not allowed because the column is being processed
by a concurrent snapshot and is being replicated to a non-SQL Server
Subscriber or Published in a publication allowing Data Transformation
Services (DTS).
This only happens on the subscriber when I try to insert data into the
publisher. Any ideas why this is happening? More importantly - how do I
fix it?
Thanks in advanceI deleted all the replication objects, rebooted the servers, and then
re-created them - problem was solved!
pinhead wrote:
> I've got 2 SQL 2000 servers within my company, and I've recently
> changed some transaction replication objects on a SQL 2000
> installation, but I now getting:
> Error Code:
> 7137
> MessageUPDATETEXT is not allowed because the column is being processed
> by a concurrent snapshot and is being replicated to a non-SQL Server
> Subscriber or Published in a publication allowing Data Transformation
> Services (DTS).
> This only happens on the subscriber when I try to insert data into the
> publisher. Any ideas why this is happening? More importantly - how do I
> fix it?
> Thanks in advance
Showing posts with label objects. Show all posts
Showing posts with label objects. Show all posts
Tuesday, March 20, 2012
Replication problem
I've got 2 SQL 2000 servers within my company, and I've recently
changed some transaction replication objects on a SQL 2000
installation, but I now getting:
Error Code:
7137
MessageUPDATETEXT is not allowed because the column is being processed
by a concurrent snapshot and is being replicated to a non-SQL Server
Subscriber or Published in a publication allowing Data Transformation
Services (DTS).
This only happens on the subscriber when I try to insert data into the
publisher. Any ideas why this is happening? More importantly - how do I
fix it?
Thanks in advanceI deleted all the replication objects, rebooted the servers, and then
re-created them - problem was solved!
pinhead wrote:
> I've got 2 SQL 2000 servers within my company, and I've recently
> changed some transaction replication objects on a SQL 2000
> installation, but I now getting:
> Error Code:
> 7137
> MessageUPDATETEXT is not allowed because the column is being processed
> by a concurrent snapshot and is being replicated to a non-SQL Server
> Subscriber or Published in a publication allowing Data Transformation
> Services (DTS).
> This only happens on the subscriber when I try to insert data into the
> publisher. Any ideas why this is happening? More importantly - how do I
> fix it?
> Thanks in advance
changed some transaction replication objects on a SQL 2000
installation, but I now getting:
Error Code:
7137
MessageUPDATETEXT is not allowed because the column is being processed
by a concurrent snapshot and is being replicated to a non-SQL Server
Subscriber or Published in a publication allowing Data Transformation
Services (DTS).
This only happens on the subscriber when I try to insert data into the
publisher. Any ideas why this is happening? More importantly - how do I
fix it?
Thanks in advanceI deleted all the replication objects, rebooted the servers, and then
re-created them - problem was solved!
pinhead wrote:
> I've got 2 SQL 2000 servers within my company, and I've recently
> changed some transaction replication objects on a SQL 2000
> installation, but I now getting:
> Error Code:
> 7137
> MessageUPDATETEXT is not allowed because the column is being processed
> by a concurrent snapshot and is being replicated to a non-SQL Server
> Subscriber or Published in a publication allowing Data Transformation
> Services (DTS).
> This only happens on the subscriber when I try to insert data into the
> publisher. Any ideas why this is happening? More importantly - how do I
> fix it?
> Thanks in advance
Friday, March 9, 2012
Replication of SPs/Views/UDFs - Third party tool?
Hi,
I am using merge replication with anonymous pull subscriber. My problem is
getting a way to consistenly replicate database objects (SPs/Views/UDFs).
Replication kind of support it, but then you are not able to modify
(drop/alter) the objects and it doesn't work anyways because it depends on
the sysdependencies information (which is totally messed up).
So, I cannot use replication for that. I have tested DTS and it fails
because sysdependencies doesn't hold dependencies accurately enough.
I am starting to think on getting a third party tool or program my own...
I know I can include before snapshot scripts to create my objects; the
problem is that my database is highly dynamic, ie: objects are
created/modified/dropped too frequently.
Any advice?
Thanks, Jos Araujo.
use sp_addscriptexec to deploy your changes in your procs, views, and other
objects. This will only work for non ftp deployed subscriptions.
Getting dependencies correct is a problem no matter what RDBMS you use.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am using merge replication with anonymous pull subscriber. My problem is
> getting a way to consistenly replicate database objects (SPs/Views/UDFs).
> Replication kind of support it, but then you are not able to modify
> (drop/alter) the objects and it doesn't work anyways because it depends on
> the sysdependencies information (which is totally messed up).
> So, I cannot use replication for that. I have tested DTS and it fails
> because sysdependencies doesn't hold dependencies accurately enough.
> I am starting to think on getting a third party tool or program my own...
> I know I can include before snapshot scripts to create my objects; the
> problem is that my database is highly dynamic, ie: objects are
> created/modified/dropped too frequently.
> Any advice?
> Thanks, Jos Araujo.
>
|||I would do that (use sp_addscriptexec); however there is SQL code being
autocreated by my application.
For instance, there are "rules" that the user defines, that are supposed to
affect the records, those rules are "translated" to stored procedures that
the application creates.
Of course, i could change a lot of things to get it working with
sp_addscriptexec, however, it would really easier to just have an
application to "synchronize" these objects.
Thanks, Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> use sp_addscriptexec to deploy your changes in your procs, views, and
other[vbcol=seagreen]
> objects. This will only work for non ftp deployed subscriptions.
> Getting dependencies correct is a problem no matter what RDBMS you use.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
is[vbcol=seagreen]
(SPs/Views/UDFs).[vbcol=seagreen]
on[vbcol=seagreen]
own...
>
|||This is not an automated approach, but you can put your objects in a
different publication, manually create the snapshot when needed and then
reinit the subscribers to push your changes.
Scott
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
>I would do that (use sp_addscriptexec); however there is SQL code being
> autocreated by my application.
> For instance, there are "rules" that the user defines, that are supposed
> to
> affect the records, those rules are "translated" to stored procedures that
> the application creates.
> Of course, i could change a lot of things to get it working with
> sp_addscriptexec, however, it would really easier to just have an
> application to "synchronize" these objects.
> Thanks, Jos.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> other
> is
> (SPs/Views/UDFs).
> on
> own...
>
|||Thanks...
"Scott Wallace" <scott.wallace@.astyles.com> wrote in message
news:eb6eajlqEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> This is not an automated approach, but you can put your objects in a
> different publication, manually create the snapshot when needed and then
> reinit the subscribers to push your changes.
> Scott
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
that[vbcol=seagreen]
problem[vbcol=seagreen]
depends[vbcol=seagreen]
the
>
I am using merge replication with anonymous pull subscriber. My problem is
getting a way to consistenly replicate database objects (SPs/Views/UDFs).
Replication kind of support it, but then you are not able to modify
(drop/alter) the objects and it doesn't work anyways because it depends on
the sysdependencies information (which is totally messed up).
So, I cannot use replication for that. I have tested DTS and it fails
because sysdependencies doesn't hold dependencies accurately enough.
I am starting to think on getting a third party tool or program my own...
I know I can include before snapshot scripts to create my objects; the
problem is that my database is highly dynamic, ie: objects are
created/modified/dropped too frequently.
Any advice?
Thanks, Jos Araujo.
use sp_addscriptexec to deploy your changes in your procs, views, and other
objects. This will only work for non ftp deployed subscriptions.
Getting dependencies correct is a problem no matter what RDBMS you use.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I am using merge replication with anonymous pull subscriber. My problem is
> getting a way to consistenly replicate database objects (SPs/Views/UDFs).
> Replication kind of support it, but then you are not able to modify
> (drop/alter) the objects and it doesn't work anyways because it depends on
> the sysdependencies information (which is totally messed up).
> So, I cannot use replication for that. I have tested DTS and it fails
> because sysdependencies doesn't hold dependencies accurately enough.
> I am starting to think on getting a third party tool or program my own...
> I know I can include before snapshot scripts to create my objects; the
> problem is that my database is highly dynamic, ie: objects are
> created/modified/dropped too frequently.
> Any advice?
> Thanks, Jos Araujo.
>
|||I would do that (use sp_addscriptexec); however there is SQL code being
autocreated by my application.
For instance, there are "rules" that the user defines, that are supposed to
affect the records, those rules are "translated" to stored procedures that
the application creates.
Of course, i could change a lot of things to get it working with
sp_addscriptexec, however, it would really easier to just have an
application to "synchronize" these objects.
Thanks, Jos.
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> use sp_addscriptexec to deploy your changes in your procs, views, and
other[vbcol=seagreen]
> objects. This will only work for non ftp deployed subscriptions.
> Getting dependencies correct is a problem no matter what RDBMS you use.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:OUZ44UAqEHA.3424@.TK2MSFTNGP12.phx.gbl...
is[vbcol=seagreen]
(SPs/Views/UDFs).[vbcol=seagreen]
on[vbcol=seagreen]
own...
>
|||This is not an automated approach, but you can put your objects in a
different publication, manually create the snapshot when needed and then
reinit the subscribers to push your changes.
Scott
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
>I would do that (use sp_addscriptexec); however there is SQL code being
> autocreated by my application.
> For instance, there are "rules" that the user defines, that are supposed
> to
> affect the records, those rules are "translated" to stored procedures that
> the application creates.
> Of course, i could change a lot of things to get it working with
> sp_addscriptexec, however, it would really easier to just have an
> application to "synchronize" these objects.
> Thanks, Jos.
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:uHPDFJCqEHA.3540@.TK2MSFTNGP11.phx.gbl...
> other
> is
> (SPs/Views/UDFs).
> on
> own...
>
|||Thanks...
"Scott Wallace" <scott.wallace@.astyles.com> wrote in message
news:eb6eajlqEHA.596@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
> This is not an automated approach, but you can put your objects in a
> different publication, manually create the snapshot when needed and then
> reinit the subscribers to push your changes.
> Scott
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23rGZvrjqEHA.2636@.TK2MSFTNGP09.phx.gbl...
that[vbcol=seagreen]
problem[vbcol=seagreen]
depends[vbcol=seagreen]
the
>
Replication of dependent objects...
Hi,
I have been trying to create a publication of all my views in my database.
The problem that i have, when the subscriber tries to apply the snapshot is
that some views are dependent to others views... so, the subscriber fails
(because the dependency has not been created yet).
This is happening in snapshot and merge replication... I know i can
workaround it, by just creating multiple publications (one per each level of
dependencies that i have), however, this adds more tasks to my already long
maintance list of TO-DOs...
Anyone has one idea? Should i just learn to live with this problem?
Thanks, Jos.
replication depends on sysdepends which is not always accurate. You can try
to fix sysdepends, or replication your stored procedures/functions/views
using a post snapshot command.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have been trying to create a publication of all my views in my database.
> The problem that i have, when the subscriber tries to apply the snapshot
is
> that some views are dependent to others views... so, the subscriber fails
> (because the dependency has not been created yet).
> This is happening in snapshot and merge replication... I know i can
> workaround it, by just creating multiple publications (one per each level
of
> dependencies that i have), however, this adds more tasks to my already
long
> maintance list of TO-DOs...
> Anyone has one idea? Should i just learn to live with this problem?
> Thanks, Jos.
>
|||Jos,
I have had the same issue and as Hilary says, the main option is to fix the
dependencies. Correcting the order even applies to a script run after the
snapshot, in the case of views, which (unlike stored procecures) don't use
deferred name resolution.
You might find these articles helpful:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinf...pendencies.asp
HTH,
Paul Ibison
|||Thanks for your answer...
However, what is a "post snapshot command"?
Do you mean to not include my sp/functions/views in the original snapshot,
and then running the scripts to create the objects?
Thanks again... Jos
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> replication depends on sysdepends which is not always accurate. You can
try[vbcol=seagreen]
> to fix sysdepends, or replication your stored procedures/functions/views
> using a post snapshot command.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
database.[vbcol=seagreen]
> is
fails[vbcol=seagreen]
level
> of
> long
>
|||I'll sure check out the link you've provided...
Thanks a lot... Jos.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||If your post snapshot script is jumbled you will get warning messages while
running the script referring to the sysdepends problem, but your
distribution agent won't fail. So it will work.
How do you fix the dependencies on the publisher so the snapshot script is
generated correctly in the first place? AFAIK - there is no way to fix
sysdepends.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||"AFAIK - there is no way to fix sysdepends."
Yeah... i have been looking for a way to recreate the dependencies, but the
only one that I have found is to recreate all objects (which is crazy).
It seems so logical that there should be a SP that recreate the dependencies
information for a given object (the same SP that MSSQL should use to create
that information in the first place)... but either it doesn't exist, or
nobody knows it exists
...
Jos.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If your post snapshot script is jumbled you will get warning messages
while[vbcol=seagreen]
> running the script referring to the sysdepends problem, but your
> distribution agent won't fail. So it will work.
> How do you fix the dependencies on the publisher so the snapshot script is
> generated correctly in the first place? AFAIK - there is no way to fix
> sysdepends.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> the
the[vbcol=seagreen]
use
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||evidently this problem plagues other RDBMS's.
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:O2PhDYtYEHA.4068@.TK2MSFTNGP10.phx.gbl...
> "AFAIK - there is no way to fix sysdepends."
> Yeah... i have been looking for a way to recreate the dependencies, but
the
> only one that I have found is to recreate all objects (which is crazy).
> It seems so logical that there should be a SP that recreate the
dependencies
> information for a given object (the same SP that MSSQL should use to
create[vbcol=seagreen]
> that information in the first place)... but either it doesn't exist, or
> nobody knows it exists...
> Jos.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> while
is[vbcol=seagreen]
fix[vbcol=seagreen]
> the
> use
in
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||right click on your publication, select publication properties, in the
snapshot tab there is an option for pre and post snapshot scripts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for your answer...
> However, what is a "post snapshot command"?
> Do you mean to not include my sp/functions/views in the original snapshot,
> and then running the scripts to create the objects?
> Thanks again... Jos
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> try
> database.
snapshot
> fails
> level
>
|||thanks
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eyBcx1vYEHA.3112@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> right click on your publication, select publication properties, in the
> snapshot tab there is an option for pre and post snapshot scripts.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...
snapshot,[vbcol=seagreen]
can[vbcol=seagreen]
procedures/functions/views[vbcol=seagreen]
> snapshot
already
>
I have been trying to create a publication of all my views in my database.
The problem that i have, when the subscriber tries to apply the snapshot is
that some views are dependent to others views... so, the subscriber fails
(because the dependency has not been created yet).
This is happening in snapshot and merge replication... I know i can
workaround it, by just creating multiple publications (one per each level of
dependencies that i have), however, this adds more tasks to my already long
maintance list of TO-DOs...
Anyone has one idea? Should i just learn to live with this problem?
Thanks, Jos.
replication depends on sysdepends which is not always accurate. You can try
to fix sysdepends, or replication your stored procedures/functions/views
using a post snapshot command.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I have been trying to create a publication of all my views in my database.
> The problem that i have, when the subscriber tries to apply the snapshot
is
> that some views are dependent to others views... so, the subscriber fails
> (because the dependency has not been created yet).
> This is happening in snapshot and merge replication... I know i can
> workaround it, by just creating multiple publications (one per each level
of
> dependencies that i have), however, this adds more tasks to my already
long
> maintance list of TO-DOs...
> Anyone has one idea? Should i just learn to live with this problem?
> Thanks, Jos.
>
|||Jos,
I have had the same issue and as Hilary says, the main option is to fix the
dependencies. Correcting the order even applies to a script run after the
snapshot, in the case of views, which (unlike stored procecures) don't use
deferred name resolution.
You might find these articles helpful:
BUG: Recreating a Table Causes sysdepends to Become Invalid
http://support.microsoft.com/?id=115333
BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
Sp_depends
http://support.microsoft.com/?id=201846
Displaying Dependencies
http://www.microsoft.com/sql/techinf...pendencies.asp
HTH,
Paul Ibison
|||Thanks for your answer...
However, what is a "post snapshot command"?
Do you mean to not include my sp/functions/views in the original snapshot,
and then running the scripts to create the objects?
Thanks again... Jos
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> replication depends on sysdepends which is not always accurate. You can
try[vbcol=seagreen]
> to fix sysdepends, or replication your stored procedures/functions/views
> using a post snapshot command.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:%23lLPLOrYEHA.384@.TK2MSFTNGP10.phx.gbl...
database.[vbcol=seagreen]
> is
fails[vbcol=seagreen]
level
> of
> long
>
|||I'll sure check out the link you've provided...
Thanks a lot... Jos.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||If your post snapshot script is jumbled you will get warning messages while
running the script referring to the sysdepends problem, but your
distribution agent won't fail. So it will work.
How do you fix the dependencies on the publisher so the snapshot script is
generated correctly in the first place? AFAIK - there is no way to fix
sysdepends.
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> Jos,
> I have had the same issue and as Hilary says, the main option is to fix
the
> dependencies. Correcting the order even applies to a script run after the
> snapshot, in the case of views, which (unlike stored procecures) don't use
> deferred name resolution.
> You might find these articles helpful:
> BUG: Recreating a Table Causes sysdepends to Become Invalid
> http://support.microsoft.com/?id=115333
> BUG: Reference to Deferred Object in Stored Procedure Will Not Show in
> Sp_depends
> http://support.microsoft.com/?id=201846
> Displaying Dependencies
>
http://www.microsoft.com/sql/techinf...pendencies.asp
> HTH,
> Paul Ibison
>
>
|||"AFAIK - there is no way to fix sysdepends."
Yeah... i have been looking for a way to recreate the dependencies, but the
only one that I have found is to recreate all objects (which is crazy).
It seems so logical that there should be a SP that recreate the dependencies
information for a given object (the same SP that MSSQL should use to create
that information in the first place)... but either it doesn't exist, or
nobody knows it exists
...Jos.
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> If your post snapshot script is jumbled you will get warning messages
while[vbcol=seagreen]
> running the script referring to the sysdepends problem, but your
> distribution agent won't fail. So it will work.
> How do you fix the dependencies on the publisher so the snapshot script is
> generated correctly in the first place? AFAIK - there is no way to fix
> sysdepends.
> "Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
> news:eJhya5rYEHA.1152@.TK2MSFTNGP09.phx.gbl...
> the
the[vbcol=seagreen]
use
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||evidently this problem plagues other RDBMS's.
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:O2PhDYtYEHA.4068@.TK2MSFTNGP10.phx.gbl...
> "AFAIK - there is no way to fix sysdepends."
> Yeah... i have been looking for a way to recreate the dependencies, but
the
> only one that I have found is to recreate all objects (which is crazy).
> It seems so logical that there should be a SP that recreate the
dependencies
> information for a given object (the same SP that MSSQL should use to
create[vbcol=seagreen]
> that information in the first place)... but either it doesn't exist, or
> nobody knows it exists
...> Jos.
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:%23ehVBrsYEHA.2500@.TK2MSFTNGP09.phx.gbl...
> while
is[vbcol=seagreen]
fix[vbcol=seagreen]
> the
> use
in
>
http://www.microsoft.com/sql/techinf...pendencies.asp
>
|||right click on your publication, select publication properties, in the
snapshot tab there is an option for pre and post snapshot scripts.
Hilary Cotter
Looking for a book on SQL Server replication?
http://www.nwsu.com/0974973602.html
"Jos Araujo" <josea@.mcrinc.com> wrote in message
news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> Thanks for your answer...
> However, what is a "post snapshot command"?
> Do you mean to not include my sp/functions/views in the original snapshot,
> and then running the scripts to create the objects?
> Thanks again... Jos
> "Hilary Cotter" <hilaryk@.att.net> wrote in message
> news:OHgaUorYEHA.2944@.TK2MSFTNGP11.phx.gbl...
> try
> database.
snapshot
> fails
> level
>
|||thanks
"Hilary Cotter" <hilaryk@.att.net> wrote in message
news:eyBcx1vYEHA.3112@.tk2msftngp13.phx.gbl...[vbcol=seagreen]
> right click on your publication, select publication properties, in the
> snapshot tab there is an option for pre and post snapshot scripts.
> --
> Hilary Cotter
> Looking for a book on SQL Server replication?
> http://www.nwsu.com/0974973602.html
>
> "Jos Araujo" <josea@.mcrinc.com> wrote in message
> news:upPuXFsYEHA.1152@.TK2MSFTNGP09.phx.gbl...
snapshot,[vbcol=seagreen]
can[vbcol=seagreen]
procedures/functions/views[vbcol=seagreen]
> snapshot
already
>
Replication Objects are still there.....
I restored a db to a new server, executed the following;
exec sp_removedbreplication 'ADAGE'
go
exec sp_dboption 'ADAGE', 'published', 'FALSE'
go
exec sp_dboption 'ADAGE', 'merge publish', 'FALSE'
go
Yet, the ADAGE db still has views like sync%
Did I miss a step necessary to remove all traces of replication on this db?
Do I have to manually remove these objects?
I want to use this database to replicate to (New Publication / Subscription), but I first want to make sure all traces of previous replication are gone.
JLS
you missed the manual delete step. But you really don't have to do this. When you reinstall replication or recreate a publication it will whack the objects it doesn't need anymore or create new names.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23DNr6Nk2FHA.2816@.tk2msftngp13.phx.gbl...
I restored a db to a new server, executed the following;
exec sp_removedbreplication 'ADAGE'
go
exec sp_dboption 'ADAGE', 'published', 'FALSE'
go
exec sp_dboption 'ADAGE', 'merge publish', 'FALSE'
go
Yet, the ADAGE db still has views like sync%
Did I miss a step necessary to remove all traces of replication on this db?
Do I have to manually remove these objects?
I want to use this database to replicate to (New Publication / Subscription), but I first want to make sure all traces of previous replication are gone.
JLS
|||Ok, great, Thanx!!!!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%234Jhehk2FHA.3416@.tk2msftngp13.phx.gbl...
you missed the manual delete step. But you really don't have to do this. When you reinstall replication or recreate a publication it will whack the objects it doesn't need anymore or create new names.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23DNr6Nk2FHA.2816@.tk2msftngp13.phx.gbl...
I restored a db to a new server, executed the following;
exec sp_removedbreplication 'ADAGE'
go
exec sp_dboption 'ADAGE', 'published', 'FALSE'
go
exec sp_dboption 'ADAGE', 'merge publish', 'FALSE'
go
Yet, the ADAGE db still has views like sync%
Did I miss a step necessary to remove all traces of replication on this db?
Do I have to manually remove these objects?
I want to use this database to replicate to (New Publication / Subscription), but I first want to make sure all traces of previous replication are gone.
JLS
exec sp_removedbreplication 'ADAGE'
go
exec sp_dboption 'ADAGE', 'published', 'FALSE'
go
exec sp_dboption 'ADAGE', 'merge publish', 'FALSE'
go
Yet, the ADAGE db still has views like sync%
Did I miss a step necessary to remove all traces of replication on this db?
Do I have to manually remove these objects?
I want to use this database to replicate to (New Publication / Subscription), but I first want to make sure all traces of previous replication are gone.
JLS
you missed the manual delete step. But you really don't have to do this. When you reinstall replication or recreate a publication it will whack the objects it doesn't need anymore or create new names.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23DNr6Nk2FHA.2816@.tk2msftngp13.phx.gbl...
I restored a db to a new server, executed the following;
exec sp_removedbreplication 'ADAGE'
go
exec sp_dboption 'ADAGE', 'published', 'FALSE'
go
exec sp_dboption 'ADAGE', 'merge publish', 'FALSE'
go
Yet, the ADAGE db still has views like sync%
Did I miss a step necessary to remove all traces of replication on this db?
Do I have to manually remove these objects?
I want to use this database to replicate to (New Publication / Subscription), but I first want to make sure all traces of previous replication are gone.
JLS
|||Ok, great, Thanx!!!!
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message news:%234Jhehk2FHA.3416@.tk2msftngp13.phx.gbl...
you missed the manual delete step. But you really don't have to do this. When you reinstall replication or recreate a publication it will whack the objects it doesn't need anymore or create new names.
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
"JLS" <jlshoop@.hotmail.com> wrote in message news:%23DNr6Nk2FHA.2816@.tk2msftngp13.phx.gbl...
I restored a db to a new server, executed the following;
exec sp_removedbreplication 'ADAGE'
go
exec sp_dboption 'ADAGE', 'published', 'FALSE'
go
exec sp_dboption 'ADAGE', 'merge publish', 'FALSE'
go
Yet, the ADAGE db still has views like sync%
Did I miss a step necessary to remove all traces of replication on this db?
Do I have to manually remove these objects?
I want to use this database to replicate to (New Publication / Subscription), but I first want to make sure all traces of previous replication are gone.
JLS
Subscribe to:
Posts (Atom)