Posted by Dan Guzman on 03/14/06 03:36

> Not sure if this will reproduce it, but it's all I've got. It may give
> someone a hint at what is going wrong. Thanks in advance.

I couldn't recreate your problem running this script from Query Analyzer.
Create a script that demonstrates the problem in your environment and then
post it here. If the error only occurs from your application, try Erland's
suggestion to use Profiler.

truncate table field_values
bulk insert field_values from 'c:\274.dat' with (FORMATFILE='c:\fa.txt');
begin tran
delete from field_values;-- where dealer_data_id = 274;
bulk insert field_values from 'c:\274.dat' with
(FORMATFILE='c:\fa.txt');
commit


--
Hope this helps.

Dan Guzman
SQL Server MVP

"Ryan" <ryanofford@hotmail.com> wrote in message
news:1142258815.563405.76180@v46g2000cwv.googlegroups.com...
>{ ADO.BEGINTRANS }
> delete from field_values where dealer_data_id = 274;
> bulk insert field_values from '274.dat' with ('fa.txt');
> { ADO.COMMIT }
> // Note there is no rollback on failure.
>
> 10-13:20:33.781xxx0863 Tdm.BulkInsert field_values from
> C:\WINNT\TEMP\HZSSTWZG1204493500.dat with
> (C:\WINNT\TEMP\XMROGIKB1204493500.dat)
> 10-13:20:33.781 ExecSQL.Exception caught by TSafeADOQuery Violation of
> PRIMARY KEY constraint 'PK_FIELD_VALUES'. Cannot insert duplicate key
> in object 'FIELD_VALUES'
> 10-13:20:33.781 xxx863 Tdm.BulkInsert Exception EAccessViolation
> (Access violation at address 004BE971 in module
> 'xxxxxxxxServer_Srvs_12.exe'
>
> fa.txt
> -------
> 8.0
> 4
> 1 SQLCHAR 0 0 "|" 1 dealer_data_id ""
> 2 SQLCHAR 0 0 "|" 2 field_code ""
> 3 SQLCHAR 0 0 "|" 3 field_value ""
> 4 SQLCHAR 0 0 "\r\n" 4 changed_type ""
>
>
> sample data from 274.dat
> -------------------------------------
>
> 274|S01|1.00000|0
> 274|S02|0.00000|0
> 274|S03|0.00000|0
> 274|S04|0.00000|0
> 274|S05|0.00000|0
> 274|S06|1.00000|0
> 274|S07|0.00000|0
> 274|S08|0.00000|0
> 274|S09|0.00000|0
> 274|S10|0.00000|0
> 274|S11|0.00000|0
> 274|S12|0.00000|0
> 274|S13|0.00000|0
> 274|S14|0.00000|0
> 274|S15|0.00000|0
> 274|S16|0.00000|0
> 274|S17|0.00000|0
> 274|S18|0.00000|0
> 274|S19|0.00000|0
> 274|S20|0.00000|0
> 274|S21|0.00000|0
> 274|S22|0.00000|0
> 274|S23|0.00000|0
> 274|S24|0.00000|0
>
>
> DDL
> ---------
>
> if exists (select * from dbo.sysobjects where id =
> object_id(N'[dbo].[FIELD_VALUES]') and OBJECTPROPERTY(id,
> N'IsUserTable') = 1)
> drop table [dbo].[FIELD_VALUES]
> GO
>
> CREATE TABLE [dbo].[FIELD_VALUES] (
> [DEALER_DATA_ID] [int] NOT NULL ,
> [FIELD_CODE] [varchar] (10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT
> NULL ,
> [FIELD_VALUE] [numeric](15, 5) NULL ,
> [CHANGED_TYPE] [int] NULL
> ) ON [PRIMARY]
> GO
>
> ALTER TABLE [dbo].[FIELD_VALUES] WITH NOCHECK ADD
> CONSTRAINT [PK_FIELD_VALUES] PRIMARY KEY CLUSTERED
> (
> [DEALER_DATA_ID],
> [FIELD_CODE]
> ) WITH FILLFACTOR = 90 ON [PRIMARY]
> GO
>
>
> Not sure if this will reproduce it, but it's all I've got. It may give
> someone a hint at what is going wrong. Thanks in advance.
>
> Ryan
>

[Back to original message]