Post reply

How to store output of SQL Server Stored Procedure in a .txt file

  • October 21, 2018 at 9:59 am

    #416218

    I have below SP I am printing the value of the variable in S.S.M.S. Instead, I want to store this same result in a .txt file.

    NOTE: I don't want to do it using S.S.M.S options of right clicking on the result and then Saving the result As. I want it to be done using any SQL code/built-in function directly in SP itself.

    CREATE PROCEDURE [dbo].[usp_printresulttofile]  AS  BEGIN  declare @var nvarchar(max) = ''  SET @var = 'print this data in txt file'  Print 'Data is : ' + @var  /* sql query here to store result of Print statement in text file */  END EXEC [dbo].[usp_printresulttofile]

  • October 21, 2018 at 1:14 pm

    #2010521

    vikasjagadale8 - Sunday, October 21, 2018 9:59 AM

    I have below SP I am printing the value of the variable in S.S.M.S. Instead, I want to store this same result in a .txt file.

    NOTE: I don't want to do it using S.S.M.S options of right clicking on the result and then Saving the result As. I want it to be done using any SQL code/built-in function directly in SP itself.

    CREATE PROCEDURE [dbo].[usp_printresulttofile]  AS  BEGIN  declare @var nvarchar(max) = ''  SET @var = 'print this data in txt file'  Print 'Data is : ' + @var  /* sql query here to store result of Print statement in text file */  END EXEC [dbo].[usp_printresulttofile]

    You have several options.  Here are 6 different ones (there are more) that I've used in the past.  Option 3 is my favorite.
    1.  Use SSIS (not my first choice)
    2.  Use PowerShell (not my first choice either)
    3.  Use xp_CmdShell to execute BCP or SQLCmd  (and, no... xp_CmdShell is NOT a security risk if you use it correctly)
    4.  Use a batch file from the command prompt (or whatever) to call BCP or SQLCmd.
    5.  Create a text based linked server on the fly and drop it when done.
    6.  Use PowerShell (actually, my last choice)

    --Jeff Moden

    RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
    First step towards the paradigm shift of writing Set Based code:
    ________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

    Change is inevitable... Change for the better is not.

    Helpful Links:
    How to post code problems
    How to Post Performance Problems
    Create a Tally Function (fnTally)

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply