Writing Headers and Footer into a File

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Development (2000)

Writing Headers and Footer into a File

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-03-11 : 00:42:01

Rajesh writes "I have written a stored procedure which uses bcp and xp_cmdshell utilities. My process involves pulling in data from a table and writing into dat file.
My problem is I want to add a header and Footer to the same text file along with the data from the table.
The header should be the description of the file and footer the no of records pulled in.
Please help me out with the syntax.

CREATE PROCEDURE testraj AS

DECLARE @FileName varchar(200),
@bcpCommand varchar(3000)
begin

SET @FileName = 'D:\testraj.dat'
SET @bcpCommand = 'bcp "select * from TBAPP002_APPLCTN " queryout "'
SET @bcpCommand = @bcpCommand + @FileName + '" -c -t\t -Usa -P -Ssql12'
EXEC master..xp_cmdshell @bcpCommand

end"

dsdeming

479 Posts

Posted - 2002-03-11 : 08:02:55

What I've done in cases like this is to create a temp table with a single varchar column wide enough to hold the desired output. Then insert into the temp table the header, the data from the source table, and the footer. Finally, bcp the temp table.

HTH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources