Question:
I would like to create a stored procedure that runs a query and outputs the results to a text file and allows me to add extra delimeters and static field info. Any help you can give would be greatly appreciated.
Answer:
SQL Server has no native command for outputting query results to a file. You can use the extended stored procedure xp_cmdshell and call isql (command-line SQL) with your query and output the results to a file. Any delimiters would need to be part of the SELECT string:
DECLARE @isqlString varchar(255)SELECT @isqlString = 'isql -Q "SELECT DateCol FROM NorthwindTest.dbo.Test" -E -o C:Results.txt'EXEC master..xp_cmdshell @isqlString