BCP Out Command

I'm using the BCP OUT command to extract the contents of a SQL table to a
text file. When doing this is there an parameter to format the data into a
fixed format?
Script being used:
bcp.exe databasename.dbo.table OUT datafile -t
"," -c -CRAW -Sservername -Uuser -Ppassword
ThanksBrent Stevenson (essexbs@.insightbb.com) writes:
> I'm using the BCP OUT command to extract the contents of a SQL table to a
> text file. When doing this is there an parameter to format the data into a
> fixed format?
> Script being used:
> bcp.exe databasename.dbo.table OUT datafile -t
> "," -c -CRAW -Sservername -Uuser -Ppassword
Do you mean a format where each column has a fixed length? You would need to
use a format file for that.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspxsql

BCP blank header line

I am using BCP to export the contents of a view into a text file. Everything is running jsut ifne, except the resulting file has a blank first line. Is there a way to preven this?

BCP statement is as follows:

bcp "select pospay from son_db.dbo.vw_pos_pay order by account desc, code asc" queryout D:\elite\USbank\PositivePay_Current\x340.d150364i. d100.txt -T -c

Thanks in advance for any help!probably the first row is a NULL. is the pospay column nullable? if so you can add a where clause to your query to omit the null.|||You can take help of :-F first_row Specifies the number of the first row to export from a table or import from a data file. This parameter requires a value greater than (>) 0 but less than (<) or equal to (=) the total number rows. In the absence of this parameter, the default is the first row of the file.|||that only matters for importing data, Satya. the OP is exporting.|||Jezemine
Look at the BOL entry I've pasted there: Specifies the number of the first row to export from a table or import from a data file

BCP - Output with Headings

Does anyone know how to output the contents of a table via BCP utility to a file WITH the column headings?
Thanks in advance,
--AllaYes but it is a pain in the butt. Email me @. jfogel_34683@.yahoo.com and I'll send you a sample of how it is done. Basically you create a table with the same column names as the table you want to export and you populate a single row in that table with the column names. Then you do a union or something in your BCP statement to get the headers to be the first row.|||an easier way out is you can create a view, instead of a table, which will give you first row as column names and the other rows data from the table...and then bcp the view...

so for eg for the Region table of northwind database i'll try out something like this

SELECT *
FROM (SELECT 'RegionId' AS Expr1, 'RegionDescription' AS Expr2
UNION
SELECT cast(RegionId AS char(4)), Regiondescription
FROM Region) xyz
ORDER BY Expr1 DESC

and the resultant flat file will contain the following data

RegionId RegionDescription
4 Southern
3 Northern
2 Western
1 Eastern

Also you will have to cast all non compatible data types to char in your resultant table....|||an easier way out is you can create a view, instead of a table, which will give you first row as column names and the other rows data from the table...and then bcp the view...

no need to create a view if you use this technique with the queryout option.|||no need to create a view if you use this technique with the queryout option.Does that include the headings?

Was this fixed?:
http://support.microsoft.com/kb/309555|||that's a 4 year old bug, I assume it's fixed in 2005.

I have never had any problems with queryout in 2005, and I have used this technique to export a csv with headigns with over 1b rows. big one!

BCP Formatting Output

Hi,

I wrote the below code and procedure that exports two tables contents into 2 separate Excel files. Is there a way to export contents of two tables via BCP utility into 1 Excel file but 2 different Worksheets of this file?

DECLARE @.FileName varchar(50),
@.FileName1 varchar(50),
@.bcpCommand varchar(2000)
SET @.FileName = 'E:\GPPD_db_stats.XLS'
SET @.FileName1 = 'E:\GPPD_file_stats.XLS'
print @.FileName
SET @.bcpCommand = 'bcp "master.dbo.spdbdesc" OUT ' + @.FileName + ' -Samex-srv-gppdb -T -c'
print @.bcpCommand
EXEC master..xp_cmdshell @.bcpCommand

SET @.bcpCommand = 'bcp "master.dbo.spfiledesc" OUT ' + @.FileName1 + ' -Samex-srv-gppdb -T -c'
print @.bcpCommand
EXEC master..xp_cmdshell @.bcpCommand
exec master.dbo.xp_stopmail
set @.bcpCommand = ' ' + @.FileName + '; ' + @.FileName1 + ''
DECLARE @.body VARCHAR(1024)
SET @.body = 'Please find enclosed files with the database status reports as of '+
CONVERT(VARCHAR, GETDATE()) + '. Please DO NOT respond to this email or the ones coming in the future ' +
'with data files as this email address is not monitored for incoming emails. However, if you have any ' +
'questions/concerns please contact ...'


EXEC master..xp_sendmail
@.recipients='alla.levit@.amex.com',
@.message = @.body,
@.subject = 'Database Weekly Statistics Report',
@.attachments = @.bcpCommand

Thanks in advance!
-AllaGood luck. I've never been able to pull this off.