Hello,
I'm writting a Crystal report on all order sent out in the past week.
I all works fine, but I am now trying to take away all the steps I can to make it easier and quicker to run.
The report has to inputs, the ClientCode and the Date Range.
Is there any way/Code that I can use to replace the need to input a date that will get the report to take todays date as the End range ( which is does at the moment ) and the - 7 days and use the result as the Start Range?
Thanks in advanceYou could use the LastFullWeek function in your record selection.
{table.field} = LastFullWeek
GJ
Showing posts with label range. Show all posts
Showing posts with label range. Show all posts
Thursday, March 22, 2012
Tuesday, March 20, 2012
Automatic recalculation without having to change formula
I have a worksheet that calculates the last 6 months of information. Is there a formula that will automatically change the cell range to the last 6 columns once you have entered another column? I need to keep the previous information. I usually add the new column, hide the oldest column, and then change the cell range manually. This leaves room for user errors and is a great big pain in the badonky donk. I would appreciate any help.I am wondering if you are talking about Reporting Services, as you are takling about cell ranges ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||
I really have no idea at all. I will give you an example.
6 month total Jan Feb Mar Apr May Jun July Aug
Kim 4639.00 492.00 875.00 921.00 785.00 567.00 924.00 567.00 824.00
Each month I add the new information to the spreatsheet. Now when I enter the totals for August I want Excel to automaticaly grab the last 6 months of information. So when I Enter August it will automaticaly drop off the February totals and pick up the newly entered August totals. I don't know if this is possible, but it would be really nice if Excel could do this. Right now I have to manually change the formula for each person. Each person has 4 different 6 month totals to calculate. For me this is not so bad because there are only 2 employees in our office, but I have received emails from other offices that have quite a few employees and this would be helpful. It would also reduce operator error.
A Greniger
|||Group your values to the month, use a matrix to display the data and put the month on the horizontal axis, and the user (seems in your case) on the vertical one.Jens K. Suessmeyer.
http://www.sqlserver2005.de
Monday, March 19, 2012
Automatic recalculation without having to change formula
I have a worksheet that calculates the last 6 months of information. Is there a formula that will automatically change the cell range to the last 6 columns once you have entered another column? I need to keep the previous information. I usually add the new column, hide the oldest column, and then change the cell range manually. This leaves room for user errors and is a great big pain in the badonky donk. I would appreciate any help.I am wondering if you are talking about Reporting Services, as you are takling about cell ranges ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||
I really have no idea at all. I will give you an example.
6 month total Jan Feb Mar Apr May Jun July Aug
Kim 4639.00 492.00 875.00 921.00 785.00 567.00 924.00 567.00 824.00
Each month I add the new information to the spreatsheet. Now when I enter the totals for August I want Excel to automaticaly grab the last 6 months of information. So when I Enter August it will automaticaly drop off the February totals and pick up the newly entered August totals. I don't know if this is possible, but it would be really nice if Excel could do this. Right now I have to manually change the formula for each person. Each person has 4 different 6 month totals to calculate. For me this is not so bad because there are only 2 employees in our office, but I have received emails from other offices that have quite a few employees and this would be helpful. It would also reduce operator error.
A Greniger
|||Group your values to the month, use a matrix to display the data and put the month on the horizontal axis, and the user (seems in your case) on the vertical one.Jens K. Suessmeyer.
http://www.sqlserver2005.de
Automatic Range Management - I give up
I have searched through this NG as well as searched BOL concerning this
process called Automatic Range Management, which is suggested as an easy way
to handle reseeding of the identity values. But I have no idea how to set up
ARM. I looked through the properties of my publications and subscriptions
and couldn't find anything for ARM. Nor do the wizards reveal any setting
for ARM.
So, howya do it?
Roger,
in the publication properties, on the articles tab, click on the elipsis
button which gives access to the article properties. There is a tab for
identities, and a checkbox at the top for automatic range management.
Assuming you haven't yet subscribed, it won't be greyed out and you can then
set the size of the range etc.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||THANK YOU!!!!!!
"Paul Ibison" wrote:
> Roger,
> in the publication properties, on the articles tab, click on the elipsis
> button which gives access to the article properties. There is a tab for
> identities, and a checkbox at the top for automatic range management.
> Assuming you haven't yet subscribed, it won't be greyed out and you can then
> set the size of the range etc.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
process called Automatic Range Management, which is suggested as an easy way
to handle reseeding of the identity values. But I have no idea how to set up
ARM. I looked through the properties of my publications and subscriptions
and couldn't find anything for ARM. Nor do the wizards reveal any setting
for ARM.
So, howya do it?
Roger,
in the publication properties, on the articles tab, click on the elipsis
button which gives access to the article properties. There is a tab for
identities, and a checkbox at the top for automatic range management.
Assuming you haven't yet subscribed, it won't be greyed out and you can then
set the size of the range etc.
HTH,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||THANK YOU!!!!!!
"Paul Ibison" wrote:
> Roger,
> in the publication properties, on the articles tab, click on the elipsis
> button which gives access to the article properties. There is a tab for
> identities, and a checkbox at the top for automatic range management.
> Assuming you haven't yet subscribed, it won't be greyed out and you can then
> set the size of the range etc.
> HTH,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Automatic Identity Range Management
Hello,
I've got a question dealing with automatic identity range management.
Currently we have a database setup for publication in which the subscribers
are running SQL Server CE on mobile devices. We've got automatic identity
range management turned on for a few of our tables and we're having some
issues with the publisher identity ranges not getting automatically
reassigned after they are exhausted. I found this statement in the SQL Server
manuals:
"If the Publisher exhausts its identity range after an insert, it can
automatically assign a new range if the insert was performed by a member of
the db_owner fixed database role."
The user that is inserting the rows into the publisher database is a member
of the db_owner fixed database role but when a row is inserted which exhausts
the currenty identity range they are not automatically reassigned. I can
manually adjust the identity ranges using sp_adjustpublisheridentityrange
logged in with the same user, but they are not automatically adjusted after
the insert as the documentation suggests.
What else should we check for?
Thanks in advance,
mike...
You have to size the ranges for the max amount of inserts that would occur
between syncs. Otherwise the range will not be adjusted.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike..." <Mike@.discussions.microsoft.com> wrote in message
news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
> Hello,
> I've got a question dealing with automatic identity range management.
> Currently we have a database setup for publication in which the
> subscribers
> are running SQL Server CE on mobile devices. We've got automatic identity
> range management turned on for a few of our tables and we're having some
> issues with the publisher identity ranges not getting automatically
> reassigned after they are exhausted. I found this statement in the SQL
> Server
> manuals:
> "If the Publisher exhausts its identity range after an insert, it can
> automatically assign a new range if the insert was performed by a member
> of
> the db_owner fixed database role."
> The user that is inserting the rows into the publisher database is a
> member
> of the db_owner fixed database role but when a row is inserted which
> exhausts
> the currenty identity range they are not automatically reassigned. I can
> manually adjust the identity ranges using sp_adjustpublisheridentityrange
> logged in with the same user, but they are not automatically adjusted
> after
> the insert as the documentation suggests.
> What else should we check for?
> Thanks in advance,
> mike...
>
|||Hilary, Thanks for the reply - the problem we've run into is that the user
accounts (Windows Auth) sync'ing the devices are not members of the db_owner
role so the merge agents do not refresh the identity ranges (which is another
issues we've been attempting to work around). From our experiences (and from
reading the SQL Server manuals) the publisher identity ranges should refresh
themselves if the inserts are done at the publisher by users in the db_owner
fixed database role. This isn't occuring at one of our sites and I'm
wondering what other security arrangements need to be made for this to occur.
Any ideas?
Thanks again,
mike...
"Hilary Cotter" wrote:
> You have to size the ranges for the max amount of inserts that would occur
> between syncs. Otherwise the range will not be adjusted.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Mike..." <Mike@.discussions.microsoft.com> wrote in message
> news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
>
>
I've got a question dealing with automatic identity range management.
Currently we have a database setup for publication in which the subscribers
are running SQL Server CE on mobile devices. We've got automatic identity
range management turned on for a few of our tables and we're having some
issues with the publisher identity ranges not getting automatically
reassigned after they are exhausted. I found this statement in the SQL Server
manuals:
"If the Publisher exhausts its identity range after an insert, it can
automatically assign a new range if the insert was performed by a member of
the db_owner fixed database role."
The user that is inserting the rows into the publisher database is a member
of the db_owner fixed database role but when a row is inserted which exhausts
the currenty identity range they are not automatically reassigned. I can
manually adjust the identity ranges using sp_adjustpublisheridentityrange
logged in with the same user, but they are not automatically adjusted after
the insert as the documentation suggests.
What else should we check for?
Thanks in advance,
mike...
You have to size the ranges for the max amount of inserts that would occur
between syncs. Otherwise the range will not be adjusted.
Hilary Cotter
Director of Text Mining and Database Strategy
RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
This posting is my own and doesn't necessarily represent RelevantNoise's
positions, strategies or opinions.
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
"Mike..." <Mike@.discussions.microsoft.com> wrote in message
news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
> Hello,
> I've got a question dealing with automatic identity range management.
> Currently we have a database setup for publication in which the
> subscribers
> are running SQL Server CE on mobile devices. We've got automatic identity
> range management turned on for a few of our tables and we're having some
> issues with the publisher identity ranges not getting automatically
> reassigned after they are exhausted. I found this statement in the SQL
> Server
> manuals:
> "If the Publisher exhausts its identity range after an insert, it can
> automatically assign a new range if the insert was performed by a member
> of
> the db_owner fixed database role."
> The user that is inserting the rows into the publisher database is a
> member
> of the db_owner fixed database role but when a row is inserted which
> exhausts
> the currenty identity range they are not automatically reassigned. I can
> manually adjust the identity ranges using sp_adjustpublisheridentityrange
> logged in with the same user, but they are not automatically adjusted
> after
> the insert as the documentation suggests.
> What else should we check for?
> Thanks in advance,
> mike...
>
|||Hilary, Thanks for the reply - the problem we've run into is that the user
accounts (Windows Auth) sync'ing the devices are not members of the db_owner
role so the merge agents do not refresh the identity ranges (which is another
issues we've been attempting to work around). From our experiences (and from
reading the SQL Server manuals) the publisher identity ranges should refresh
themselves if the inserts are done at the publisher by users in the db_owner
fixed database role. This isn't occuring at one of our sites and I'm
wondering what other security arrangements need to be made for this to occur.
Any ideas?
Thanks again,
mike...
"Hilary Cotter" wrote:
> You have to size the ranges for the max amount of inserts that would occur
> between syncs. Otherwise the range will not be adjusted.
> --
> Hilary Cotter
> Director of Text Mining and Database Strategy
> RelevantNOISE.Com - Dedicated to mining blogs for business intelligence.
> This posting is my own and doesn't necessarily represent RelevantNoise's
> positions, strategies or opinions.
> 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
>
> "Mike..." <Mike@.discussions.microsoft.com> wrote in message
> news:5D34048F-05C2-4502-B295-C10AB89AF4BE@.microsoft.com...
>
>
Automatic Identity Range Handling warning message.
Ive implemented merge replication with SQL Server and an Access Database.
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
SQL Server is the publisher and Access database is the subscriber.
I have ONE table in the schema that can be updated in both the subscriber
side as well as the publisher side. I have an IDENTITY column in this table
and I have set the property "Automatic Identity Range Handling" ON for this
table.
Ive also created a script to create this publisher. Now, heres my problem.
When I create the publisher, I get a warning message thats stated below:
"Warning: only Subscribers running SQL Server 2000 can synchronize with
publication '<database>' because automatic identity ranges are being used."
OK, Ive done some resonable testing on this statement and it SEEMS that
synchronization with Automatic Identity Range Handing enabled DOES work for
Access databases as well even though the warning states otherwise! Im using
JET to connect to access and not the desktop engine. Once the threshold is
reached, and after the merge agent kicks in, the indentity seed is reset to
the appropriate values on both the access and the sql server side.
Appropriate check constraints are also placed.
I just want to make sure if this is correct - and this warning is just
bogus.
Thanks,
Girish
Hi Girish,
From your descriptions, I understood that you would like to know whether it
matters when warning is shown for "automatic identity ranges are being
used". Have I understood you? If there is anything I misunderstood, please
feel free to let me know.
Based on my scope, there are some known issues for us by using automatic
identity ranges. You could have a view on them
BUG: Unable to Change Identity Range of Publisher If You Use Auto Identity
Range
http://support.microsoft.com/default...b;en-us;310540
BUG: Identity Range Not Adjusted on Publisher When Merge Agent Runs
Continuously
http://support.microsoft.com/default...b;en-us;304706
Additionaly, there is a by design issue for automatic identity range based
on the descriptions in the following documents.
PRB: Automatic Identity Range Handling Is Not Correct If the Merge Agent
Runs an Insert Trigger
http://support.microsoft.com/default...b;en-us;324361
I am afraid the above all are why this warning is shown up.
Thank you for your patience and cooperation. If you have any questions or
concerns, don't hesitate to let me know. We are here to be of assistance!
Sincerely yours,
Mingqing Cheng
Microsoft Developer Community Support
Introduction to Yukon! - http://www.microsoft.com/sql/yukon
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks!
automatic identity range handling - newbie question
I am having problems when attempting to publish my database. When I try to
set the automatic identity range handler, the checkbox is dimmed out.
I am using SQL Server 2000 and my subscribers are using Windows CE. I have
set all the indentity fields to INT (2). Everything seems to be in order, but
the checkbox is still dimmed out.
All help will be greatly appreciated
Dale
Dale,
do you already have a subscription to this publication? If it is dimmed out
when looking at the publisher properties, you'll have to drop the
subscriptions, drop the article (apply) then readd the article and it will
then be possible to have automatic range management.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply.
I am very new to this, so could you please explain how I "drop" the
subscriptions, and "drop" the article.
thanks
Dale
"Paul Ibison" wrote:
> Dale,
> do you already have a subscription to this publication? If it is dimmed out
> when looking at the publisher properties, you'll have to drop the
> subscriptions, drop the article (apply) then readd the article and it will
> then be possible to have automatic range management.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Dale,
you can right-click on the publication and select the publisher properties.
On the subscriptions tab, delete each one. Apply. On hte articles tab,
uncheck the table in question. Apply. Next, check this article (table) and
on the article properties you'll be able to enable automatic range
management. After that, readd the subscriptions and run the snapshot agent
and merge agents.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your help Paul!
I found the problem, one of the identity fields was accidently set to
cluster. Once fixed the publication went smoothly.
BTW your website is great!
Dale
"Paul Ibison" wrote:
> Dale,
> you can right-click on the publication and select the publisher properties.
> On the subscriptions tab, delete each one. Apply. On hte articles tab,
> uncheck the table in question. Apply. Next, check this article (table) and
> on the article properties you'll be able to enable automatic range
> management. After that, readd the subscriptions and run the snapshot agent
> and merge agents.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
set the automatic identity range handler, the checkbox is dimmed out.
I am using SQL Server 2000 and my subscribers are using Windows CE. I have
set all the indentity fields to INT (2). Everything seems to be in order, but
the checkbox is still dimmed out.
All help will be greatly appreciated
Dale
Dale,
do you already have a subscription to this publication? If it is dimmed out
when looking at the publisher properties, you'll have to drop the
subscriptions, drop the article (apply) then readd the article and it will
then be possible to have automatic range management.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for the reply.
I am very new to this, so could you please explain how I "drop" the
subscriptions, and "drop" the article.
thanks
Dale
"Paul Ibison" wrote:
> Dale,
> do you already have a subscription to this publication? If it is dimmed out
> when looking at the publisher properties, you'll have to drop the
> subscriptions, drop the article (apply) then readd the article and it will
> then be possible to have automatic range management.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
|||Dale,
you can right-click on the publication and select the publisher properties.
On the subscriptions tab, delete each one. Apply. On hte articles tab,
uncheck the table in question. Apply. Next, check this article (table) and
on the article properties you'll be able to enable automatic range
management. After that, readd the subscriptions and run the snapshot agent
and merge agents.
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Thanks for your help Paul!
I found the problem, one of the identity fields was accidently set to
cluster. Once fixed the publication went smoothly.
BTW your website is great!
Dale
"Paul Ibison" wrote:
> Dale,
> you can right-click on the publication and select the publisher properties.
> On the subscriptions tab, delete each one. Apply. On hte articles tab,
> uncheck the table in question. Apply. Next, check this article (table) and
> on the article properties you'll be able to enable automatic range
> management. After that, readd the subscriptions and run the snapshot agent
> and merge agents.
> Cheers,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>
>
Sunday, March 11, 2012
Automatic Data Ranges
Hello -
I'm trying to come up with a report that will separate a range of data into X slices. For example, the range might be from 0 through 100. I'd then want to slice it 10 ways, ending up with 10 groups. For each of those groups, I'd want a count of the number of records in my database that fall into each of those ranges. So, the number of records between 0 and 10 becomes the value of range 1. 11-20 becomes range 2, etc. After all that I'll be charting that data, but I think that'll be the start and then I can handle it from there.
Ideas?
Thanks,
Greg.You can do it one of two ways. Both ways require you to create a group - this can be done from the Insert/Group menu.
First way would be to use the "Specified Order" option in the order drop down list. this allows you to specify conditions that will group the data. This is fairly static in that you have to specify the conditions at design time, although there are ways to make this more dynamic using parameters - but that is a little more complex.
The second method is more flexible. You can create a group based on a Formula. Create a new Formula by right clicking on the Formula heading in the Field Explorer (assuming you are using V9 or above). You now have access to the complete formula language to define how you want to group your data - this can be very dynamic, as it can be conditioned by the data coming into the report itself. Once you have created your formula you can create a group on it.
Regards,
DS.
I'm trying to come up with a report that will separate a range of data into X slices. For example, the range might be from 0 through 100. I'd then want to slice it 10 ways, ending up with 10 groups. For each of those groups, I'd want a count of the number of records in my database that fall into each of those ranges. So, the number of records between 0 and 10 becomes the value of range 1. 11-20 becomes range 2, etc. After all that I'll be charting that data, but I think that'll be the start and then I can handle it from there.
Ideas?
Thanks,
Greg.You can do it one of two ways. Both ways require you to create a group - this can be done from the Insert/Group menu.
First way would be to use the "Specified Order" option in the order drop down list. this allows you to specify conditions that will group the data. This is fairly static in that you have to specify the conditions at design time, although there are ways to make this more dynamic using parameters - but that is a little more complex.
The second method is more flexible. You can create a group based on a Formula. Create a new Formula by right clicking on the Formula heading in the Field Explorer (assuming you are using V9 or above). You now have access to the complete formula language to define how you want to group your data - this can be very dynamic, as it can be conditioned by the data coming into the report itself. Once you have created your formula you can create a group on it.
Regards,
DS.
Friday, February 10, 2012
Auto identity in republisher scenario
Hi to everyone,
Is auto identity range supported in publisher-subscriber scenario?
In example:
Server1 publish database1 with article Table1 with auto identity range.
Server1 reserve itself the range 1-100.
Server2 subscribe to Server1 and get and identity range 101-200.
Server2 is also a republisher for database1 so it allows Client1 to
subscribe and replicate with it.
Which identity range does Client1 get?
I suppose Client1 will get a 201-300 range from Server2, which will
eventually conflict if the same range is assigned to other clients directly
from Server1.
Am I right or am I wrong?
Kind regards,
Corrado
You are correct that it will eventually conflict. Auto-identity ranges is
something that I've played with and then never used. There are just too
many potential problems with them at this point. You can wind up with range
skipping and run out of values long before you would have. I've also had
cases where you generated duplicate values.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||are you using merge replication? it is unclear from your post.
Server 1 will have a check constraint on the identity column allowing a
range of identity values from 101-200. Server 2 from 201-300. Client 1 will
have a range from 320-330.
The ranges will be maintained between server 2 and client 1 with a different
set of ranges and thresholds than between Server 1 and Server 2.
There are some caveats with automatic identity range management. First you
must use a representative data type that will be good for your range of
values - ie smallint will probably be too small for most applications.
Secondly pick a range which is larger than your largest batch insert. The
automatic range adjustment is run after a transaction is distruted for
transactional publications or covergence is achieved if you are using merge.
Third most dba's will set the maxiumn ranges they expect their replication
solution to use in the life time of thier probject - sort of like set it and
forget it.
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:uT2xsySMEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi to everyone,
> Is auto identity range supported in publisher-subscriber scenario?
> In example:
> Server1 publish database1 with article Table1 with auto identity range.
> Server1 reserve itself the range 1-100.
> Server2 subscribe to Server1 and get and identity range 101-200.
> Server2 is also a republisher for database1 so it allows Client1 to
> subscribe and replicate with it.
> Which identity range does Client1 get?
> I suppose Client1 will get a 201-300 range from Server2, which will
> eventually conflict if the same range is assigned to other clients
directly
> from Server1.
> Am I right or am I wrong?
> Kind regards,
> Corrado
>
Is auto identity range supported in publisher-subscriber scenario?
In example:
Server1 publish database1 with article Table1 with auto identity range.
Server1 reserve itself the range 1-100.
Server2 subscribe to Server1 and get and identity range 101-200.
Server2 is also a republisher for database1 so it allows Client1 to
subscribe and replicate with it.
Which identity range does Client1 get?
I suppose Client1 will get a 201-300 range from Server2, which will
eventually conflict if the same range is assigned to other clients directly
from Server1.
Am I right or am I wrong?
Kind regards,
Corrado
You are correct that it will eventually conflict. Auto-identity ranges is
something that I've played with and then never used. There are just too
many potential problems with them at this point. You can wind up with range
skipping and run out of values long before you would have. I've also had
cases where you generated duplicate values.
Mike
Principal Mentor
Solid Quality Learning
"More than just Training"
SQL Server MVP
http://www.solidqualitylearning.com
http://www.mssqlserver.com
|||are you using merge replication? it is unclear from your post.
Server 1 will have a check constraint on the identity column allowing a
range of identity values from 101-200. Server 2 from 201-300. Client 1 will
have a range from 320-330.
The ranges will be maintained between server 2 and client 1 with a different
set of ranges and thresholds than between Server 1 and Server 2.
There are some caveats with automatic identity range management. First you
must use a representative data type that will be good for your range of
values - ie smallint will probably be too small for most applications.
Secondly pick a range which is larger than your largest batch insert. The
automatic range adjustment is run after a transaction is distruted for
transactional publications or covergence is achieved if you are using merge.
Third most dba's will set the maxiumn ranges they expect their replication
solution to use in the life time of thier probject - sort of like set it and
forget it.
"Corrado Labinaz" <corradolab@.virgilio.it> wrote in message
news:uT2xsySMEHA.3668@.TK2MSFTNGP11.phx.gbl...
> Hi to everyone,
> Is auto identity range supported in publisher-subscriber scenario?
> In example:
> Server1 publish database1 with article Table1 with auto identity range.
> Server1 reserve itself the range 1-100.
> Server2 subscribe to Server1 and get and identity range 101-200.
> Server2 is also a republisher for database1 so it allows Client1 to
> subscribe and replicate with it.
> Which identity range does Client1 get?
> I suppose Client1 will get a 201-300 range from Server2, which will
> eventually conflict if the same range is assigned to other clients
directly
> from Server1.
> Am I right or am I wrong?
> Kind regards,
> Corrado
>
Subscribe to:
Posts (Atom)