Hi Terry,

Sorry, I'm not sure the real situation. Would you please post the exact
reproduce steps?

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Terry" <terrymercer@iniinc.com>
| Sender: "Terry" <terrymercer@iniinc.com>
| Subject: Duplicate PK error
| Date: Wed, 13 Aug 2003 20:41:28 -0700
| Lines: 26
| Message-ID: <0a7901c36215$f1a9d3e0$a301280a@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNiFfGpe/imAsRYQ+6EWiRqclVECQ==
| Newsgroups: microsoft.public.sqlserver.dts
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:37313
| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| I am attempting to use DTS from an Access 2002 database
| table to import data into a SQL Server 2000 table. PK
| entry in SQL Server table is created by a trigger.
|
| SELECT @maxc = (SELECT Max(TBL_ID) FROM SQL_ServerTable)
| SELECT @newc = (SELECT TBL_ID FROM inserted)
| IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
| IF @newc <> 0 or @newc <> null SELECT @maxc = @newc
| UPDATE SQL_ServerTable SET TBL_ID = @maxc WHERE TBL_ID =
| @newc
| GO
|
| SQL_ServerTable.TBL_ID int is not an identity and has a
| default of 0
|
| When using Access 2002 and ODBC to Link to
| SQL_ServerTable then use an append query which does not
| include TBL_ID to append the rows the SQL Server trigger
| fires and a new TBL_ID is generated for each row.
| However, when using DTS and do not include the TBL_ID
| column or include the TBL_ID column with null values I
| receive an error Violation of PRIMARY KEY
| constraint 'TS_PRIMARY'. Cannot insert duplicate key in
| object 'SQL_ServerTable'.
|
| Thanks for any suggestions.
|

Hi Terry,

Thank you for your update, I am still looking into this issue and hope to
update you today or tommorow. Thank you for you patience.

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Terry" <terrymercer@iniinc.com>
| Sender: "Terry" <terrymercer@iniinc.com>
| References: <0a7901c36215$f1a9d3e0$a301280a@phx.gbl>
<ki78x90YDHA.2592@cpmsftngxa06.phx.gbl>
| Subject: RE: Duplicate PK error
| Date: Fri, 15 Aug 2003 15:48:37 -0700
| Lines: 163
| Message-ID: <0a5001c3637f$5d7793e0$a001280a@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNjf111p3ucvmQ5RT+dw0hbQypDcA==
| Newsgroups: microsoft.public.sqlserver.dts
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:37425
| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| To replicate the issue:
| Create on a SQL Server 2000 sp3 the following objects:
| A new database or place these objects into an existing
| database
|
| /****** Object: Table dbo.SQL_ServerTable ******/
| CREATE TABLE dbo.SQL_ServerTable (
| TBL_ID int NOT NULL CONSTRAINT
| DF_SQL_ServerTable_TBL_ID DEFAULT (0),
| CL_CODE int NOT NULL ,
| TITLE_1 varchar (34) NULL ,
| TITLE_2 varchar (34) NULL ,
| VOL_NO varchar (9) NULL ,
| ts timestamp NULL
| )
| GO
|
| /****** Object: Table dbo. SQL_ServerTable ******/
| ALTER TABLE dbo. SQL_ServerTable WITH NOCHECK ADD
| CONSTRAINT TSQL_PRIMARY PRIMARY KEY CLUSTERED
| (
| TBL_ID
| )
| GO
|
| /****** Object: Trigger dbo.SQL_ServerTable_ITrig ******/
| CREATE TRIGGER SQL_ServerTable_ITrig ON SQL_ServerTable
| FOR INSERT AS
| DECLARE @maxc int, @newc int
| /** COUNTER-EMULATION CODE FOR FIELD 'TBL_ID' **/
| SELECT @maxc = (SELECT Max(TBL_ID) FROM SQL_ServerTable)
| SELECT @newc = (SELECT TBL_ID FROM inserted)
| IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
| IF @newc <> 0 or @newc <> null SELECT @maxc = @newc
|
| UPDATE SQL_ServerTable SET TBL_ID = @maxc WHERE TBL_ID =
| @newc
| GO
| Add a few records to SQLServerTable
|
| Create an Access 2002 database AccXPdb1.mdb
| Create in AccXPdb1.mdb a table AccessXP_Table
| CL_CODE Number long integer NOT NULL
| TITLE_1 Text (34) NULL
| TITLE_2 Text (34) NULL
| VOL_NO Text (9) NULL
|
| Add a few rows to AccessXP_Table
|
| Create an ODBC DSN to SQL Server 2000 database containing
| SQL_ServerTable
|
| In AccXPdb1.mdb
| link to SQL_ServerTable
| Create an Append query Source AccessXP_Table
| Append to SQL_ServerTable
|
| The records will be appended and the TBL_ID column will
| increment as expected.
|
| Use SQL Server Enterprise Manager
| Tools, Data Transformation Services, Import Data., Data
| Transformation Services Import/Export Wizard
| Data Source = Microsoft Access
| File name = <path>AccXPdb1.mdb
| Destination = Microsoft OLDB Provider for SQL Server <<IF
| Microsoft ODBC Driver for SQL Server is selected here the
| records are inserted correctly. HOWEVER, it took 3
| minutes to insert 10,500 records>>
| Server = YOUR SQL Server
| Database = YOUR database containing SQL_ServerTable
| Use a Query to specify the data to transfer
| select query builder
| select all columns from AccessXP_Table
| No sort Order
| Select Destination as SQL_ServerTable
| An error is returned as follows:
| Violation of PRIMARY KEY constraint 'TS_PRIMARY'. Cannot
| insert duplicate key in object 'SQL_ServerTable'.
|
| >-----Original Message-----
| >Hi Terry,
| >
| >Sorry, I'm not sure the real situation. Would you please
| post the exact
| >reproduce steps?
| >
| >Sincerely,
| >
| >William Wang
| >Microsoft Partner Online Support
| >
| >Get Secure! - www.microsoft.com/security
| >=====================================================
| >When responding to posts, please "Reply to Group" via
| >your newsreader so that others may learn and benefit
| >from your issue.
| >=====================================================
| >
| >This posting is provided "AS IS" with no warranties, and
| confers no rights.
| >--------------------
| >| Content-Class: urn:content-classes:message
| >| From: "Terry" <terrymercer@iniinc.com>
| >| Sender: "Terry" <terrymercer@iniinc.com>
| >| Subject: Duplicate PK error
| >| Date: Wed, 13 Aug 2003 20:41:28 -0700
| >| Lines: 26
| >| Message-ID: <0a7901c36215$f1a9d3e0$a301280a@phx.gbl>
| >| MIME-Version: 1.0
| >| Content-Type: text/plain;
| >| charset="iso-8859-1"
| >| Content-Transfer-Encoding: 7bit
| >| X-Newsreader: Microsoft CDO for Windows 2000
| >| X-MimeOLE: Produced By Microsoft MimeOLE
| V5.50.4910.0300
| >| Thread-Index: AcNiFfGpe/imAsRYQ+6EWiRqclVECQ==
| >| Newsgroups: microsoft.public.sqlserver.dts
| >| Path: cpmsftngxa06.phx.gbl
| >| Xref: cpmsftngxa06.phx.gbl
| microsoft.public.sqlserver.dts:37313
| >| NNTP-Posting-Host: TK2MSFTNGXA11 10.40.1.163
| >| X-Tomcat-NG: microsoft.public.sqlserver.dts
| >|
| >| I am attempting to use DTS from an Access 2002
| database
| >| table to import data into a SQL Server 2000 table. PK
| >| entry in SQL Server table is created by a trigger.
| >|
| >| SELECT @maxc = (SELECT Max(TBL_ID) FROM
| SQL_ServerTable)
| >| SELECT @newc = (SELECT TBL_ID FROM inserted)
| >| IF @newc = 0 or @newc = null SELECT @maxc = @maxc
| + 1
| >| IF @newc <> 0 or @newc <> null SELECT @maxc =
| @newc
| >| UPDATE SQL_ServerTable SET TBL_ID = @maxc WHERE TBL_ID
| =
| >| @newc
| >| GO
| >|
| >| SQL_ServerTable.TBL_ID int is not an identity and has
| a
| >| default of 0
| >|
| >| When using Access 2002 and ODBC to Link to
| >| SQL_ServerTable then use an append query which does
| not
| >| include TBL_ID to append the rows the SQL Server
| trigger
| >| fires and a new TBL_ID is generated for each row.
| >| However, when using DTS and do not include the TBL_ID
| >| column or include the TBL_ID column with null values I
| >| receive an error Violation of PRIMARY KEY
| >| constraint 'TS_PRIMARY'. Cannot insert duplicate key
| in
| >| object 'SQL_ServerTable'.
| >|
| >| Thanks for any suggestions.
| >|
| >
| >.
| >
|

Hi Terry,

Sorry for the delay response. According to your reproduce steps, I have
reproduced this issue on my side and I would like to list the exact
reproduce steps that are some different from yours in order to make the
explaination more clearly. We don't have much to do with Access since the
issue is mainly related to SQL.

1) Create a table with 2 columns. The table will be destination table.

CREATE TABLE D_TBL
( ID INT NOT NULL DEFAULT (0) PRIMARY KEY,
NUM INT NOT NULL
)
GO

2) Create a trigger on D_TBL

CREATE TRIGGER D_TBL_ITrig ON D_TBL
FOR INSERT AS
DECLARE @maxc int, @newc int
/** COUNTER-EMULATION CODE FOR FIELD 'ID' **/
SELECT @maxc = (SELECT Max(ID) FROM D_TBL)
SELECT @newc = (SELECT ID FROM inserted)
IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
IF @newc <> 0 or @newc <> null SELECT @maxc = @newc

UPDATE D_TBL SET ID = @maxc WHERE ID = @newc
GO

3) Insert a row into table D_TBL

INSERT INTO D_TBL VALUES (0, 1)
INSERT INTO D_TBL VALUES (0, 2)

SELECT * FROM D_TBL
GO
--------------------------------------------------
Returns:

ID NUM
----- -----
1 1
2 2

(1 row(s) affected)

It works fine just as expected.

4) Create a table with only one column as source table

CREATE TABLE S_TBL
( NUM INT NOT NULL
)
GO

5) Insert a row into S_TBL

INSERT INTO S_TBL VALUES (3)
GO

6) Start Data Transformation Services Import/Export Wizard,
Data Source = Microsoft OLE DB Provider for SQL Server
Destination = Microsoft OLE DB Provider for SQL Server
Use a Query to specify the data to transfer
select query builder
select all columns from S_TBL
Select Destination as D_TBL

Click Finish, the process should complete successfully!

7) SELECT * FROM D_TBL

ID NUM
---- ----
0 3
1 1
2 2

(3 row(s) affected)

Now we can see that the ID of the newest inserted row is 0, not 3 expected.
Just as you described, if I choose Microsoft ODBC Driver for SQL Server as
Destination, the expected result will return

ID NUM
---- ----
1 1
2 2
3 3

I believe with "Destination = Microsoft OLDB Provider for SQL Server", the
INSERT command is not used because it is slow, so that the trigger for
insert will no be fired. Since the trigger is not fired, every row
inserted(append) to the destination table(D_TBL) will cause the field ID to
be 0 which is by your design. ID is primary key, so we will get the error
message "Violation of PRIMARY KEY constraint".

Sincerely,

William Wang
Microsoft Partner Online Support

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via
your newsreader so that others may learn and benefit
from your issue.
=====================================================

This posting is provided "AS IS" with no warranties, and confers no rights.
--------------------
| Content-Class: urn:content-classes:message
| From: "Terry" <terrymercer@iniinc.com>
| Sender: "Terry" <terrymercer@iniinc.com>
| References: <0a7901c36215$f1a9d3e0$a301280a@phx.gbl>
<ki78x90YDHA.2592@cpmsftngxa06.phx.gbl>
| Subject: RE: Duplicate PK error
| Date: Fri, 15 Aug 2003 15:48:37 -0700
| Lines: 163
| Message-ID: <0a5001c3637f$5d7793e0$a001280a@phx.gbl>
| MIME-Version: 1.0
| Content-Type: text/plain;
| charset="iso-8859-1"
| Content-Transfer-Encoding: 7bit
| X-Newsreader: Microsoft CDO for Windows 2000
| X-MimeOLE: Produced By Microsoft MimeOLE V5.50.4910.0300
| Thread-Index: AcNjf111p3ucvmQ5RT+dw0hbQypDcA==
| Newsgroups: microsoft.public.sqlserver.dts
| Path: cpmsftngxa06.phx.gbl
| Xref: cpmsftngxa06.phx.gbl microsoft.public.sqlserver.dts:37425
| NNTP-Posting-Host: TK2MSFTNGXA08 10.40.1.160
| X-Tomcat-NG: microsoft.public.sqlserver.dts
|
| To replicate the issue:
| Create on a SQL Server 2000 sp3 the following objects:
| A new database or place these objects into an existing
| database
|
| /****** Object: Table dbo.SQL_ServerTable ******/
| CREATE TABLE dbo.SQL_ServerTable (
| TBL_ID int NOT NULL CONSTRAINT
| DF_SQL_ServerTable_TBL_ID DEFAULT (0),
| CL_CODE int NOT NULL ,
| TITLE_1 varchar (34) NULL ,
| TITLE_2 varchar (34) NULL ,
| VOL_NO varchar (9) NULL ,
| ts timestamp NULL
| )
| GO
|
| /****** Object: Table dbo. SQL_ServerTable ******/
| ALTER TABLE dbo. SQL_ServerTable WITH NOCHECK ADD
| CONSTRAINT TSQL_PRIMARY PRIMARY KEY CLUSTERED
| (
| TBL_ID
| )
| GO
|
| /****** Object: Trigger dbo.SQL_ServerTable_ITrig ******/
| CREATE TRIGGER SQL_ServerTable_ITrig ON SQL_ServerTable
| FOR INSERT AS
| DECLARE @maxc int, @newc int
| /** COUNTER-EMULATION CODE FOR FIELD 'TBL_ID' **/
| SELECT @maxc = (SELECT Max(TBL_ID) FROM SQL_ServerTable)
| SELECT @newc = (SELECT TBL_ID FROM inserted)
| IF @newc = 0 or @newc = null SELECT @maxc = @maxc + 1
| IF @newc <> 0 or @newc <> null SELECT @maxc = @newc
|
| UPDATE SQL_ServerTable SET TBL_ID = @maxc WHERE TBL_ID =
| @newc
| GO
| Add a few records to SQLServerTable
|
| Create an Access 2002 database AccXPdb1.mdb
| Create in AccXPdb1.mdb a table AccessXP_Table
| CL_CODE Number long integer NOT NULL
| TITLE_1 Text (34) NULL
| TITLE_2 Text (34) NULL
| VOL_NO Text (9) NULL
|
| Add a few rows to AccessXP_Table
|
| Create an ODBC DSN to SQL Server 2000 database containing
| SQL_ServerTable
|
| In AccXPdb1.mdb
| link to SQL_ServerTable
| Create an Append query Source AccessXP_Table
| Append to SQL_ServerTable
|
| The records will be appended and the TBL_ID column will
| increment as expected.
|
| Use SQL Server Enterprise Manager
| Tools, Data Transformation Services, Import Data., Data
| Transformation Services Import/Export Wizard
| Data Source = Microsoft Access
| File name = <path>AccXPdb1.mdb
| Destination = Microsoft OLDB Provider for SQL Server <<IF
| Microsoft ODBC Driver for SQL Server is selected here the
| records are inserted correctly. HOWEVER, it took 3
| minutes to insert 10,500 records>>
| Server = YOUR SQL Server
| Database = YOUR database containing SQL_ServerTable
| Use a Query to specify the data to transfer
| select query builder
| select all columns from AccessXP_Table
| No sort Order
| Select Destination as SQL_ServerTable
| An error is returned as follows:
| Violation of PRIMARY KEY constraint 'TS_PRIMARY'. Cannot
| insert duplicate key in object 'SQL_ServerTable'.
|
| >-----Original Message-----
| >Hi Terry,
| >
| >Sorry, I'm not sure the real situation. Would you please
| post the exact
| >reproduce steps?
| >
| >Sincerely,
| >
| >William Wang
| >Microsoft Partner Online Support
| >
| >Get Secure! - www.microsoft.com/security
| >=====================================================
| >When responding to posts, please "Reply to Group" via
| >your newsreader so that others may learn and benefit

Thanks to all who replied:
It appears the only way in this situation to use OLE db
and thereby be much faster than ODBC is to:
1. Find max +1 of final destination table (FDTbl)
2. Create new table (NTbl)with same columns as FDTbl with
exception of PK Column and make it an identity with SEED
of results of SELECT MAX (PK)+1 FROM FDTbl
3. DTS from Access db Source table to NTbl and let the
identity populate the column in NTbl
4. DTS from NTbl to FDTbl
5. Drop NTbl
I have created a DTS package to do everything except
SELECT MAX (PK) FROM FDTbl and place the results as the
SEED in the Create Table dbo.NTbl script.

If anyone has a suggestion on how to do this it would be
appreciated.

Terry


[quoted text, click to view]