query to a comma-separated text file

Question

Post reply

query to a comma-separated text file

Erik Hansson

Old Hand

Points: 395
More actions
November 25, 2008 at 5:59 am

#128849

Hi all
How do I create a automatic job that write a SQL query to a comma-separated text file?
Regards
ERIK

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Michael Earl-395764 SSC Guru Points: 53873 More actions · Answer 1

Create an SSIS package to run the query and write the file and use the SQL Agent to schedule the package.

Erik Hansson Old Hand Points: 395 More actions · Answer 2

Isn´t it possible to create a comma-separated file from a query. I can create a file with this query

DECLARE @cmd VARCHAR(2048)

SET @cmd = 'OSQL -Stest\SQLSERVER2005 -dtest -E'

+ ' -Q"select name as sp_name, modify_date, @@servername as servername, db_name() as db_name from sys.objects where type = ''P'' AND DATEDIFF(D,modify_date, GETDATE()) < 7 order by modify_date desc"'

+ ' -oc:\authors.doc'

EXEC master..xp_cmdshell @cmd, NO_OUTPUT

The file is created but the file is not comma-separated.

How can the file be comma-separated?

/ERIK

Jared Hunt Mr or Mrs. 500 Points: 507 More actions · Answer 3

The OSQL command will be depricated in a future release. You should use the sqlcmd utility, which has the -o option allowing output to a file. As stated before, SSIS is another option.

MANU-J. SSC-Dedicated Points: 31126 More actions · Answer 4

Try executing the below code from management studio:

!!Sqlcmd -S Servername -E -d northwind -Q "Select * from employees" -o C:\output_file.csv -s ,

-s switch specifies the column-separator character. The default is a blank space.

HTH,

MJ

Ken Simmons SSCertifiable Points: 7822 More actions · Answer 5

You can use bcp also.

EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'

http://msdn.microsoft.com/en-us/library/ms162802.aspx

brainy SSCrazy Points: 2445 More actions · Answer 6

Use bcp command to export to csv file. In bcp you can specify the delimeter which you want to use. It can be a comma(,), semicolon(;), pipe(|) anything. Also you specify the line termicator i.e for each record.

You can set some other formats also.

query to a comma-separated text file

Cookies on SQLServerCentral