none
Redirecting query output

    Question


  • Hi

    Is there any way to redirect results to file directly while executing query?  Like in management studio we can redirect result to Text or File.(Option Result to File)

    Regards
    Arun.M

    Wednesday, February 27, 2008 4:08 AM

Answers

  • The simple answer is no, there is no T-SQL command to direct output to a file.

     

    However, you could probably create an unsafe CLR object to do this for you, if you *really* need to do this in a generic SQL command.

     

    The real question to be asked here is "why?"  The reason you are trying to do this will greatly affect the answer.  It seems like this is an application question, rather than a T-SQL question.
    Thursday, February 28, 2008 4:24 AM

All replies

  • Hi,

     

    You can use the xp_cmdshell to do so. Check my blog over here :

     

    http://www.sqlservercurry.com/2007/12/redirect-select-query-output-to-text.html

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    http://www.sqlservercurry.com
    -----

     

    Wednesday, February 27, 2008 4:26 AM
  • You can use bcp to do this.

    Wednesday, February 27, 2008 4:27 AM
  • What he said

     

    Wednesday, February 27, 2008 4:28 AM


  • No Suprotim,

      In xp_cmdshell there is a security issue. I dont want to get in to that. Is there any other way to do it?
                                         


    Regards
    Arun.M









      
    Wednesday, February 27, 2008 5:37 AM
  • Hi,

     

    Then as told by a user earlier, use BCP. The bcp utility can be used to import large numbers of new rows into SQL Server tables or to export data out of tables into data files .

     

     

    Code Snippet

    With Windows auth:

     

    bcp "SELECT * FROM Northwind.dbo.Customers" queryout C:\Customers.txt -c -T

     

     

    With Mixed mode:

     

    bcp "SELECT * FROM Northwind.dbo.Customers" queryout C:\Customers.txt -c -S<servername\instancename> -U<sa> -P<pwd>

     

     

     

    HTH,
    Suprotim Agarwal

    -----
    http://www.dotnetcurry.com
    http://www.sqlservercurry.com
    -----

     

     

    Wednesday, February 27, 2008 6:12 AM

  • Hi

    We have to run bcp utility also in windows command prompt. So again i have to use xp_cmdshell. Am i correct?




    Regards
    Arun.M
    Wednesday, February 27, 2008 7:34 AM
  • Either that, or run a batch file rather than a SQL script.

     

    Wednesday, February 27, 2008 2:27 PM

  • ok. How to run the batch file in query without using xp_cmdshell?
                   Still i have to copy batch file to the server. Any other way...
    Thursday, February 28, 2008 4:17 AM
  • The simple answer is no, there is no T-SQL command to direct output to a file.

     

    However, you could probably create an unsafe CLR object to do this for you, if you *really* need to do this in a generic SQL command.

     

    The real question to be asked here is "why?"  The reason you are trying to do this will greatly affect the answer.  It seems like this is an application question, rather than a T-SQL question.
    Thursday, February 28, 2008 4:24 AM
  • Why? In a Query Analyzer, let us say I need to send the SELECT (may also be in a called sproc) result to a regular user as an ascii comma delimited text file, not doing the ctrl-a and then ctrl-c.

     

    Thursday, March 27, 2008 12:43 PM
  • Correctly said by JackJax....
    In Microsoft SQL Server Management Studio 2005, we have options to have the results in three ways -
    • In a grid, as usual in the result pane
    • In text, in the same result pane
    • Into a file, prompted by a save to window.
    You can select your choice from the QUERY > Results To available in the menu bar of the management studio. this can also be accomplished from the shortcut toolbars.

    If you are thinking of logging error messages into a file, you can have a
    BEGIN TRY

    END TRY
    BEGIN CATCH

    END CATCH

    or else you can use -OSQL from the command prompt....
    Thursday, March 27, 2008 1:24 PM
  •  JackJax2005 wrote:
    Why? In a Query Analyzer, let us say I need to send the SELECT (may also be in a called sproc) result to a regular user as an ascii comma delimited text file, not doing the ctrl-a and then ctrl-c.

     

    "However, you could probably create an unsafe CLR object to do this for you, if you *really* need to do this in a generic SQL command."

     

    CLR Object seem to be the solution here. Appreciate any help with unsafe CLR Object.

    Thursday, March 27, 2008 2:38 PM