February 8, 2005 at 12:18 am
Dear All,
I am using the bcp utility to export a table into a text file. The table looks like this:
TABLE A
=======
Field A1 Field A2 Field A3 Field A4
=====================================================
a11_val a21_val NULL a41_val
a12_val a22_val <zero-length value> a42_val
a13_val a23_val a33_val a43_val
a14_val NULL NULL a43_val
The actual command I am using is:
bcp "Select * From <DB_Name>.<Owner_Name>.TableA Where <Condition>" QueryOut "<FileName>" /S"<Server_Instance_Name>>" /U"<User>" /P"<*****>" /m0 /n /w /C"RAW" /t"|"
The expected result in <FileName> is:
a11_val|a21_val||a41_val
a12_val|a22_val||a42_val
a13_val|a23_val|a33_val|a43_val
a14_val|||a43_val
Although the above command executes successfully with a message of "4 rows copied", my file gets truncated and it gives me the following:
a11_val|a21_val||a41_val
a12_val|a22_val|
Somehow, it does not handle the zero-length field.
Any ideas?
Regards,
Andreas
February 8, 2005 at 3:22 am
Not sure, really, but you seem to have some conflicting switches..
You're using native mode (/n) and unicode chars (/w) and also says that no conversion should occur (/CRAW)
What happens if you try this instead:
bcp "Select * From <DB_Name>.<Owner_Name>.TableA Where <Condition>" QueryOut "<FileName>" /S"<Server_Instance_Name>>" /U"<User>" /P"<*****>" /m0 /c /t"|"
... by using just /c you say 'plain ascii' basically (subject to eventual conversion, but you can adjust that later with the appropriate /C switch - note case difference)
/Kenneth
February 8, 2005 at 3:46 am
Dear Kenneth,
I have tried your suggestion but unfortunately it does not work. Please have in mind that the field in question is of type NVARCHAR. Also, the codepage used is 737 (Greek) as my database contains lots of data in Greek (I do not know whether this helps or not, or whether it explains the switches used in "bcp" - I came up with this combination few months ago using trial & error approach - I did not really have the time to explore this matter).
Regards,
Andreas
February 8, 2005 at 8:08 am
Does the <zero-length value> contain a CR or LF character?
/Kenneth
February 9, 2005 at 7:12 am
What is the actual value of <zero-length value>
If it contains 026 0x1A, then it represents ascii eof and the file will seem truncated to certain applications.
Far away is close at hand in the images of elsewhere.
Anon.
February 9, 2005 at 11:14 pm
To Kenneth:
It does not contain a CR or LF character.
To David:
The actual value is spaces. Somehow the user entered spaces for this field and the front end application did not trim this or set it to NULL.
Regards,
Andreas
February 10, 2005 at 5:02 am
Umm, well... spaces are not <zero-length values>, nor are they the same as NULL. They are charachters just like 'abc'. A bunch of spaces may however be treated differently (ie trimmed and such) depending on the tools involved.
But, judging from your example, I'm with David here... It does indeed look like an EOF char might cause this.
Are you sure that all there is in that column on that row is spaces and nothing else..? Have you checked every position?
/Kenneth
February 10, 2005 at 5:21 am
Dear Kenneth,
I have checked all positions and indeed all there was there was spaces. It seems, as you have already mentioned, that bcp treats these values in a strange way which I could not figure out. However, I have "resolved" my problem by modifying the front end application so that this kind of values are inserted as NULLs. Existing values will be handles by a small SQL script.
Regards,
Andreas
February 10, 2005 at 8:00 am
Another thought struck me - how are you viewing the files?
I did some very quick and unscientific tests with data that had en EOF in it. BCP always reported all rows (as it should) and when I viewed the file in notepad, the file was complete, including the EOF char (which was embedded in some spaces). However... when I used the DOS command "type <myfile>", the output looked just like you showed in your example. So.. how do you verify that the file is indeed truncated? (ie what program/command/tool do you use)
As a sidenote on bcp switches.. on my version of bcp (8.00.382), if I use both /n and /w I get a message from bcp that states that /w overrides /n ... I assume this is also the case with you? There is no way a file can be created both in native and unicode format, so I still believe that you using the /n switch is redundant. It would be enough with just one of them, depending on which is preferrable.
/Kenneth
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply