Re: Cannot insert explicit value for identity...

(nicolas.bouchard@xxxxxxxxx) 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@xxxxxxxxxxxxx

Books Online for SQL Server 2005 at
Books Online for SQL Server 2000 at

Relevant Pages

  • Re: msde2000
    ... Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) is the free, ... As for it being better than MS-Access, the answer to that is probable yes, ... MOST other database DO NOT. ...
  • Re: Database Connectivity
    ... But, MSDE has been know to work very reliably in small user LANS, ... > just a simple step to attach the tables from MSDE to SQL Server. ... database, or pay for an "industrial-strength" version of it when there ... Don't like Microsoft?, sounds like a personal problem to me. ...
  • Re: Theres a new Borland Delphi directions survey up
    ... Choosing and Using MSDE 2000 as the Database Engine for Your Application ... The Microsoft SQL Server 2000 Desktop Engine is an alternative ... database engine in Microsoft Access 2002 which enables software developers ...
  • Re: msde2000
    ... MSDE is the desktop version of SQL server. ... Basically, Access is a desktop application, MSDE is a database backend. ... go through OLEDB, so theoretically therefore it's arguably the fastest ...
  • RE: MSDE, Access, SharePoint... I am not sure where to ask this qu
    ... 1.>> What is the difference between MSDE and WMSDE? ... MSDE that is targeted for use by Windows components only. ... isn't SQL Server Express replacing MSDE and WMSDE? ... Microsoft CSS Online Newsgroup Support ...