473,327 Members | 2,081 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,327 software developers and data experts.

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.

Apr 24 '06 #1
3 21838
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 !

Apr 24 '06 #2
(ni**************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx
Apr 24 '06 #3
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

Apr 25 '06 #4

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

2
by: Jack | last post by:
Hi, I got a simple form where one needs to input data. The data is being processed in an asp page. However, I cannot figure out why couple of values I am typing in the form is not being retrived...
2
by: Eugene | last post by:
Hi, Problem: I need to get the value of auto-incremented field from just inserted record In Oracle this is INSERT .. RETURNING command. In SQL Server there are @@IDENTITY, IDENT_CURRENT,...
4
by: brent.ryan | last post by:
How do I get the next int value for a column before I do an insert in MY SQL Server 2000? I'm currently using Oracle sequence and doing something like: select seq.nextval from dual; Then I...
8
by: Bri | last post by:
Greetings, I'm having a very strange problem in an AC97 MDB with ODBC Linked tables to SQL Server 7. The table has an Identity field and a Timestamp field. The problem is that when a new record...
3
by: Fabio Negri Cicotti [MCP] | last post by:
Hi All. I'm trying to insert data into 2 tables (parent-child) using the ADO.NET's SetParentRow method. The parent table has an identity column as primary key. When I execute the code below I...
1
by: nicholas | last post by:
To insert a record in a Ms Access database and be able to retrieve the newly created ID (autonumber) I used the code below (code 1). Now, the problem is that this is not very secure and that, if...
8
by: carlospedr | last post by:
I have to insert data from about 30 tables into a single table (Users), to do so i used a cursor and a bit of dynamic sql, this should work fine if the tables have to do the select from had the...
6
by: Christopher Lusardi | last post by:
How can I fix this? When I do the below I get the error message: "Cannot insert explict value for identity column in table 'Employees' when IDENTITY_INSERT is set to OFF." To get this message,...
4
by: Dabbler | last post by:
I'm using an SQLCommand to insert row using a text command. Is there a way to return the IDENTITY key value after the insert ? Thanks much!
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.