It seems there's a bit of confusion here...
First, when you create the text file, do you want it done automatically as part of a stored procedure or do you want to do it manually from SSMS?
Another name for "column aligned" text files is "Fixed Width Formatting" where every line in the ouput file has the same number of fields, characters per field, etc, and every line ends up having the same number of characters. This can be achieved in a couple of ways...
1. Concatenate and pad your information into a wide column in an "output" table and BCP the result.
2. Create a BCP format file (acts as record layout documentation, as well) and BCP the data out (this is probably the fastest off all methods, performance wise).
3. Write a DTS flow to do it for you (personnally, I never use DTS... too slow for me).
As you can tell, I recommend method #2. Just keep in mind that if you want to run BCP from a proc, you will need the user running the proc be logged in as "SA".
You can use OPENROWSET and a couple of other methods, but, if memory serves me correctly, the file must already exist. That makes it kinds hard to make a new file, huh?
Also, you'll run into some folks who do it with the sp_OA* procs... same problem as BCP... they require you to login with "SA" privs to run.
Alternatively, you could schedule a Windows Batch Job that called BCP...
Take a look at "BCP FORMAT FILE" in Books Online and let us know if you have any questions.
is pronounced ree-bar and is a Modenism for R
First step towards the paradigm shift of writing Set Based code: Stop thinking about what you want to do to a row... think, instead, of what you want to do to a column.
If you think its expensive to hire a professional to do the job, wait until you hire an amateur. -- Red Adair
How to post code problemsHow to post performance problemsForum FAQs