none
Export, or write, dynamic SQL to flat file

    질문

  • I am running the SQL below and it works fine, but I'm wondering if there is a way to get the results saved to a text file, rather than displaying the results in a grid view.  Is this possible?  I'm using SQL Server 2008.

    Declare @STARTDATE Varchar(30)
    Declare @ENDDATE Varchar(30)
    DECLARE @SQLQUERY Nvarchar(4000)
    
    SET @STARTDATE = '2017-04-01'
    SET @ENDDATE = '2018-04-30'
    
    SELECT 'select top 1 '+(select [Source_Column_Name]) +
    +' from data_mart_us.dbo.' + (select [Source_Table] )  +' where asofdate between '''+ CONVERT(VARCHAR(10),@STARTDATE, 120)+  ''' and ''' + CONVERT(VARCHAR(10),@ENDDATE, 120) + ''''
      FROM [RECONCILIATION].[dbo].CheckList
      where database_name like 'mart_us'
    GO


    MY BOOK

    2018년 5월 17일 목요일 오후 7:14

모든 응답

  • You could chose "Results to file" on query window , to change for a specific query window

    OR 

    Go to Tools> Options > Query Results > select "Results to file"  in drop down and click ok.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.


    • 편집됨 Sarat (SS) 2018년 5월 17일 목요일 오후 7:22
    2018년 5월 17일 목요일 오후 7:22
  • I tried that.  It still prints to the grid view.  I'm guessing it doesn't work because of the dynamic SQL.

    MY BOOK

    2018년 5월 17일 목요일 오후 7:27
  • I did that and I could see SSMS trying to open a file location to save details.

    Which one the above 2 options did you try?

    1st approach works for only the query window for which the change done.

    2nd approach, works for any query window opened after the change. If there are open windows, then they would still follow previous option.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 5월 17일 목요일 오후 7:31
  • You can use bcp to export the result to a flat file. Create a stored procedure to include your dynamic query. 

    Give it a try.

    2018년 5월 17일 목요일 오후 7:43
    중재자
  • Yes, yes, I think it has to be done via bcp, or PowerShell.  I don't think you can use the interactive query window to do this kind of thing.

    MY BOOK

    2018년 5월 17일 목요일 오후 7:47
  • OR you try import/export wizard and chose flat file as destination. At the end of flow you could it as SSIS package if needed.

    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    2018년 5월 17일 목요일 오후 7:58
  • For dynamic sql result, you may have have issues when you use it as datasource in SSIS.
    2018년 5월 17일 목요일 오후 8:07
    중재자
  • As it turns out, you can click 'Query' and 'SQLCMD Mode'.  Then enter one line at the top of the query: ':OUT C:\your_path_here\test.txt'.  That works.

    MY BOOK

    2018년 5월 17일 목요일 오후 8:57
  • Hi,

    I'm glad to hear that you have resolved your issue. Please kindly mark your solution as answer. It'll benefit others with relevant issue. :)

    Thanks,
    Xi Jin.


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    2018년 5월 18일 금요일 오전 6:47