|
|
You're in the
sql server replication
group:Merge replication - schema change. Am I in trouble?
sql server replication:
after update from 2000SP4 to 2005SP2 I noticed two stored procedures having
issue due to changed functionality of the @@IDENTITY vs. SCOPE_INDETITY(). I changed them on the publisher/distributor and regenrated the snapshot before marking subscriptions for reinitialization. Next sync supposed to apply these changes to the subscribers, right ? I had the publication comp level set to 90RTM, ddl changes enabled, etc. - nothing worked unless publication was dropped and recreated. With high number of subscribers and rather active publisher I didn't want to reconfigure my replication setup... and after digging around I found the way to change these 2 sp's directly on the subscribers: disable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE go ALTER PROCEDURE .... go enable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE go Does anyone see a potential future problem with this "solution" ? tia, rafael
Which stored procedures did you modify and what changes did you make to
them? -- 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 [quoted text, click to view] "Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message news:OGgI9AicHHA.648@TK2MSFTNGP04.phx.gbl... > after update from 2000SP4 to 2005SP2 I noticed two stored procedures > having issue due to changed functionality of the @@IDENTITY vs. > SCOPE_INDETITY(). I changed them on the publisher/distributor and > regenrated the snapshot before marking subscriptions for reinitialization. > Next sync supposed to apply these changes to the subscribers, right ? I > had the publication comp level set to 90RTM, ddl changes enabled, etc. - > nothing worked unless publication was dropped and recreated. With high > number of subscribers and rather active publisher I didn't want to > reconfigure my replication setup... > and after digging around I found the way to change these 2 sp's directly > on the subscribers: > > disable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE > go > > ALTER PROCEDURE .... > go > > enable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE > go > > > Does anyone see a potential future problem with this "solution" ? > tia, > rafael >
these were user stored procedures inside of the replicated database.
I replaced statement "SELECT @HEADER_ID = @@IDENTITY" with "SELECT @HEADER_ID = SCOPE_IDENTITY( )" . Now the subscribers have the same stored procedures as the publisher, but I wonder if sneaky SQL Server has some kind of checksum or something, that might give me trouble in subsequent synchronizations... thanks rafael [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:uPddJMicHHA.3484@TK2MSFTNGP04.phx.gbl... > Which stored procedures did you modify and what changes did you make to > them? > > -- > 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 > > > > "Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message > news:OGgI9AicHHA.648@TK2MSFTNGP04.phx.gbl... >> after update from 2000SP4 to 2005SP2 I noticed two stored procedures >> having issue due to changed functionality of the @@IDENTITY vs. >> SCOPE_INDETITY(). I changed them on the publisher/distributor and >> regenrated the snapshot before marking subscriptions for >> reinitialization. Next sync supposed to apply these changes to the >> subscribers, right ? I had the publication comp level set to 90RTM, ddl >> changes enabled, etc. - nothing worked unless publication was dropped and >> recreated. With high number of subscribers and rather active publisher I >> didn't want to reconfigure my replication setup... >> and after digging around I found the way to change these 2 sp's directly >> on the subscribers: >> >> disable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE >> go >> >> ALTER PROCEDURE .... >> go >> >> enable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE >> go >> >> >> Does anyone see a potential future problem with this "solution" ? >> tia, >> rafael >> > >
No, it doesn't for user stored procedures. I take it you are aware you can
distribution a change to all unc deployed subscribers via sp_addscriptexec? -- 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 [quoted text, click to view] "Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message news:ucomHnicHHA.3960@TK2MSFTNGP04.phx.gbl... > these were user stored procedures inside of the replicated database. > I replaced statement "SELECT @HEADER_ID = @@IDENTITY" > with "SELECT @HEADER_ID = SCOPE_IDENTITY( )" . > Now the subscribers have the same stored procedures as the publisher, > but I wonder if sneaky SQL Server has some kind of checksum or something, > that might give me trouble in subsequent synchronizations... > thanks > rafael > > > "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message > news:uPddJMicHHA.3484@TK2MSFTNGP04.phx.gbl... >> Which stored procedures did you modify and what changes did you make to >> them? >> >> -- >> 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 >> >> >> >> "Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message >> news:OGgI9AicHHA.648@TK2MSFTNGP04.phx.gbl... >>> after update from 2000SP4 to 2005SP2 I noticed two stored procedures >>> having issue due to changed functionality of the @@IDENTITY vs. >>> SCOPE_INDETITY(). I changed them on the publisher/distributor and >>> regenrated the snapshot before marking subscriptions for >>> reinitialization. Next sync supposed to apply these changes to the >>> subscribers, right ? I had the publication comp level set to 90RTM, ddl >>> changes enabled, etc. - nothing worked unless publication was dropped >>> and recreated. With high number of subscribers and rather active >>> publisher I didn't want to reconfigure my replication setup... >>> and after digging around I found the way to change these 2 sp's directly >>> on the subscribers: >>> >>> disable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE >>> go >>> >>> ALTER PROCEDURE .... >>> go >>> >>> enable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE >>> go >>> >>> >>> Does anyone see a potential future problem with this "solution" ? >>> tia, >>> rafael >>> >> >> > >
great, thanks.
I was aware, but I wanted to do the "fix" right away, used Windows batch with SQLCMD. thanks for your responses, as always very knowledgable and quick ;-) [quoted text, click to view] "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message news:uhCsd0icHHA.3960@TK2MSFTNGP04.phx.gbl... > No, it doesn't for user stored procedures. I take it you are aware you can > distribution a change to all unc deployed subscribers via > sp_addscriptexec? > > -- > 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 > > > > "Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message > news:ucomHnicHHA.3960@TK2MSFTNGP04.phx.gbl... >> these were user stored procedures inside of the replicated database. >> I replaced statement "SELECT @HEADER_ID = @@IDENTITY" >> with "SELECT @HEADER_ID = SCOPE_IDENTITY( )" . >> Now the subscribers have the same stored procedures as the publisher, >> but I wonder if sneaky SQL Server has some kind of checksum or something, >> that might give me trouble in subsequent synchronizations... >> thanks >> rafael >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> wrote in message >> news:uPddJMicHHA.3484@TK2MSFTNGP04.phx.gbl... >>> Which stored procedures did you modify and what changes did you make to >>> them? >>> >>> -- >>> 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 >>> >>> >>> >>> "Rafael Lenartowicz" <rafaell@rcl-consulting.com> wrote in message >>> news:OGgI9AicHHA.648@TK2MSFTNGP04.phx.gbl... >>>> after update from 2000SP4 to 2005SP2 I noticed two stored procedures >>>> having issue due to changed functionality of the @@IDENTITY vs. >>>> SCOPE_INDETITY(). I changed them on the publisher/distributor and >>>> regenrated the snapshot before marking subscriptions for >>>> reinitialization. Next sync supposed to apply these changes to the >>>> subscribers, right ? I had the publication comp level set to 90RTM, ddl >>>> changes enabled, etc. - nothing worked unless publication was dropped >>>> and recreated. With high number of subscribers and rather active >>>> publisher I didn't want to reconfigure my replication setup... >>>> and after digging around I found the way to change these 2 sp's >>>> directly on the subscribers: >>>> >>>> disable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE >>>> go >>>> >>>> ALTER PROCEDURE .... >>>> go >>>> >>>> enable TRIGGER [MSmerge_tr_alterschemaonly] ON DATABASE >>>> go >>>> >>>> >>>> Does anyone see a potential future problem with this "solution" ? >>>> tia, >>>> rafael >>>> >>> >>> >> >> > >
Don't see what you're looking for? Try a search.
|
April 2003
June 2003
July 2003
August 2003
September 2003
October 2003
November 2003
December 2003
January 2004
February 2004
March 2004
April 2004
May 2004
June 2004
July 2004
August 2004
September 2004
October 2004
November 2004
December 2004
January 2005
February 2005
March 2005
April 2005
May 2005
June 2005
July 2005
August 2005
September 2005
October 2005
November 2005
December 2005
January 2006
February 2006
March 2006
April 2006
May 2006
June 2006
July 2006
August 2006
September 2006
October 2006
November 2006
December 2006
January 2007
February 2007
March 2007
April 2007
May 2007
June 2007
July 2007
August 2007
September 2007
October 2007
November 2007
| home · blog · groups | Questions? Comments? Contact the dn |