Fixed Length Output for Store Procedure Results

[Omnillas]

I am implementing a stored procedure to dump a list of
previous days work orders. The recipient of this lovely
list of work orders is utilizing an older system of fixed
length flat files for import and processing.

Each row in the output file is 500 bytes and must be 500
bytes to be cosidered a valid row before the receiving
process will even accept the data. Some data uses the full
500 bytes while some don't. Those that don't are padded out with blanks.

Consider the following short example:

Fields name    Size
-----------    -----
LName          15
FName          10
Item           12
-----------    ------
               37

Desired Output Rows ( - represents blanks )
=====================================
Jones----------Bill------Screwdrivers<eol>
Smith----------Carol-----Hammer------<eol>
Vitterman------Tom-------Pipe Wrench-<eol>

Results Per General Execution ( - for blanks)
=====================================
Jones----------Bill------Screwdrivers<eol>
Smith----------Carol-----Hammer<eol>
Vitterman------Tom-------Pipe Wrench<eol>

I am finding that the SP is truncating the end of the
records of the blank spaces which are needed by the
receiving system. Know of any good way to Execute this
SP and have the blanks preserved? I have tried a couple
different approaches but I must be overlooking something
quite simple. I can't see the forest for the trees today.

 

[vongrunt]

Can you CAST() each row as char(500) or something?

 

[Omnillas]

I didn't even think to try to combine the fields. I'll
give it a test.

 

[vongrunt]

Or cast each variable-length column (varchar & stuff) to single width... that is probably better.

 

[Omnillas]

Sad to say that the output from the scheduled job still
truncates the blanks. Going to try a few other suggestions
I found for preserving width.

 

[Omnillas]

I guess for now I must use Q-Analyzer to get the output in
the fixed width format I need. It is a bit annoying to
work around the limitations of the tools.

 

[SQLSister]

If you do this in DTS, you can specify the result as a fixed field length text file.

Questions about posting. See faq183-874