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.

.

Relevant Pages

  • RE: How to configure WSS to use SQL server instead of MSDE
    ... This is why, in this post, I wanted to focus on only WSS features. ... Apparently WSS DOES NOT SUPPORT SQL Server since it has no tool (that we ... >>MSDE databases for its configuration and content ...
    (microsoft.public.sharepoint.windowsservices)
  • Re: Newbie Questions
    ... I have noticed that some of the documentation talkes about SQL Server ... > MSDE is the old database engine there is now as well SQL server Express, ... > hard to find (mostly everything is done with scripting ASP style). ... How do I use the MSDE functionality in relation to databases? ...
    (microsoft.public.dotnet.languages.vb)
  • Re: Relocate MSDE 2000 database?
    ... Most of the 2000 versions of SQL Server and thus MSDE have been pulled. ... It can be purchased for about $40 and can manage any version of SQL Server. ... It will be increasingly expensive to support no-supported databases as time goes on. ... post -- these databases are installed and deployed by 3rd party apps ...
    (microsoft.public.sqlserver.msde)
  • Re: Change default to named instance??
    ... Take a backup of all databases ... Stop SQL Server and copy the MDF and LDF to a safe place ... Un Install the MSDE ... > conflicts with other app's msdes, I now want to use a named instance. ...
    (microsoft.public.sqlserver.msde)
  • Re: Cannot insert explicit value for identity...
    ... The main database is the SQL Server, the MSDE will contain a really ... of time taken to develop an integration process between those ...
    (comp.databases.ms-sqlserver)