Writing to a Fixed-Length ASCII File in SQL

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

General SQL Server Forums

New to SQL Server Programming

Writing to a Fixed-Length ASCII File in SQL

Author

Topic

BobRoberts
Posting Yak Master

109 Posts

Posted - 2009-06-11 : 11:41:47

Is there a simple, direct way to write records to an ASCII text file from SQL? The following example cursor has the following data (why won't the forum window line these up right?):

@CSocialSecurityNo @CLastName @CQ1Etotal
123883480 Williams 3841.44
710420255 Smith 48230.00

I want to put two records into a file called C:\ETEST1.TXT, with a carriage return and/or linefeed at the end of each, so it looks like (pretend the columns are lined up vertically, even though the forum window doesn't seem able to do that):

123883480Williams 000000384144
710420255Smith 000004823000

-------- EXAMPLE CURSOR ------

DECLARE TestCursor CURSOR
FOR
SELECT SocialSecurityNo, LastName, SUM(Amount1+Amount2+Amount3) as Q1Etotal
FROM [TESTDB].[dbo].[tblPaEmpTest]
GROUP BY SocialSecurityNo, LastName;

DECLARE @CSocialSecurityNo nvarchar(40),
@CLastName varchar(20),
@CQ1Etotal decimal

OPEN TestCursor
FETCH NEXT FROM TestCursor
INTO @CSocialSecurityNo,
@CLastName,
@CQ1Etotal

WHILE @@Fetch_Status = 0

BEGIN

-- I want to put out the cursor row in an ASCII text file here.

FETCH NEXT FROM TestCursor
INTO @CSocialSecurityNo,
@CLastName,
@CQ1Etotal

END

CLOSE TestCursor
DEALLOCATE TestCursor

RETURN

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2009-06-11 : 11:47:26

You can use the BCP utility with the queryOut flag. You can specify the delimiters and row endings. For the fixed width you can pad with spaces in your SELECT statements.

No need for a cursor at all. If you must do this Server side then you can still use BCP with a xp_commandShell call.

Check out BCP in books online.

http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources