Writing data to ASCII files 1

[macz]

Hi,

Anyone of you knows the syntax codes to write data into ASCII files / create & export files using SQL query statements?

Appreciate if you can refer me to any good websites.

Thanks in advance!

 

[tlbroadbent]

I sugest using DTS, BCP, OSQL or ISQL to export. They are well docuented in SQL Books Online. There are other techniques outlined in Neil Pike's FAQ at

http://www.windows2000faq.com/Articles/Index.cfm?ArticleID=14379.

I found the following posted by Lars Temme on the microsoft.public.sqlserver.programming newsgroup.


****************************

ALTER PROCEDURE prcQueryToFile
( @db sysname,
@query VARCHAR(255),
@file VARCHAR(255)
)

AS

--===================================
-- This procedure writes the result from a query to a file.
-- The procedure takes the following parameters:
-- @db SYSNAME (required) : the database where the query will be executed.
-- @query VARCHAR(255) (required): the query to be executed.
-- @file (VARCHAR(255) (required): the filename to output to
--===================================

SET NOCOUNT ON
--Get version number and verify supported version

DECLARE @ver VARCHAR(7)

SELECT @ver = CASE
WHEN CHARINDEX('6.50', @@VERSION) > 0 THEN '6.50'
WHEN CHARINDEX('7.00', @@VERSION) > 0 THEN '7.00'
WHEN CHARINDEX('8.00', @@VERSION) > 0 THEN '8.00'
ELSE 'Unknown'
END

IF @ver = 'Unknown'
BEGIN
RAISERROR('Unsupported version of SQL Server.',16,1)
RETURN -101
END

--ADD The No Count on parameter to query to remove row affected count

SET @Query = 'SET NOCOUNT ON ' + @Query

EXECUTE ('master..xp_cmdshell ''osql -w8000 -r -s, -h-1 -o' + @file + ' -d'
+ @db + ' -Q"' + @query + '" -E''')


GO

EXECUTE prcQueryToFile 'pubs', 'SELECT * FROM sysobjects',
'G:\lars\test.txt'
Terry

http://members.home.net/tlbroadbent/prog.htm

_____________________________________
Man's mind stretched to a new idea never goes back to its original dimensions. - Oliver Wendell Holmes

 

[macz]

Thanks for your post tlbroadbent,
It's helpful.