|
|
You're in the
sql server replication
group:Workarround in identities in Merge Replication to avoid the use of auto identity range
sql server replication:
Hi,
I've some tables where I can only use an ID and auto identity range. But in some other tables I can have the primary key as [Subscriber_code] + ID. Then I don't need to use auto identity range. The Subscriber_code it's stored in the INI file of the application that uses the database so the only problem that I may have is that a system administrator install the application without setting the Subscriber_code at the INI file. But how can I avoid users to insert a Subscriber_code different from its real Subscriber_code ? I've some ideas, but none of them seems enough good: - Use a CONSTRAINT using WITH NOCHECK and NOT FOR REPLICATION. - Use a TRIGGER using NOT FOR REPLICATION. In both examples, I should write a script to create these objects with the desired Subscriber_code in each Subscriber database. I think there can be a lot of trouble... so how would you do that ? Is there a solution instead of constraint and triggers ? Thank you in advance ! Josep.
I am having a hard time understanding your problem. Are you saying that you
need a SQL solution as a failback incase your administrator forgot to include the ini file in the deployment? I think you need to address your deployment so that this does not happen. By auto identity ranges do you mean automatic identity range management? or do you mean the identity property of the columns? You can remove the automatic identity constraint and modify the identity property of the identity column to have a different value, but you have to be careful doing this as you might assign a range or values which are in use by other subscribers. -- 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] "Josep" <jmartinez@autec.es> wrote in message news:uBNMdtMrHHA.4100@TK2MSFTNGP06.phx.gbl... > Hi, > > I've some tables where I can only use an ID and auto identity range. But > in some other tables I can have the primary key as [Subscriber_code] + ID. > Then I don't need to use auto identity range. The Subscriber_code it's > stored in the INI file of the application that uses the database so the > only problem that I may have is that a system administrator install the > application without setting the Subscriber_code at the INI file. > > But how can I avoid users to insert a Subscriber_code different from its > real Subscriber_code ? > > I've some ideas, but none of them seems enough good: > - Use a CONSTRAINT using WITH NOCHECK and NOT FOR REPLICATION. > - Use a TRIGGER using NOT FOR REPLICATION. > > In both examples, I should write a script to create these objects with the > desired Subscriber_code in each Subscriber database. I think there can be > a lot of trouble... so how would you do that ? Is there a solution instead > of constraint and triggers ? > > > Thank you in advance ! > > Josep. >
Yes, I'm afraid that the system administration forget to configure properly the Subscriber_code in the INI file. Or he might copy this file to another Subscriber and forget to change this code.... To avoid this, I would like to know if there's a possible way to avoid (triggers, constraints, ...). Yes, I mean the automatic range management. I can avoid of using it in tables where primary key is Subscriber_code + ID if I can assure that no insert will be made in one subscriber with a Subscriber_code different that its. I don't have any experience with Merge Replications, but for the tests I've done until now, I've found that automatic range management could be a "problem maker", so if I can avoid them... I hope this time I've explain myself better... Thank you for your help! Josep. "Hilary Cotter" <hilary.cotter@gmail.com> escribió en el mensaje news:OZfmrqOrHHA.4604@TK2MSFTNGP04.phx.gbl... [quoted text, click to view] >I am having a hard time understanding your problem. Are you saying that you >need a SQL solution as a failback incase your administrator forgot to >include the ini file in the deployment? I think you need to address your >deployment so that this does not happen. > > By auto identity ranges do you mean automatic identity range management? > or do you mean the identity property of the columns? > > You can remove the automatic identity constraint and modify the identity > property of the identity column to have a different value, but you have to > be careful doing this as you might assign a range or values which are in > use by other subscribers. > > -- > 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 > "Josep" <jmartinez@autec.es> wrote in message > news:uBNMdtMrHHA.4100@TK2MSFTNGP06.phx.gbl... >> Hi, >> >> I've some tables where I can only use an ID and auto identity range. But >> in some other tables I can have the primary key as [Subscriber_code] + >> ID. Then I don't need to use auto identity range. The Subscriber_code >> it's stored in the INI file of the application that uses the database so >> the only problem that I may have is that a system administrator install >> the application without setting the Subscriber_code at the INI file. >> >> But how can I avoid users to insert a Subscriber_code different from its >> real Subscriber_code ? >> >> I've some ideas, but none of them seems enough good: >> - Use a CONSTRAINT using WITH NOCHECK and NOT FOR REPLICATION. >> - Use a TRIGGER using NOT FOR REPLICATION. >> >> In both examples, I should write a script to create these objects with >> the desired Subscriber_code in each Subscriber database. I think there >> can be a lot of trouble... so how would you do that ? Is there a solution >> instead of constraint and triggers ? >> >> >> Thank you in advance ! >> >> Josep. >> > >
A not for replication check constraint would probably be ideal. perhaps one
that would include sniffing @@servername to dynamically adjust for different subscribers. -- 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] "Josep" <jmartinez@autec.es> wrote in message news:u9dqvJqrHHA.3228@TK2MSFTNGP03.phx.gbl... > I'm sorry Hilary for my english, but my last postt was not a sentence. It > was a question: > How can I prevent Inserts/Updates with a Subscriber_code different than > its Subscriber_code (every subscriber has a unique code)? > > > Thank you in advance, > > Josep. > > > > "Josep" <jmartinez@autec.es> escribió en el mensaje > news:e9huncPrHHA.1508@TK2MSFTNGP05.phx.gbl... >> >> >> Yes, I'm afraid that the system administration forget to configure >> properly the Subscriber_code in the INI file. Or he might copy this file >> to another Subscriber and forget to change this code.... >> >> >> To avoid this, I would like to know if there's a possible way to avoid >> (triggers, constraints, ...). >> >> Yes, I mean the automatic range management. I can avoid of using it in >> tables where primary key is Subscriber_code + ID if I can assure that no >> insert will be made in one subscriber with a Subscriber_code different >> that its. >> >> I don't have any experience with Merge Replications, but for the tests >> I've done until now, I've found that automatic range management could be >> a "problem maker", so if I can avoid them... >> >> >> I hope this time I've explain myself better... >> >> >> Thank you for your help! >> >> Josep. >> >> >> >> >> "Hilary Cotter" <hilary.cotter@gmail.com> escribió en el mensaje >> news:OZfmrqOrHHA.4604@TK2MSFTNGP04.phx.gbl... >>>I am having a hard time understanding your problem. Are you saying that >>>you need a SQL solution as a failback incase your administrator forgot to >>>include the ini file in the deployment? I think you need to address your >>>deployment so that this does not happen. >>> >>> By auto identity ranges do you mean automatic identity range management? >>> or do you mean the identity property of the columns? >>> >>> You can remove the automatic identity constraint and modify the identity >>> property of the identity column to have a different value, but you have >>> to be careful doing this as you might assign a range or values which are >>> in use by other subscribers. >>> >>> -- >>> 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 >>> "Josep" <jmartinez@autec.es> wrote in message >>> news:uBNMdtMrHHA.4100@TK2MSFTNGP06.phx.gbl... >>>> Hi, >>>> >>>> I've some tables where I can only use an ID and auto identity range. >>>> But in some other tables I can have the primary key as >>>> [Subscriber_code] + ID. Then I don't need to use auto identity range. >>>> The Subscriber_code it's stored in the INI file of the application that >>>> uses the database so the only problem that I may have is that a system >>>> administrator install the application without setting the >>>> Subscriber_code at the INI file. >>>> >>>> But how can I avoid users to insert a Subscriber_code different from >>>> its real Subscriber_code ? >>>> >>>> I've some ideas, but none of them seems enough good: >>>> - Use a CONSTRAINT using WITH NOCHECK and NOT FOR REPLICATION. >>>> - Use a TRIGGER using NOT FOR REPLICATION. >>>> >>>> In both examples, I should write a script to create these objects with >>>> the desired Subscriber_code in each Subscriber database. I think there >>>> can be a lot of trouble... so how would you do that ? Is there a >>>> solution instead of constraint and triggers ? >>>> >>>> >>>> Thank you in advance ! >>>> >>>> Josep. >>>> >>> >>> >> >> > >
I'm sorry Hilary for my english, but my last postt was not a sentence. It
was a question: How can I prevent Inserts/Updates with a Subscriber_code different than its Subscriber_code (every subscriber has a unique code)? Thank you in advance, Josep. "Josep" <jmartinez@autec.es> escribió en el mensaje news:e9huncPrHHA.1508@TK2MSFTNGP05.phx.gbl... [quoted text, click to view] > > > Yes, I'm afraid that the system administration forget to configure > properly the Subscriber_code in the INI file. Or he might copy this file > to another Subscriber and forget to change this code.... > > > To avoid this, I would like to know if there's a possible way to avoid > (triggers, constraints, ...). > > Yes, I mean the automatic range management. I can avoid of using it in > tables where primary key is Subscriber_code + ID if I can assure that no > insert will be made in one subscriber with a Subscriber_code different > that its. > > I don't have any experience with Merge Replications, but for the tests > I've done until now, I've found that automatic range management could be a > "problem maker", so if I can avoid them... > > > I hope this time I've explain myself better... > > > Thank you for your help! > > Josep. > > > > > "Hilary Cotter" <hilary.cotter@gmail.com> escribió en el mensaje > news:OZfmrqOrHHA.4604@TK2MSFTNGP04.phx.gbl... >>I am having a hard time understanding your problem. Are you saying that >>you need a SQL solution as a failback incase your administrator forgot to >>include the ini file in the deployment? I think you need to address your >>deployment so that this does not happen. >> >> By auto identity ranges do you mean automatic identity range management? >> or do you mean the identity property of the columns? >> >> You can remove the automatic identity constraint and modify the identity >> property of the identity column to have a different value, but you have >> to be careful doing this as you might assign a range or values which are >> in use by other subscribers. >> >> -- >> 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 >> "Josep" <jmartinez@autec.es> wrote in message >> news:uBNMdtMrHHA.4100@TK2MSFTNGP06.phx.gbl... >>> Hi, >>> >>> I've some tables where I can only use an ID and auto identity range. But >>> in some other tables I can have the primary key as [Subscriber_code] + >>> ID. Then I don't need to use auto identity range. The Subscriber_code >>> it's stored in the INI file of the application that uses the database so >>> the only problem that I may have is that a system administrator install >>> the application without setting the Subscriber_code at the INI file. >>> >>> But how can I avoid users to insert a Subscriber_code different from its >>> real Subscriber_code ? >>> >>> I've some ideas, but none of them seems enough good: >>> - Use a CONSTRAINT using WITH NOCHECK and NOT FOR REPLICATION. >>> - Use a TRIGGER using NOT FOR REPLICATION. >>> >>> In both examples, I should write a script to create these objects with >>> the desired Subscriber_code in each Subscriber database. I think there >>> can be a lot of trouble... so how would you do that ? Is there a >>> solution instead of constraint and triggers ? >>> >>> >>> Thank you in advance ! >>> >>> Josep. >>> >> >> > >
Thank you for your help :-)
Regards, Josep. "Hilary Cotter" <hilary.cotter@gmail.com> escribió en el mensaje news:uNRs2jqrHHA.4100@TK2MSFTNGP06.phx.gbl... [quoted text, click to view] >A not for replication check constraint would probably be ideal. perhaps one >that would include sniffing @@servername to dynamically adjust for >different subscribers. > > -- > 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 > "Josep" <jmartinez@autec.es> wrote in message > news:u9dqvJqrHHA.3228@TK2MSFTNGP03.phx.gbl... >> I'm sorry Hilary for my english, but my last postt was not a sentence. It >> was a question: >> How can I prevent Inserts/Updates with a Subscriber_code different than >> its Subscriber_code (every subscriber has a unique code)? >> >> >> Thank you in advance, >> >> Josep. >> >> >> >> "Josep" <jmartinez@autec.es> escribió en el mensaje >> news:e9huncPrHHA.1508@TK2MSFTNGP05.phx.gbl... >>> >>> >>> Yes, I'm afraid that the system administration forget to configure >>> properly the Subscriber_code in the INI file. Or he might copy this file >>> to another Subscriber and forget to change this code.... >>> >>> >>> To avoid this, I would like to know if there's a possible way to avoid >>> (triggers, constraints, ...). >>> >>> Yes, I mean the automatic range management. I can avoid of using it in >>> tables where primary key is Subscriber_code + ID if I can assure that no >>> insert will be made in one subscriber with a Subscriber_code different >>> that its. >>> >>> I don't have any experience with Merge Replications, but for the tests >>> I've done until now, I've found that automatic range management could be >>> a "problem maker", so if I can avoid them... >>> >>> >>> I hope this time I've explain myself better... >>> >>> >>> Thank you for your help! >>> >>> Josep. >>> >>> >>> >>> >>> "Hilary Cotter" <hilary.cotter@gmail.com> escribió en el mensaje >>> news:OZfmrqOrHHA.4604@TK2MSFTNGP04.phx.gbl... >>>>I am having a hard time understanding your problem. Are you saying that >>>>you need a SQL solution as a failback incase your administrator forgot >>>>to include the ini file in the deployment? I think you need to address >>>>your deployment so that this does not happen. >>>> >>>> By auto identity ranges do you mean automatic identity range >>>> management? or do you mean the identity property of the columns? >>>> >>>> You can remove the automatic identity constraint and modify the >>>> identity property of the identity column to have a different value, but >>>> you have to be careful doing this as you might assign a range or values >>>> which are in use by other subscribers. >>>> >>>> -- >>>> 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 >>>> "Josep" <jmartinez@autec.es> wrote in message >>>> news:uBNMdtMrHHA.4100@TK2MSFTNGP06.phx.gbl... >>>>> Hi, >>>>> >>>>> I've some tables where I can only use an ID and auto identity range. >>>>> But in some other tables I can have the primary key as >>>>> [Subscriber_code] + ID. Then I don't need to use auto identity range. >>>>> The Subscriber_code it's stored in the INI file of the application >>>>> that uses the database so the only problem that I may have is that a >>>>> system administrator install the application without setting the >>>>> Subscriber_code at the INI file. >>>>> >>>>> But how can I avoid users to insert a Subscriber_code different from >>>>> its real Subscriber_code ? >>>>> >>>>> I've some ideas, but none of them seems enough good: >>>>> - Use a CONSTRAINT using WITH NOCHECK and NOT FOR REPLICATION. >>>>> - Use a TRIGGER using NOT FOR REPLICATION. >>>>> >>>>> In both examples, I should write a script to create these objects with >>>>> the desired Subscriber_code in each Subscriber database. I think there >>>>> can be a lot of trouble... so how would you do that ? Is there a >>>>> solution instead of constraint and triggers ? >>>>> >>>>> >>>>> Thank you in advance ! >>>>> >>>>> Josep. >>>>> >>>> >>>> >>> >>> >> >> > >
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 |