November 25, 2008 at 5:59 am
Hi all
How do I create a automatic job that write a SQL query to a comma-separated text file?
Regards
ERIK
November 25, 2008 at 6:03 am
Create an SSIS package to run the query and write the file and use the SQL Agent to schedule the package.
November 25, 2008 at 6:24 am
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
November 25, 2008 at 1:42 pm
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.
November 25, 2008 at 2:15 pm
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
November 25, 2008 at 7:41 pm
You can use bcp also.
EXEC xp_cmdshell 'bcp "SELECT * FROM sysfiles" queryout "C:\bcptest.txt" -T -c -t,'
November 26, 2008 at 12:00 pm
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.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply