Re: CSV import failures in 2005.

How about scripting out each table and posting the definitions?

John

"Nathan Mellor" <nathan.d.mellor@xxxxxxxxx> wrote in message news:d1d1b3bf-ed1b-4552-98e2-555aefdd7744@xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx
On May 21, 3:38 pm, Erland Sommarskog <esq...@xxxxxxxxxxxxx> wrote:

That's the price you pay for clicking in GUI:s and try to second-guess
them. Why not simply use BCP to extract the data from SQL 2008 server,
and then use it to import?

As long as the databases have the same collation, you can do:

bcp db..tbl out tbl.bcp -n -T -S thatserver

And then to import, you change "out" to "in" and server name and database
name. (Replace -T with -U and -P if you use SQL authentication.)

If the there is a collation mismatch, you need to use a format file,
which still is simple:

bcp db..tbl format nul -n -f tbl.fmt -T -S thatserver

this creates the format file. Then add "-f tbl.fmt" to the commands above.

-n specifies native format. This means that numbers, dates etc cannot
be distorted by the conversion to text. It also means that the data file
will be somewhat smaller and the entire process is somewhat faster.


We are now trying the bcp with -n as you suggest. Ufortuantely, we are
getitng string truncations and unexpected end of file. We even tried
options like -V90 that seemed like a sure winner.

We've tried the command to generate a format file. But it seems to
*expect* a format file, not generate one. Are we supposed to handcode
a format file?

.

Relevant Pages

  • Re: CSV import failures in 2005.
    ... If the there is a collation mismatch, you need to use a format file, ... Then add "-f tbl.fmt" to the commands above. ... We are now trying the bcp with -n as you suggest. ...
    (microsoft.public.sqlserver.programming)
  • Re: BCP Nightly Extracts
    ... > application database to many extract files each night. ... To include the column header as part of the file, ... Unless the quotes are part of the data, you need to use a format file ... Books Online for SQL Server SP3 at ...
    (microsoft.public.sqlserver.programming)
  • RE: BCP Format Files
    ... Don't think I have ever used a format file for an extract. ... bcp will take a query and you can use that to format the data. ... You can also build up the extract in a glabal temp table with a single character column and extract that. ...
    (microsoft.public.sqlserver.programming)
  • Re: BCP Format Files
    ... >> Don't think I have ever used a format file for an extract. ... a query and you can use that to format the data. ... i never managed to import native format bcp files w/o a format file. ...
    (microsoft.public.sqlserver.programming)
  • pcap
    ... Anyone know of if there is a java class that extract every field of a pcap ...
    (comp.lang.java)
Quantcast