Output TSQL result to textfile in script

Question

Is there a way to directly write result returned from TSQL / stored procedure to a text file (not using CTRL + T => Result to Text). As this TSQL will be dynamic in one of my service routine. Whenever I call this service routine it generates SQL Statement => executes and here I want to direct it to text file by passing the filepath as parameter.

How can this be done ?

Thanks

Answer 1

Something like this would work:

declare @cmd varchar(500)
select @cmd = 'osql -U -P -S -Q"select * from myTable" -o"c:\output.txt" -w500'
exec master..xp_cmdshell @cmd

You could also do it by creating a C# based stored procedure and execute it via the CLR.