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
>