DB-LIB and SQL 2005

Hello,

I've an application developped in VC ++ 6.0 using Db library for bulk copying flat files records to a SQL server (SQL server 7) table. We're trying to migrate to MSSQL 2005. While performing tests, the bcp command fails telling that "The primary key constraint has been violated'. All other Db lib command works with no problem. I've even changed the format file (.fmt) in eliminating the Primary key field as well as in the flat file (source file). But the bcp_exec command succeeds with no problem if i delete the primary constraint. Where's the problem with this new version of SQL SERVER 2005 ?

Thanks for your help

Let me see if I understand you correctly.

You have an application that copies a flat file into a SQL Server 2005 table using bcp_exec in the DB-Lib. When trying to do this, you receive the error "The primary key constraint has been violated". If you delete the primary key constraint on the table, bcp_exec then succeeds. Is this correct? Did the table in SQL Server 7 contain the primary key constraint as well but imports worked anyways?

You probably know this, but I'll state it anyways -- primary keys must be unique, including that they cannot be set to NULL. If I understand correctly, simply deleting the columns from the flat file and the format file will not work as that would try to put a default value of NULL in the primary key column, something not allowed.

Does the table in SQL Server 7 already have unique values in this column? If so, I'm surprised that there would be a problem, unless there's a collision with the data already in the table on SQL Server 2005.

I would suggest comparing the schemas. If they are identical, including constraints, then see if there is something amiss in the data, a NULL or something else. If there isn't, then try looking at what's already in the SQL Server 2005 table and compare that to what's in the flat file. Is there a conflict or collision there preventing the import?

If none of these is the case, please reply and we can see what else we can find.

|||

Thanks a lot for your reply.

The sql server 7.0 table contains the primary key constraint and the bcp_exec via DB-Lib works fine. The format file references this primary key and in the flat file that column contains 0 for each record. The field separator is ;. The table was created on SQLSERVER 2005 using the same commands as for SQL 7.

CREATE TABLE [dbo].[Table1] (
[Field1] [int] IDENTITY (1, 1) NOT NULL ,
etc
etc...
)
GO

ALTER TABLE [dbo].[Table1] WITH NOCHECK ADD
PRIMARY KEY CLUSTERED
(
[Field1]
) ON [PRIMARY]
GO

As i've explained earlier that when i found that the bcp_exec did'nt work with SQL 2005, i've complety removed the primary key (field1 in our ex.) from the format file and the Zros from the flat file. Nothing doing... this time bcp exec fails with no message.

Thanks a lot again