After most DOS commands you can type >> followed by a filename, the results of the command executing will be appended to the file and nothing will be returned in the DOS box. You can also use the ECHO command to print your own text to the screen, again, if you use >> it is not printed to the screen but appended to the file.
SQL Server has an extended stored procedure called xp_cmdshell. This sproc will execute the text that you pass it as if it were in a DOS box, any results from the command executing will be returned as a recordset unless you supply the no_output parameter, in which case nothing is returned. Therefore to add the result of the @str variable to a text file called s.txt all we need to do is:
Code:
declare @str varchar(50)
select @str = 'subodh'
print @str
select @str = 'ECHO ' + @str + ' >>C:\s.txt'
-- @str will become "ECHO subodh >>C:\s.txt"
EXEC xp_cmdshell @str, no_output
I am sure you can understand the security issues involved in using this sproc, so be very careful in how you use it and what access permissions you give your SQL Server instance (because the sproc inherits it's permissions from the account used to run SQL Server and not the user calling the sproc). You may also need to check that you or someone else has not disabled this sproc for security reasons.
Regards
Owain Williams
Print value of a variable to text file
