March 11, 2008 at 6:44 am
I cannot seem to get a bcp in native format to work to transfer data between 2000 and 2005. I've reduced the data to just one column, for simplicity, and the insert gets only 41,952 of the 500,000 rows I am sending.
This is so simple, why does it not work?
Here's the export:
bcp "SELECT top 500000 CustomerID from saint.dbo.customers where customerid > 0" queryout
customers.txt -S"myservername" -T -N
And the import:
bcp oasis.dbo.customers in e:\xfer\customers.txt -T -N
I've checked, the table oasis.dbo.customers has one field which is type int, which is exactly the type of the customerid field in the source table.
If I use Unicode (-w) instead of Native (-N), it works, but is slow.
Clues?
March 12, 2008 at 4:46 am
Tina,
Are you sure that the export works correctly? I heard about various errors caused by "queryout" parameter and I prefer rather to create a view and export the view with "out" parameter, e.g.:
use [saint]
go
create view MyCustomers as
SELECT top 500000 CustomerID from customers where customerid > 0
and: bcp saint.dbo.MyCustomers out customers.dat -S"myservername" -T -N
BTW, can customerid be <= 0 ?
It would be helpful if you redirect the output of the BCP command (both of them) to a file and show us these files. There will be the number of records (both exported as well imported).
Marek
March 12, 2008 at 5:29 am
I ended up solving this by doing two things:
1. Creating a view to eliminate the use of queryout
2. Creating a format file to use on import
For some reason, you are right, the queryout creates a corrupt data file. SQL 2005 was able to import it, but the record count was incorrect and the data was crazy.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply