MS SQL Server, BCP OUT Fixed-Length Record: Truncated Length

John B. Morro
#1 / 4

BCP OUT Fixed-Length Record: Truncated Length

Here's my BCP OUT format file -- it should explain my field structure:

6.0
13
1 SQLCHAR 0 10 "" 1 id
2 SQLCHAR 0 5 "" 2 prefix
3 SQLCHAR 0 25 "" 3 first
4 SQLCHAR 0 20 "" 4 middle
5 SQLCHAR 0 25 "" 5 last
6 SQLCHAR 0 5 "" 6 suffix
7 SQLCHAR 0 35 "" 7 address1
8 SQLCHAR 0 35 "" 8 address2
9 SQLCHAR 0 25 "" 9 city
10 SQLCHAR 0 2 "" 10 state
11 SQLCHAR 0 5 "" 11 zip5
12 SQLCHAR 0 4 "" 12 zip4
13 SQLCHAR 0 10 "\r\n" 13 phone

Challenge: I must export a fixed-length record to an outside client, and
all records must be exactly the same length.

Situation: In the above structure, some of the trailing fields (like ZIP4
and PHONE) are null

Problem: BCP is truncating the record if, for instance, PHONE field is
null. The record length should be 208 including CR/LF at end; if phone is
null, recl = 198.

Question: Does anybody know of a way to set BCP to export the "pure" fixed
length of each and every field, including any final fields which may
contain nulls?

I realize I could force-fill those nulls with zeroes or some such filler,
but I don't wanna pump filler into a 4-million record table -- that ain't
my style. In the meantime, my only solution is to set up this file
structure in Foxpro, import the BCP-ed file, and then export it. On
export, Fox will not truncate a record just because trailing fields are
empty. But this step will be time-consuming if I'm working with a million
records.

Any ideas?

John Morrow
WKA
Winston-Salem NC

Mon, 19 Feb 2001 03:00:00 GMT

Greg Druia
#2 / 4

BCP OUT Fixed-Length Record: Truncated Length

This might work: create a VIEW of your table in which NULLS are replaced by
filler characters. This will relieve you of the need to add filler to your
large table. Then BCP OUT the view.

Quote:

> Here's my BCP OUT format file -- it should explain my field structure:

> 6.0
> 13
> 1 SQLCHAR 0 10 "" 1 id
> 2 SQLCHAR 0 5 "" 2 prefix
> 3 SQLCHAR 0 25 "" 3 first
> 4 SQLCHAR 0 20 "" 4 middle
> 5 SQLCHAR 0 25 "" 5 last
> 6 SQLCHAR 0 5 "" 6 suffix
> 7 SQLCHAR 0 35 "" 7 address1
> 8 SQLCHAR 0 35 "" 8 address2
> 9 SQLCHAR 0 25 "" 9 city
> 10 SQLCHAR 0 2 "" 10 state
> 11 SQLCHAR 0 5 "" 11 zip5
> 12 SQLCHAR 0 4 "" 12 zip4
> 13 SQLCHAR 0 10 "\r\n" 13 phone

> Challenge: I must export a fixed-length record to an outside client, and
> all records must be exactly the same length.

> Situation: In the above structure, some of the trailing fields (like ZIP4
> and PHONE) are null

> Problem: BCP is truncating the record if, for instance, PHONE field is
> null. The record length should be 208 including CR/LF at end; if phone is
> null, recl = 198.

> Question: Does anybody know of a way to set BCP to export the "pure" fixed
> length of each and every field, including any final fields which may
> contain nulls?

> I realize I could force-fill those nulls with zeroes or some such filler,
> but I don't wanna pump filler into a 4-million record table -- that ain't
> my style. In the meantime, my only solution is to set up this file
> structure in Foxpro, import the BCP-ed file, and then export it. On
> export, Fox will not truncate a record just because trailing fields are
> empty. But this step will be time-consuming if I'm working with a million
> records.

> Any ideas?

> John Morrow
> WKA
> Winston-Salem NC

Tue, 20 Feb 2001 03:00:00 GMT

John B. Morro
#3 / 4

BCP OUT Fixed-Length Record: Truncated Length

Quote:

> This might work: create a VIEW of your table in which NULLS are replaced
by
> filler characters. This will relieve you of the need to add filler to
your
> large table. Then BCP OUT the view.

Greg,

A very elegant and very creative solution! Especially since I'm a "view
guy" anyway; the export I'm doing is already a BCP from a view.

Question: How would you write the code to do the null replacement? I'm a
little fuzzy on that, since a view to me is simply a virtual snapshot of
real data; if I replace VIEW null fields with filler, am I not really
updating the originating data fields?

John

Tue, 20 Feb 2001 03:00:00 GMT

John B. Morro
#4 / 4

BCP OUT Fixed-Length Record: Truncated Length

Greg:

Thanks. Your suggestion prompted the following solution:

I added one element to my view statement -- the last "field" in my select
statement was simply a character (I used a zero) as filler. [syntax was 0
as "filler"]

Every record ended with a zero in the last byte, which made every record
the exact same fixed length. I simply adjusted my format file and the BCP
was perfect.

Gracias!

John Morrow
~~~~~~~~~~~~~~~~~

Quote:

> This might work: create a VIEW of your table in which NULLS are replaced
by
> filler characters. This will relieve you of the need to add filler to
your
> large table. Then BCP OUT the view.

> > Here's my BCP OUT format file -- it should explain my field structure:

> > 6.0
> > 13
> > 1 SQLCHAR 0 10 "" 1 id
> > 2 SQLCHAR 0 5 "" 2 prefix
> > 3 SQLCHAR 0 25 "" 3 first
> > 4 SQLCHAR 0 20 "" 4 middle
> > 5 SQLCHAR 0 25 "" 5 last
> > 6 SQLCHAR 0 5 "" 6 suffix
> > 7 SQLCHAR 0 35 "" 7 address1
> > 8 SQLCHAR 0 35 "" 8 address2
> > 9 SQLCHAR 0 25 "" 9 city
> > 10 SQLCHAR 0 2 "" 10 state
> > 11 SQLCHAR 0 5 "" 11 zip5
> > 12 SQLCHAR 0 4 "" 12 zip4
> > 13 SQLCHAR 0 10 "\r\n" 13 phone

> > Challenge: I must export a fixed-length record to an outside client,
and
> > all records must be exactly the same length.

> > Situation: In the above structure, some of the trailing fields (like
ZIP4
> > and PHONE) are null

> > Problem: BCP is truncating the record if, for instance, PHONE field is
> > null. The record length should be 208 including CR/LF at end; if phone
is
> > null, recl = 198.

> > Question: Does anybody know of a way to set BCP to export the "pure"
fixed
> > length of each and every field, including any final fields which may
> > contain nulls?

> > I realize I could force-fill those nulls with zeroes or some such
filler,
> > but I don't wanna pump filler into a 4-million record table -- that
ain't
> > my style. In the meantime, my only solution is to set up this file
> > structure in Foxpro, import the BCP-ed file, and then export it. On
> > export, Fox will not truncate a record just because trailing fields are
> > empty. But this step will be time-consuming if I'm working with a
million
> > records.

> > Any ideas?

> > John Morrow
> > WKA
> > Winston-Salem NC

Sun, 25 Feb 2001 03:00:00 GMT