SQL Server Performance

saving the query results to text file

Discussion in 'SQL Server 2005 General Developer Questions' started by erajendar, Dec 4, 2007.

  1. erajendar New Member

    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
  2. ranjitjain New Member

    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
  3. satya Moderator

    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.

Share This Page