Fixed Formatted File

1 Sep 2006 7:11 PM

wnfisba

I have to create a fixed formatted file out of our SQL Server Databas eas an
extract file that will be used to load a third party application.

Do I have to re-cast each selected field so that it becomes fixed formatted???

Are there any tricks within Query Analyzer to make this happen dynamically???

Any help would be GREATLY appreciated.

Thanks in advance.

wnfisba

1 Sep 2006 7:46 PM

Arnie Rowland

The easiest solution is to use either DTS (SQL 2000) or SSIS (SQL 2005).

Both can create the fixed format output files with ease, and by saving the
'package', you can rerun it whenever needed, or even on schedule.

--
Arnie Rowland, Ph.D.
Westwood Consulting, Inc

Most good judgment comes from experience.
Most experience comes from bad judgment.
- Anonymous

Show quote

"wnfisba" <wnfi***@discussions.microsoft.com> wrote in message
news:3F60BB69-7586-48CE-932A-277BCD38954A@microsoft.com...
>I have to create a fixed formatted file out of our SQL Server Databas eas
>an
> extract file that will be used to load a third party application.
>
> Do I have to re-cast each selected field so that it becomes fixed
> formatted???
>
> Are there any tricks within Query Analyzer to make this happen
> dynamically???
>
> Any help would be GREATLY appreciated.
>
> Thanks in advance.
>
> wnfisba

1 Sep 2006 10:42 PM

Erland Sommarskog

wnfisba (wnfi***@discussions.microsoft.com) writes:

> I have to create a fixed formatted file out of our SQL Server Databas
> eas an extract file that will be used to load a third party application.
>
> Do I have to re-cast each selected field so that it becomes fixed
> formatted???
>
> Are there any tricks within Query Analyzer to make this happen
> dynamically???

Running queries for fixed format is not fun at all. Of course, you
could write a small application that reads the data and generate the
file.

But you can also use BCP with a format file to get a fixed format. And,
of course, as Arnie said DTS/SSIS can also do the job, but I don't know
those.

--
Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx