September 22, 2016 at 7:12 am
Hi,
I realize this is potentially outside the scope of this forum, but I'm interested in what might be considered the ideal approach for running a stored procedure, dumping the results into an Excel spreadsheet, and making this process automated/scheduled.
A few ideas I plan to look into:
- VB.NET application running as a scheduled task
- SSIS package
Are there any other SQL tools (or non-SQL tools for that matter) that are generally recommended in this situations?
Much appreciated!
Additional note: I'd be particularly interested in any SQL tool that allows for some manipulation of the format of the spreadsheet, for example (1) checking column names already in an Excel spreadsheet to know where to dump the column data into, (2) size, color and style formatting of text and cells, (3) where the data gets dumped (say, starting the first column in C4 as opposed to A1), and (4) being able to run multiple stored procedures, one for each tab of the spreadsheet.
September 22, 2016 at 7:38 am
OK, this is a really quick and dirty way that I use to generate CSV files, which Excel will happily open.
They key to this one is that the parameter @NameOfTableToExport has to be a global temp table. So something like ##TableOut will work. The Export path has to be someplace that the SQL Server can write to. And of course, you'll want to make sure that the path to BCP is correct. I had to hard code it in my environment because we have a BCP for sybase on the same system and it shows up in the PATH environment variable first. Don't ask...
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author:SBrauksieck
-- Create date: 2015-12-30
-- Description:Exports table to specified flat file
-- =============================================
CREATE PROCEDURE [dbo].[ExportToDatafile]
-- Add the parameters for the stored procedure here
@NameOfTableToExport VARCHAR(200),
@ExportPath VARCHAR(1000),
@OrderByCols VARCHAR(1000) = NULL
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
EXEC(@AlterStmt)
SET @AlterStmt = ''
SELECT @AlterStmt = @AlterStmt + 'UPDATE ' + @NameOfTableToExport + ' SET [' + b.column_name + '] = REPLACE([' + 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
EXEC(@AlterStmt)
IF @OrderByCols IS NOT NULL BEGIN
SET @AlterStmt = 'CREATE CLUSTERED INDEX IX_PK ON ' + @NameOfTableToExport + ' (' + @OrderByCols + ')'
EXEC(@AlterStmt)
END
DECLARE @SQL VARCHAR(max) = ''
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
SET @SQL =
'DECLARE @SQLOUT VARCHAR(8000) ' +
'SET @SQLOUT = ''E:\Progra~2\Micros~1\100\Tools\Binn\bcp "SELECT ' + LEFT(@ColumnHeaders,LEN(@ColumnHeaders)-1) +
' UNION ALL SELECT * FROM ' + @NameOfTableToExport +
'" queryout "' + @exportpath + '" -c -t, -T -S' + @@SERVERNAME + '''' +
' PRINT @SQLOUT; EXEC master..xp_cmdshell @SQLOUT'
EXEC(@SQL)
END
September 22, 2016 at 8:27 am
SSIS is probably what most people use. You could absolutely do all this using Powershell though.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt
Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning
September 23, 2016 at 1:16 pm
Thanks for the suggestions! Sounds like SSIS might be the closest thing there is to some sort of industry standard for this type of automation.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply