Cannot Make Inserts at Subscribers

I'm getting this error message when trying to insert a row on some
tables in my database.
The identity range managed by replication is full and must be updated
by a replication agent. The INSERT conflict occurred in database 'XXX',
table 'tblContact', column 'Number'. Sp_adjustpublisheridentityrange can
be called to get a new identity range.
These subscribers have been running for several months with out issue
and they are now all getting these errors as of yesterday. Running the
merge agent does not remedy the problem and that stored procedure seems
to only work for the publisher which is running just fine.
Running dbcc checkident ('tblContact', NORESEED) returns
Checking identity information: current identity value '94005', current
column value '94005'.
This will increase by one if I try an insert, but the insert fails.
I took everything down. Deleted the databases on the subscribers and
disabled publishing on the publisher, and started from scratch.
After setting up the replication again, some of the tables that were
giving me the problem are now working, but the majority of the tables
are still giving me this error.
I've run out of things to try. I would appreciate any further
suggestions.
Thank you,
David
Basically you have blown your identity range.
http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
Automatic identity range management has a check constraint which constrains
the possible identity values which can be used on your publisher and
subscriber. If you do an insert the check constraint will kick back the
insert when you blow the range and increment the current identity value.
the proc Sp_adjustpublisheridentityrange will adjust the identity range on
the Publisher, but not the subscriber. To fix this on your subscribers you
have to sample each one and figure out where they are. Then run the merge
agent one by one. One of the first things it will do when it detects the
range has been blown is to adjust it.
If this fails to work you may have to manually intervene and reset it based
on what all your subscribers are at and the values stored in select * from
distribution.dbo.MSmerge_identity_range_allocation s
You may also need to run your merge agents more frequently or bump up the
ranges to account for the max amount of data inserted on the subscriber at
any one time between syncs.
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
"David TMS" <IdontlikeSpam@.nospam.com> wrote in message
news:MPG.20636a6d184a3e8a989680@.msnews.microsoft.c om...
> I'm getting this error message when trying to insert a row on some
> tables in my database.
> The identity range managed by replication is full and must be updated
> by a replication agent. The INSERT conflict occurred in database 'XXX',
> table 'tblContact', column 'Number'. Sp_adjustpublisheridentityrange can
> be called to get a new identity range.
> These subscribers have been running for several months with out issue
> and they are now all getting these errors as of yesterday. Running the
> merge agent does not remedy the problem and that stored procedure seems
> to only work for the publisher which is running just fine.
> Running dbcc checkident ('tblContact', NORESEED) returns
> Checking identity information: current identity value '94005', current
> column value '94005'.
> This will increase by one if I try an insert, but the insert fails.
> I took everything down. Deleted the databases on the subscribers and
> disabled publishing on the publisher, and started from scratch.
> After setting up the replication again, some of the tables that were
> giving me the problem are now working, but the majority of the tables
> are still giving me this error.
> I've run out of things to try. I would appreciate any further
> suggestions.
> Thank you,
> David
|||Running the merge agent did not fix this problem for me. The identity
ranges on the subscribers were also more than large enough. They had
room for 10,000 inserts on each table and were not able to make any on
most of the tables, but other tables seemed to work just fine. Deleting
the publication and disabling replication, but keeping the original
database seemed to make the problem recur immediately after setting up
the replication. I'm still not sure of the cause of this problem, but I
seem to have a work around. I started a new database with a fresh
schema. I then imported the data from the old database. I set
everything back up using this new database name and it went fairly
smoothly.
Thanks,
Dave
In article <#2pv#P8ZHHA.4720@.TK2MSFTNGP06.phx.gbl>,
hilary.cotter@.gmail.com says...
> Basically you have blown your identity range.
> http://www.simple-talk.com/sql/database-administration/the-identity-crisis-in-replication/
> Automatic identity range management has a check constraint which constrains
> the possible identity values which can be used on your publisher and
> subscriber. If you do an insert the check constraint will kick back the
> insert when you blow the range and increment the current identity value.
> the proc Sp_adjustpublisheridentityrange will adjust the identity range on
> the Publisher, but not the subscriber. To fix this on your subscribers you
> have to sample each one and figure out where they are. Then run the merge
> agent one by one. One of the first things it will do when it detects the
> range has been blown is to adjust it.
> If this fails to work you may have to manually intervene and reset it based
> on what all your subscribers are at and the values stored in select * from
> distribution.dbo.MSmerge_identity_range_allocation s
> You may also need to run your merge agents more frequently or bump up the
> ranges to account for the max amount of data inserted on the subscriber at
> any one time between syncs.
>

Cannot insert explicit value for identity...

I am developing an integration process between two databases. One of
them is a SQL Server 2000 and the other is using MSDE 2000. The
integration process is done in C# (VS2003).

The main database is the SQL Server, the MSDE will contain a really
small subset of the data found on the main. To help diminish the amount
of time taken to develop an integration process between those
databases, the same structure are found on both side. The only
difference, when I insert data in the MSDE from the SQL Server, I set
the IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.

I can insert one set of data without problem, but from there, if I try
again, I will always receive the "Cannot insert explicit value for
identity column in table ... when IDENTITY_INSERT is set to OFF." I
saw on Microsoft website the article ID 878501; I noticed I was using
MSDE sp3, I upgraded to SP4... and I still have the problem.

I know, when I call the update function on the sqldataadapter, the
adapters contain the IDENTITY_INSERT ON and it's set to OFF after the
insert. The "Cannot insert..." error is the only one I received.

Can anyone help me on that issue? Take note that this approach was
used because of customer requirements; the size of the database also
causes some problem (over 200 tables) and we decided to use the same
structure on both side to minimize the support time.Just thought about posting the SQL command in the adapter, it will look
something like:

SET IDENTITY_INSERT myTable ON; INSERT INTO myTable (myId, myCol1)
VALUES (@.myId, @.myCol1); SELECT myId, myCol1 FROM myTable WHERE (myId =
@.@.IDENTITY); SET IDENTITY_INSERT myTable OFF;

I hope that will help !|||(nicolas.bouchard@.gmail.com) writes:
> I am developing an integration process between two databases. One of
> them is a SQL Server 2000 and the other is using MSDE 2000. The
> integration process is done in C# (VS2003).
> The main database is the SQL Server, the MSDE will contain a really
> small subset of the data found on the main. To help diminish the amount
> of time taken to develop an integration process between those
> databases, the same structure are found on both side. The only
> difference, when I insert data in the MSDE from the SQL Server, I set
> the IDENTITY_INSERT to ON and use the same IDs found on the SQL Server.

The first reaction is: have you considered replication?

The second reaction is: skip IDENTITY, and generate the IDs on your
own. That is a trivial business. It's only if you have a high INSERT
rate from simultaneous processes that you need IDENTITY, as rolling
your own key can result in contention issues.

IDENTITY is convenient at times, but not in a situation like this. It
causes more problems than it solves.

Yet, an alternative is to use bulk load to extract and load data.
Moving one at time as you do not is not terribly effecient. Which may
not matter if the data size is moderate, but for something like
10-20 MB it could. With BCP it's easy to insert explicit IDENTITY
values with the -E option.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Hi,

thanks for the reply!

We did considered replication but for a reason unknown to me, we didn't
choose that solution; I can still imagine why, due to the fact we had
to develop an ownership process on certain tables, some tables are
owned by the main and others by the MSDE system and again, depending on
the state of the system.

The system (the main one) will be used by 200+ persons during the day,
the insert rate may be quite a problem with our own generated ids.

Until few days ago, I didn't know of bulk load, I will definitely look
into that for future projects. For this project, the amount of data
transfered between databases are not that big (talking less than 1MB
for the first transfer, then it will be almost nothing).

About my problem, I fixed it and I had few more tests to do; I was
planning to post the fix to my problem here, hoping it will help
someone else. I tried to do too much instructions in one command (my
guess is). Instead of doing everything in the same SQLCommand, I
modified the command to only manage the insert and the select to get
the new values; before calling the update command of the DataAdapter, I
disable the IDENTITY_INSERT then after the comand I enable it.

As I said, thanks for the quick answer !

Nicolas Bouchard

Cannot insert explicit value for identity column in table SS_Messeges when IDENTITY_INSERT

I use SQLExpress2005 and I search about this problem , this is a BUG in MsSql 2000 but I use sql Express 2005.

although in my table I set IDENTITY_INSERT on (master Key)

Please help me

I don't quite understand what your problem is. Have you tried "Set identity_insert SS_Messages ON" and still after setting this you're not able to insert explicit value in your table ? Can you explain your problem further ?

|||

let's me explain the my problem

I design a Messages table width this columns

MessageID >> int - MasterKey - Is Identityyes- Identity Increment1 - Identity seed1 NOTNULL

MessageTitle >> nvarchar(256) NOTNULL

MessageBody >> ntext NOTNULL

AddedDate >> dateTime NOTNULL

AddedByIP >> nvarchar(256) NOTNULL

IsRead >> bit NOTNULL

ResName >> nvarchar(256) NULL

ResMail >> nvarchar(256) NOTNULL

my Insert producer

ALTER PROCEDURE dbo.SS_Messages_InsertMessage
(
@.MessageTitle nvarchar(256),
@.MessageBody nvarchar(256),
@.AddedDate datetime,
@.AddedByIP nvarchar(256),
@.isRead bit,
@.ResMail nvarchar(256),
@.ResName nvarchar(256),
@.MessageID int OUTPUT
)
as
SET NOCOUNT ON
insert into SS_Messeges
(MessageID,MessageTitle,MessageBody,AddedDate,AddedByIP,isRead,ResMail,ResName)
VALUES (@.MessageID,@.MessageTitle,@.MessageBody,@.AddedDate,@.AddedByIP,@.isRead,@.ResMail,@.ResName)
SET @.MessageID=SCOPE_IDENTITY()

DAL Message class (Insert Message methods)

public override int InsertMessage(MessageDetails message)
{
using (SqlConnection cn = new SqlConnection(this.ConnectionString))
{
SqlCommand cmd = new SqlCommand("SS_Messeges_InsertMessege", cn);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@.MessageTitle",SqlDbType.NVarChar).Value=message.MessageTitle;
cmd.Parameters.Add("@.MessageBody", SqlDbType.NVarChar).Value=message.MessageBody;
cmd.Parameters.Add("@.AddedDate",SqlDbType.DateTime).Value=message.AddedDate;
cmd.Parameters.Add("@.AddedByIP",SqlDbType.NVarChar).Value=message.AddedByIP;
cmd.Parameters.Add("@.isRead",SqlDbType.NVarChar).Value=message.IsRead;
cmd.Parameters.Add("@.ResMail", SqlDbType.NVarChar).Value = message.ResMail;
cmd.Parameters.Add("@.ResName", SqlDbType.NVarChar).Value = message.ResName;
cmd.Parameters.Add("@.MessageID", SqlDbType.Int).Direction = ParameterDirection.Output;
cn.Open();
int ret = ExecuteNonQuery(cmd);
return (int)cmd.Parameters["@.MessageID"].Value;
}
}

BLL Messages Class >> Insert Message Methods

public static int InsertMessage(string messageTitle, string messagebody,
string resMail, string resName)
{
messageTitle = BizObject.ConvertNullToEmptyString(messageTitle);
messagebody = BizObject.ConvertNullToEmptyString(messagebody);
resName = BizObject.ConvertNullToEmptyString(resName);
resMail = BizObject.ConvertNullToEmptyString(resMail);
MessageDetails record = new MessageDetails(0, messageTitle, messagebody, DateTime.Now, BizObject.CurrentUserIP,
false, resMail, resName);
int ret = SiteProvider.Messages.InsertMessage(record);
return ret;
}

Contact US Code Behind

protected void txtSubmit_Click(object sender, EventArgs e)
{

int ret = Message.InsertMessage(txtTitle.Text, txtBody.Text, txtResMail.Text, txtResName.Text);
}

What is wrong??

when I want to add an Message this error happened :(

Cannot insert explicit value for identity column in table 'SS_Messeges' when IDENTITY_INSERT is set to OFF

|||

You want to insert and return the new ID? It doesn't look like you are supplying a value for the MessageID that you are trying to insert.

If that is the case, you can take the @.MessageID out of the insert statement. You're not really inserting anything into that field. YOu want SQL server to create the next ID for you.

SET NOCOUNT ON
insert into SS_Messeges
(MessageTitle,MessageBody,AddedDate,AddedByIP,isRead,ResMail,ResName)
VALUES (@.MessageTitle,@.MessageBody,@.AddedDate,@.AddedByIP,@.isRead,@.ResMail,@.ResName)
SET @.MessageID=SCOPE_IDENTITY()

|||

thank Pyre

I catch my problem with your post

|||

An identity column, by design, autogenerates its own value when an insert is performed. Apparently, your code must be including the IDENTITY column in the insert and supplying a value. I presume that this is NOT what you want to do.

Cannot insert explicit value for identity column in table Gallery when IDENTITY_INSERT is

Hello!

Do anybody know how to fix this error?

Cannot insert explicit value for identity column in table 'Gallery' when IDENTITY_INSERT is set to OFF.?

Thanks!
Varcar!Don't insert values into the identity column, or set identity_insert on?

Cannot insert explicit value for identity column (was "Error")

Cannot insert explicit value for identity column in table 'tblUsed' when IDENTITY_INSERT is set to OFF.
:confused:Then either set it to on or post why you need to insert into the Autonumber field

Cannot insert explicit value for identity column

SQL Server 2000 SP4 (Enterprise edition) both publisher and Subscriber.
I have setup Identity column 'Not for Replication' in subscriber.
My error is
Cannot insert explicit value for identity column in table 'tablename' when
IDENTITY_INSERT is set to OFF.
I ran
SET IDENTITY_INSERT 'tablename' ON
I am still getting same error. Pl help
TIA
Sandeep
I am running transactional replication.
Thanks,
Sandeep
"Sandeep Shankar" <sandeep@.discussions.microsoft.com> wrote in message
news:%23yzPifMKIHA.1208@.TK2MSFTNGP03.phx.gbl...
> SQL Server 2000 SP4 (Enterprise edition) both publisher and Subscriber.
> I have setup Identity column 'Not for Replication' in subscriber.
> My error is
>
> Cannot insert explicit value for identity column in table 'tablename' when
> IDENTITY_INSERT is set to OFF.
>
> I ran
> SET IDENTITY_INSERT 'tablename' ON
>
> I am still getting same error. Pl help
> TIA
> Sandeep
>

Cannot find the identity range allocation entry for the Subscriber

Dear All,
When I am synchronizing the merge push subscription, receiving following error
Cannot find the identity range allocation entry for the Subscriber in the
MSmerge_identity_range table. Reinitialize the subscription.
What could be the cause of message, and is there some way so I can avoid
re-initialization of susbscription
How did you deploy this subscriber? Did you do a no-sync?
"M. M. Ansari" <MMAnsari@.discussions.microsoft.com> wrote in message
news:15A3AA2B-9F5F-45BC-BBC7-4C48FEF239E7@.microsoft.com...
> Dear All,
> When I am synchronizing the merge push subscription, receiving following
> error
> Cannot find the identity range allocation entry for the Subscriber in the
> MSmerge_identity_range table. Reinitialize the subscription.
> What could be the cause of message, and is there some way so I can avoid
> re-initialization of susbscription
>