markandrew
3/26/2007 1:00:00 AM
On Mar 21, 9:34 am, "Mike C#" <x...@xyz.com> wrote:
> <markand...@dbs.com> wrote in message
>
> news:1174440129.994348.177310@n59g2000hsh.googlegroups.com...
>
>
>
>
>
> >I am using SQL 2005, transferring data from Sybase on AIX via text
> > file to SQL table(s).
>
> > The file arrives as a compressed .gz file which is extracted to a .dat
> > file.
> > This is a comma delimited text file with no header row.
>
> > When I use the following BCP script
>
> > bcp "RatesMastr.dbo.AssetDefinition" in "D:\RatesData\Output
> > \AssetDefinition.dat" -Sserver -Uuser -Ppwd -c -t"," >> D:\RatesData
> > \Output\AssetDefinition.log
>
> > I get he following error
> > SQLState = 22001, NativeError = 0
> > Error = [Microsoft][ODBC SQL Server Driver]String data, right
> > truncation
>
> > If I open the file (in VB), read each line into a .txt file the same
> > script (changed to .txt) works.
>
> > The AssetDefinition table is correctly formatted and received the .TXT
> > file with no problem.
> > I have also tried the .DAT file to a table where all fields are
> > varchar(255), this fails with the same error.
>
> > Conclusion is that there is something wrong with the rowdelimiter, I
> > have tried -r\n and -r\r with no positive effect
>
> > Any help will be appreciated
>
> According to BOL you need to use the -t field terminator like this:
>
> -t ,
>
> Since the field terminator can be "a string of up to 10 printable
> characters", bcp might think that your field terminator is actually "," with
> the quotes. Try specifying -t , without the quotes. If that doesn't work,
> consider what BOL says about row terminators:
>
> "When you use bcp interactively and specify \n (newline) as the row
> terminator, bcp prefixes the \r (carriage return) character automatically."
>
> If the terminator character is \n alone, you might need to create a bcp
> format file. Try loading the data file into an editor (like TextPad) in
> binary mode and see if you can determine exactly what the line terminator
> character(s) actually is(are).- Hide quoted text -
>
> - Show quoted text -
The field terminator is not the problem, the "," works perfectly in
the .TXT file.
I beleive it is the rowterminator which I think is a line feed only.
The problem seems to be that I cannot define the rowterminator as a
linefeed only - BCP wants to add a carriagereturn character as well
and this is missing from the data file. Therefore when I read each
line the crlf is added correctly and BCP works.
Is there any way to force a chr(10) to be the linefeed.