Re: Issue with date move from Sql 6.5 to Sql 2000 using bcp

Tech-Archive recommends: Fix windows errors by optimizing your registry

On Jun 19, 10:19 am, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:
just55 (hvmall...@xxxxxxxxx) writes:
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..?

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, esq...@xxxxxxxxxxxxx

Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

Hi,

Thanks for the reply. I am using BCP 6.5 with character mode. As you
pointed out, it may not be copying the seconds/milli seconds info to
the target.
The table does not have any sysname datatype. I tried BCP from sql 6.5
to Sql 2000 with the native mode as you suggested, but got the
following error:
Starting copy...
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right
truncation
SQLState = 22001, NativeError = 0

I checked that both the source and target tables have the same
columns, datatypes and lengths.
Not sure here about the error.

You suggested the second alternative for BCP 2000 to get the data from
SQL 6.5..
Would you please elaborate how this can be achieved..

Thanks a lot!!

Regards,
Himansu
.

Relevant Pages

  • Re: Identity Block Reserved For Inserts Issue - Are you good?
    ... I have a bit of a complicated question, hope we have an SQL guru out there ... Because we need to get all the linking before we BCP, ... used or were used by a competing process that uses only automated identity ... Use lock hints to lock out the competing processes until you're ...
    (microsoft.public.dotnet.languages.csharp)
  • Identity Block Reserved For Inserts Issue - Are you good?
    ... I have a bit of a complicated question, hope we have an SQL guru out there ... Because we need to get all the linking before we BCP, ... push in from BCP. ... There is a lag between getting the block and uploading using BCP of anywhere ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: FTP Snapshot errors
    ... the bcp operation in SQL 2005 will automatically restart on failure. ... try to manually bcp the data in the subscriber and see what error message ... Looking for a SQL Server replication book? ...
    (microsoft.public.sqlserver.replication)
  • RE: Identity Block Reserved For Inserts Issue - Are you good?
    ... I have a bit of a complicated question, hope we have an SQL guru out there ... Because we need to get all the linking before we BCP, ... push in from BCP. ... There is a lag between getting the block and uploading using BCP of anywhere ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: Issue with date move from Sql 6.5 to Sql 2000 using bcp
    ... am I missing to copy the datetime data properly from Sql 6.5 to ... 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 ... Erland Sommarskog, SQL Server MVP, esquel@xxxxxxxxxxxxx ...
    (microsoft.public.sqlserver.tools)