Hi,

My apologies if this question is not suitable to be posted here..

We are facing an issue while moving a Sql 6.5 database to a Sql 2000
server using bcp out/bcp in.
We have moved several databases from Sql 6.5 to Sql 2000 earlier by
scripting out and then bulk copying data using bcp, but now having
issues with this database, specifically with one table.

The table in question has a combined primary key of four columns out
of which two columns are of datetime datatype. I performed the
following steps for copying the table data to target Sql 2000 db:
1 - recreated the table in target Sql 2000 db with script from source
Sql 6.5 db (without creating the primary key)
2 - pulled data from source Sql 6.5 db using bcp out, and then
inserted the data to target Sql 2000 table using bcp in.
3 - tried to recreate the primary key on the target table. This is
when I get the following error:
CREATE UNIQUE INDEX terminated because a duplicate key was found for
index ID 1. Most significant primary key is '549994615'.
Server: Msg 1750, Level 16, State 1, Line 3
Could not create constraint. See previous errors.
The statement has been terminated.

The primary key is existing in the source Sql 6.5 database.
I checked that there are several duplicate values in the target table
for this combined primary key.
With bcp out, the datetime data in the bcp output file are of the
format for example 'Nov 18 2005 12:00AM', and while I query the data
in the db, it shows in the format '2005-11-18 00:00:00.000'.

So in the source Sql 6.5 database(primary key exists here!!) as well
the target Sql 2000 db(pk creation gives error), I see the same set of
duplicate data for the primary key columns with datetime in the format
'2005-11-18 00:00:00.000'.

My confusion is - if these data values are duplicate, how can the
primary key exist in the source 6.5 database ??
Or, am I missing to copy the datetime data properly from Sql 6.5 to
Sql 2000 with bcp out/in ?? Is there any other option for me to copy
the source table with the constraints etc as it is to the target Sql
2000 instance..?

Please let me know if I could not explain this properly, or if some
sample data is needed to make myself understood..

Thanks in advance for your guidance ..!!

Regards,
Himansu

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

just55 (hvmallick (AT) gmail (DOT) com) writes:
It sounds a bit mysterious, but permit me to start with a known gotcha.

Which BCP do you use to BCP out, and which format do you use?

If you use BCP 6.5 and character format, be aware of that BCP 6.5 will give
you only hours and minutes. If there are datetime values with differences
in seconds and milliseconds only, this could explain the PK errors.

There are two alternative ways to preserve seconds and milliseconds. One if
to use native format. However, if you have columns of the sysname data type,
this will fail, since the definition of this type is different in SQL 2000.
The other alternative is to use BCP 2000 to get the data from SQL 6.5.
Then you can still use character format.


--
Erland Sommarskog, SQL Server MVP, esquel (AT) sommarskog (DOT) se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx