HELP! The merge process could not retrieve identity range resource

Of course I get the error AFTER I start rolling into production!!!!
SQL Server 2000 SP4 and laptops with MSDE SP4 using MERGE with PULL.
There must be a bug in creating articles:
-- ****************** Table Contact with Int16 Identity field on primary key
PRINT 'Table Contact with Int16 Identity field on primary key'
exec sp_addmergearticle @.publication = N'_Prd', @.article = N'Contact',
@.source_owner = N'dbo',
@.source_object = N'Contact', @.type = N'table', @.description = null,
@.column_tracking = N'true',
@.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
0x000000000000CFF1,
@.article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict
Resolver',
@.resolver_info = N'ChangeStamp',
@.subset_filterclause = null, @.vertical_partition = N'false',
@.destination_owner = N'dbo',
@.auto_identity_range = N'true',
@.pub_identity_range = 15000,
@.identity_range = 100,
@.threshold = 99,
@.verify_resolver_signature = 0,
@.allow_interactive_resolver = N'false', @.fast_multicol_updateproc = N'true',
@.check_permissions = 0
GO
Creates a constraint of:
([ContactId] > 12 and [ContactId] < 30000)
when it should be
([ContactId] > 12 and [ContactId] < 15000)
So now after a few clients I can no longer replicate cause I can only go to
32767.
So a few questions:
1) Why the bug and how do I fix it?
2) How do I fix things in production WITHOUT redoing the replication?
3) How can I tell what the current identity is? I mean, even starting at
30000, I should get lots of ranges on the laptops because they should be only
incrementing by 100. That should still be 27 laptops (100 * 2767) before I
blow the Integer data type. We've only rolled about 5-10.
The publisher range has always been twice as large as it should be. Also the
check constraint is one out. I've pointed these errors to MS developers and
apparently they're ok in SQL Server 2005 (haven't tested this yet).
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||your threshold is all wrong. You need a smaller value. Basically you are
saying that at the very best you know that there will be 1 sync occurring
for every insert on Contact and you absolutely know beyond any shadow of a
doubt that this insert will never be kicked back and leave a gap in the
identity field.
For instance try this
create table identity_test
(pk int identity,charcol char(1))
go
begin tran
insert into identity_test (charcol) values ('x')
rollback tran
insert into identity_test (charcol) values ('x')
dbcc checkident('identity_test')
select * from identity_test
you get the value of 2. What happened to 1? you can't reclaim it without
doing a reseed.
This could happen when you want to bump up your threshold and then the next
insert will be kicked back by the constraint.
Pick a value for your range which represents the max amount of inserts which
could occur on your publisher between sync. Multiply by a number to give you
some wiggle room, I use 10. Then set your threshold to 80. This will mean
you get many opportunities for the range to be adjusted.
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
"Buzz" <buzz@.online.nospam> wrote in message
news:C73BE767-1CF1-4289-A875-3F271D6BA3B0@.microsoft.com...
> Of course I get the error AFTER I start rolling into production!!!!
> SQL Server 2000 SP4 and laptops with MSDE SP4 using MERGE with PULL.
> There must be a bug in creating articles:
> -- ****************** Table Contact with Int16 Identity field on primary
key
> PRINT 'Table Contact with Int16 Identity field on primary key'
> exec sp_addmergearticle @.publication = N'_Prd', @.article = N'Contact',
> @.source_owner = N'dbo',
> @.source_object = N'Contact', @.type = N'table', @.description = null,
> @.column_tracking = N'true',
> @.pre_creation_cmd = N'drop', @.creation_script = null, @.schema_option =
> 0x000000000000CFF1,
> @.article_resolver = N'Microsoft SQL Server DATETIME (Later Wins) Conflict
> Resolver',
> @.resolver_info = N'ChangeStamp',
> @.subset_filterclause = null, @.vertical_partition = N'false',
> @.destination_owner = N'dbo',
> @.auto_identity_range = N'true',
> @.pub_identity_range = 15000,
> @.identity_range = 100,
> @.threshold = 99,
> @.verify_resolver_signature = 0,
> @.allow_interactive_resolver = N'false', @.fast_multicol_updateproc =
N'true',
> @.check_permissions = 0
> GO
> Creates a constraint of:
> ([ContactId] > 12 and [ContactId] < 30000)
> when it should be
> ([ContactId] > 12 and [ContactId] < 15000)
> So now after a few clients I can no longer replicate cause I can only go
to
> 32767.
> So a few questions:
> 1) Why the bug and how do I fix it?
> 2) How do I fix things in production WITHOUT redoing the replication?
> 3) How can I tell what the current identity is? I mean, even starting at
> 30000, I should get lots of ranges on the laptops because they should be
only
> incrementing by 100. That should still be 27 laptops (100 * 2767) before I
> blow the Integer data type. We've only rolled about 5-10.
>

HELP! How get user NT login

Hi,
I want to retrieve reports that use the Windwos authentication to filter
data linked to the user (e.g.: only sales for a specific user)
I tried to use a parameter which uses the property
system.security.principal.WindowsIdentity.GetCurrent.name,
It works fine when I run it from the designer, but when I deploy the report
on the ReportManager, I go an error:
Error during processing of 'login' report parameter.
(rsReportParameterProcessingError)
Any idea about this problem, or how to get the login by using another way?
Thanks,
StéphanieYou can use the built in RS parameter User!UserID.Value. This returns the
Windows login name, pre-Windows 2000 style.
If you need to strip of the domain name, I've written a post on how to do it
here
http://blogs.spipp.net/kaisa/archive/2005/04/15/255.aspx
Kaisa
"StephyB" <ssss@.zz.fr> wrote in message
news:%23GBha18zFHA.800@.TK2MSFTNGP12.phx.gbl...
> Hi,
> I want to retrieve reports that use the Windwos authentication to filter
> data linked to the user (e.g.: only sales for a specific user)
> I tried to use a parameter which uses the property
> system.security.principal.WindowsIdentity.GetCurrent.name,
> It works fine when I run it from the designer, but when I deploy the
> report on the ReportManager, I go an error:
> Error during processing of 'login' report parameter.
> (rsReportParameterProcessingError)
> Any idea about this problem, or how to get the login by using another way?
> Thanks,
> Stéphanie
>

Help with WHERE Clause

hi guys help please..I have a stored procedure below that basically retrieve data from tables and under my WHERE clause I want to execute conditions depending on the value of "@.FilterBy" variable. If @.FilterBy is equal to "Pending" then execute a conditions under "IF @.FilterBy = 'Pending'" and if it equals to 'Delivered' then execute conditions under IF @.FilterBy = 'Delivered'. But unfortunately I can't figure out how to do that my stored procedure below just wont work becuase it has an error "Incorrect syntax near the keyword 'IF'"...Any help guys on how to solve this problem? Thanks in advance!

USE [CFREEDB]
GO
/****** Object: StoredProcedure [dbo].[usp_DELIVERY_GET] Script Date: 09/01/2007 12:03:11 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_DELIVERY_GET]
@.FilterBy varchar(20),
@.CustomerID int,
@.FromDate datetime,
@.ToDate datetime
AS
BEGIN

SELECT DISTINCT Delivery.CustomerID, Customer.Customer_LastName, Customer.Customer_MiddleName, Customer.Customer_FirstName,
Customer.Customer_Company, Customer.Customer_Address, Customer.Customer_ContactNo, Customer.Customer_Discount, Customer_Balance

FROM CFREE_Delivery Delivery
INNER JOIN CFREE_Customer Customer
ON Delivery.CustomerID = Customer.CustomerID
WHERE
IF @.FilterBy = 'Pending'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NULL AND
Delivery.IsRemitted IS NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END
IF @.FilterBy = 'Delivered'
BEGIN
Delivery.IsDeleted <> 1 AND
Delivery.IsDelivered IS NOT NULL AND
Delivery.IsRemitted IS NOT NULL AND
Delivery_Date BETWEEN @.FromDate AND @.ToDate
END

ORDER BY Customer.Customer_LastName, Customer.Customer_FirstName, Customer.Customer_MiddleName

ENDWHERE Delivery.IsDeleted <> 1
AND Delivery_Date BETWEEN @.FromDate AND @.ToDate
AND (
( @.FilterBy = 'Pending'
AND Delivery.IsDelivered IS NULL
AND Delivery.IsRemitted IS NULL
)
OR ( @.FilterBy = 'Delivered'
AND Delivery.IsDelivered IS NOT NULL
AND Delivery.IsRemitted IS NOT NULL
)
)