Save results from Stored procedure to a CSV file.

  • I have a SqlAgentJob calling a stored proc. that loops through a list of locations and passes the location value and other parameters to a stored procedure.  I would like to save each of the results to a CSV on the server. Because i have 50+ locations my current method of a separate sqlAgenjob using a sqlcmd to save is not possible a new locations are added or removed. Is there a way to save a sql query result to a csv. I know that you can save the results to a csv from the SSMS window, but i need to run at a scheduled time. and the 50 + reports takes hours to run.

  •  

     

    • This reply was modified 2 months, 2 weeks ago by  lee.hopkins.
  • Since you are using a SQL Agent Job for this, in the settings for the SQL Agent Job STEP, you can tell it to output to a file.  You should be able to set that to append after each job step or not (depending on the requirements).  then any data set output from the command would be written out to a text file.

    I would do some testing with this to make sure it gives things in the format you want.  If it doesn't you may need to get fancy and do things in SSIS instead.

  • You might be able to setup a Powershell script to do this - and setup the agent job to execute that script.  A simple driver table to identify the parameters for each loop should work.

    In the script - use Invoke-SqlCmd to get the data from your driver table, loop over the results and call your stored procedure with the appropriate parameters and output to a variable - and then use Export-Csv to export the data from the procedure to a file.

    You can dynamically create the file based on the parameters or configuration in your driver table.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • A bunch of years ago, I created a terrible stored procedure to allow me to export data from the SQL Server. This is for my own internal use, so it's not very polished, but it's essentially what you're trying to do.

    This requires xp_cmdshell to run.  The SQL Server Service account needs to have permissions to write to the directory you're trying to export to.

    The setup is that you insert data into a global temp table, and then call the stored procedure. The procedure alters the table to VARCHAR datatypes so that it can add the column headers to the output file.

    If you want the data to come out sorted, it tries to cheat by putting a clustered index on your sort columns.

    The delimiter field is optional, defaulting to comma.

    Sample would be something like

    select * into ##Blah from sys.databases

    exec ExportToDatafileDelim '##Blah', 'E:\OutputDirectory\Databases.csv'

     

    CREATE PROCEDURE [dbo].[ExportToDatafileDelim] 
    -- Add the parameters for the stored procedure here
    @NameOfTableToExport VARCHAR(200),
    @ExportPath VARCHAR(1000),
    @OrderByCols VARCHAR(1000) = NULL,
    @Delimiter char(1) = ','
    AS
    BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;


    DECLARE @AlterStmt VARCHAR(MAX) = ''

    SELECT @AlterStmt = @AlterStmt + 'ALTER TABLE ' + @NameOfTableToExport + ' ALTER COLUMN [' + b.column_name + '] VARCHAR(500);'
    FROM TEMPDB.INFORMATION_SCHEMA.TABLES a
    INNER JOIN TEMPDB.INFORMATION_SCHEMA.Columns b ON a.table_name = b.table_name
    WHERE a.table_name = @NameOfTableToExport
    and b.DATA_TYPE <> 'varchar'


    if @AlterStmt <> ''
    EXEC(@AlterStmt)

    SET @AlterStmt = ''

    SELECT @AlterStmt = @AlterStmt + 'UPDATE ' + @NameOfTableToExport + ' SET [' + b.column_name + '] = REPLACE([' + b.column_name + '],''' + @Delimiter + ''','''');'
    FROM TEMPDB.INFORMATION_SCHEMA.TABLES a
    INNER JOIN TEMPDB.INFORMATION_SCHEMA.Columns b ON a.table_name = b.table_name
    WHERE a.table_name = @NameOfTableToExport

    EXEC(@AlterStmt)

    IF @OrderByCols IS NOT NULL BEGIN
    SET @AlterStmt = 'CREATE CLUSTERED INDEX IX_PK ON ' + @NameOfTableToExport + ' (' + @OrderByCols + ')'
    EXEC(@AlterStmt)
    END


    DECLARE @SQL VARCHAR(8000) = ''

    DECLARE @ColumnHeaders VARCHAR(max) = ''

    SELECT @ColumnHeaders = @ColumnHeaders + '''''' + b.column_name + ''''' [' + b.column_name + '],'
    FROM TEMPDB.INFORMATION_SCHEMA.TABLES a
    INNER JOIN TEMPDB.INFORMATION_SCHEMA.Columns b ON a.table_name = b.table_name
    WHERE a.table_name = @NameOfTableToExport

    declare @path VARCHAR(1000)
    SET @path=SUBSTRING(@ExportPath,1,len(@ExportPath) - CHARINDEX('\',REVERSE(@ExportPath))+1)

    set @sql = 'DIR "' + @path + '"'
    print @sql

    CREATE TABLE #DirInfo(line varchar(255))

    INSERT INTO #DirInfo(line)
    EXEC master..xp_cmdshell @SQL

    if EXISTS(select 1 from #DirInfo where line = 'The system cannot find the file specified.') BEGIN
    print 'Creating dir ' + @path
    exec master..xp_create_subdir @path
    END

    drop table #DirInfo

    SET @SQL =
    'DECLARE @SQLOUT VARCHAR(8000) ' +
    'SET @SQLOUT = ''bcp "SELECT ' + LEFT(@ColumnHeaders,LEN(@ColumnHeaders)-1) +
    ' UNION ALL SELECT * FROM ' + @NameOfTableToExport +
    '" queryout "' + @exportpath + '" -c -t"' + @Delimiter +'" -T -S' + @@SERVERNAME + '''' +
    ' PRINT @SQLOUT; EXEC master..xp_cmdshell @SQLOUT'


    EXEC(@SQL)


    END



    GO
  • This might work, Tks

     

  • @stevenb,

    I like it... a LOT!  Nicely done!

    --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".
    "Dear Lord... I'm a DBA so please give me patience because, if you give me strength, I'm going to need bail money too!"

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

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

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