Spool operation in SQL server

SQL Server doesn't have a SPOOL command like Oracle does for writing to files,
But there are other ways of doing what we want.

1. For reading, use xp_cmdshell and the ECHO command. Use the > or >> redirection symbols to either create or append to a file.
xp_cmdshell "@ECHO test message >> C:\file.fil"
2. Put the information you want into a table (note this can't be an ordinary temporary table, but it can be a global temporary table) and then bcp it out to a file via xp_cmdshell.
xp_cmdshell "bcp .. out c:\file.fil -Usa -P -c"
3. Run the select through ISQL via xp_cmdshell and use the -o parameter to output the results to a file. This example uses the -E parameter to avoild hard-coding a userid.

declare @str varchar(255)
select @str = 'isql -Q"select * from " -E -oc:\file.fil'
exec master..xp_cmdshell @str