Export data as fixed width file from SQL Server 2005

Question

I thought it is a very simple task to export data in a view from SQL Server 2005 to a fixed width text file. But the wizard is a pain. The format is not correct. Does anybody know how to deal with it? or any better way to do that?

Thanks!

Answer 1

If the format from the wizard doesn't suit your needs, you will need to develop your own SSIS package. Are you sure you really need a fixed width file? A delimited file would probably be easier to get right as they are much more common.

Answer 2

Use bcp with queryout option http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

bcp "SELECT * FROM AdventureWorks.Person.Contact" queryout Contacts.txt -c -T

Use a format file if you want fixed-width output

http://weblogs.sqlteam.com/brettk/archive/2006/07/06/10504.aspx

I just tried exporting in AdventureWorks, Fixed Width gave me a lot of issues too (compared to column delimiters) I had to ignore the GUID columns, not include column names in the first row, etc.. finally exported Sales.Customer table

Without BCP, you may have to look into SSIS or SQLCMD

Answer 3

One option is to use OpenDataSource to write the rows out to a text file. This requires that the text file already exists, but is relatively easy.

This article (for disclosure, I wrote it, and it focuses on reading from, but touches on writing too) explains the basics of reading and writing from text files with OpenDataSource. : http://www.sqlservercentral.com/articles/OpenDataSource/61552/

Answer 4

I have found SQL2005/SSIS to be anything but simple to do seemingly simple tasks. Things always take me 5x longer than they should. I know I'm not alone on this.

BCP should work.

Don't know about sqlcmd - seems like it doesn't do fixed width, unless you did the padding in your sql statement - which would also be a pain.

usage: Sqlcmd            [-U login id]          [-P password]
  [-S server]            [-H hostname]          [-E trusted connection]
  [-d use database name] [-l login timeout]     [-t query timeout]
  [-h headers]           [-s colseparator]      [-w screen width]
  [-a packetsize]        [-e echo input]        [-I Enable Quoted Identifiers]
  [-c cmdend]            [-L[c] list servers[clean output]]
  [-q "cmdline query"]   [-Q "cmdline query" and exit]
  [-m errorlevel]        [-V severitylevel]     [-W remove trailing spaces]
  [-u unicode output]    [-r[0|1] msgs to stderr]
  [-i inputfile]         [-o outputfile]        [-z new password]
  [-f <codepage> | i:<codepage>[,o:<codepage>]] [-Z new password and exit]
  [-k[1|2] remove[replace] control characters]
  [-y variable length type display width]
  [-Y fixed length type display width]
  [-p[1] print statistics[colon format]]
  [-R use client regional setting]
  [-b On error batch abort]
  [-v var = "value"...]  [-A dedicated admin connection]
  [-X[1] disable commands, startup script, enviroment variables [and exit]]
  [-x disable variable substitution]
  [-? show syntax summary]