Hi - Is there any way to save the results of a query to a text file.. SELECT * FROM Table1. I wanna save the results of the above query to a text file. I know we can do it using BCP, OSQL utility. We can also do it by selecting the Save to File option from Menubar in SSMS. But, my requriement here is different. I have very lengthy query (500 lines) whose results need to be trace in a text file. My idea is to have the query in .sql file whose results will be saved into a text file. Then I will execute that .sql from commandprompt using osql/bcp utility. Pls let me know if there is a way .. Thanks in advance
Check this From BOL: E. Writing variable contents to a file The following example writes the contents of the @var variable to a file named var_out.txt in the current server directory. DECLARE @cmd sysname, @var sysname SET @var = 'Hello world' SET @cmd = 'echo ' + @var + ' > var_out.txt' EXEC master..xp_cmdshell @cmd
Bear in mind if this is executed by a non-sysadmin user then ensure to schedule as a job which uses the proxy account, if you have disabled XP_CMDSHELL for non-sysadmin users. As it seems it is on SQL 2005 by default this is disabled.