0

I am currently trying to BCP out of a Sybase ASE database (15.7) into BCP into SQL Server (2016). All BCP tasks are being run on Windows environments.

As I have records with CRLF characters, I have needed to specify a custom record terminator to overcome this issue. The terminator I would have liked to use is "~\t\t\t\t\t~\r\n". I have added the CRLF characters for readability in case I need to open the files. The Out command is as follows:

bcp database..table out table.dat -Sserver -Uuser -Ppassword -c -t "|\t\t\t\t\t|" -r "~\t\t\t\t\t~\r\n"

When I BCP back in to SQL Server, I am getting a "String data, right truncation error". The command is as follows:

bcp database..table in table.dat -SServer -T -c -t "|\t\t\t\t\t|" -r "~\t\t\t\t\t~r\n"

I am using the ASE 15.7 SP126 version of BCP for out. I am using the SQL Server 2016 version of BCP (13.0.1601.5) for in.

Based on the thread here, I have determined that the issue is with the \r\n characters used for the -r option. If I test just using a -r "\r\n" on the out and a -r "0x0a" on the in, it works (apart from failing on the records where I have CRLF's in the data).

As soon as I remove the \r\n characters on the out and the in, I can BCP in the data successfully.

Does anyone know if is any way I can used a combination of printable characters + a CRLF?

Cheers.

Ash
  • 390
  • 4
  • 18

1 Answers1

1

If you're using MSSQL's bcp for pulling data from ASE, you may want to consider using ASE's bcp for pulling data from ASE if simply to rule out any issues between the 2 programs. [See this thread - Sybase bcp vs MSSQL bcp - for details; in particular note the comment: They definitely do not handle output to a file the same way and the docs for each show that.]

Why the insistence on using CRLF (and to a lesser extent all the \t's) in the terminator? Any reason why you can't use some other printable character combinations you know don't show up in your data, eg:

bcp ... -t"|&" -r "|&&" ...
bcp ... -t"##&&" -r "||&&" ...
markp-fuso
  • 28,790
  • 4
  • 16
  • 36
  • Sorry - should have mentioned that. I am using the ASE version of BCP (15.7 SP126). I'll update the OP. As for using the CRLF, its purely for readability. As for the tabs, I tried combo's of other characters, but found that I had false matches in some hashed fields. Tabs was suggested by someone else in the organisation, so I went with that. – Ash Jun 22 '17 at 02:05
  • 1
    short of tracking down an offending record, looking at the actual data (od -c), and figuring out which field is generating the error ... and knowing from testing that removing the CRLF characters fixes the problem ... I'd probably go with removing the CRLF characters for an immediate solution; then when I've got some free time I'd probably look into the issue more closely (if simply as a learning exercise) – markp-fuso Jun 22 '17 at 03:05
  • Thanks, yes, that is the current solution. The more I think about it, the more I conclude that the chances of the file being read by a human are slim, so its probably a non issue (the biggest I have seen is 35GB). If I have to open one, I'll just have to munge it with something. – Ash Jun 22 '17 at 06:35