Ah... apologies, I've managed to completely omit the fact that sp_send_dbmail is stored procedure...
I'm aware that virtually all other tasks can be achieved by this, but there are further requirements on the format of the file:
- all data values within CSV file need to be enclosed with double quotes, even empty cells
- column headers need to be exported as well, also enclosed in double quotes.
For those reasons I've created function to generate CSV file. It's dynamic SQL, reads column names, puts those in temporary column, then reads data, adds to the same table, then I'm using BCP to export this to CSV file.
I'm new to SQL environment, I was (am) programmer, so my approach might not be most effective one - but I know it works.
I know that functions have different purpose in SQL, and I'm using those as well, but being programmer pushes me to create functions with less parameters required, easier to remember and use (in my world at least). having my own function to send file by email saves me remembering profiles, correct syntax of built in stored procedure, etc... I know, hardly any benefit, just the way I work.
Generate_CSV_File is this:
ALTER FUNCTION [dbo].[Generate_CSV_File] (@schema NVARCHAR(MAX), @table NVARCHAR(MAX), @filename NVARCHAR(MAX)) returns int
as
declare @st nvarchar(max)
declare @Column nvarchar(max)
declare @CommandLine nvarchar(254)
exec dbo.DeleteTable reporting, _temporary_holder_for_csv_file
declare column_cur cursor for select [column_name] from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @schema and TABLE_NAME = @table
open column_cur
set @st = 'select '
fetch next from column_cur into @Column
while @@FETCH_STATUS = 0
begin
set @Column = '(select ''' + dbo.wrapstring(@Column) + ''') as [' + @Column + '],' + char(13) + char(10)
set @st = @st + @Column
fetch next from column_cur into @Column
end
deallocate column_cur
set @st = left(@st, len(@st)-3) + char(10) + char(13) + 'into [reporting].[_temporary_holder_for_csv_file]'
declare column_cur cursor for select [column_name] from INFORMATION_SCHEMA.COLUMNS where TABLE_SCHEMA = @schema and TABLE_NAME = @table
open column_cur
set @st = @st + 'union all select '
fetch next from column_cur into @Column
while @@FETCH_STATUS = 0
begin
set @Column = 'dbo.wrapstring(cast([' + @Column + '] as nvarchar(max))),' + char(10) + char(13)
set @st = @st + @Column
fetch next from column_cur into @Column
end
set @st = left(@st, len(@st)-3) + char(10) + char(13)
set @st = @st + 'from [' + @schema + '].[' + @table + ']' + char(13) + char(10)
exec sp_executesql @st
set @CommandLine = 'BCP [xxxxxxxxxx].[reporting].[_temporary_holder_for_csv_file] out ' + @filename + ' -c -t, -T -S'
EXEC xp_cmdshell @CommandLine
deallocate column_cur
exec dbo.DeleteTable reporting, _temporary_holder_for_csv
return 0
dbo.WrapString is this:
ALTER FUNCTION [dbo].[WrapString] (@iString NVARCHAR(MAX)) returns NVARCHAR(MAX)
AS
BEGIN
return '"' + dbo.FixString(@iString) + '"'
END
dbo.DeleteTable is this:
ALTER FUNCTION [dbo].[DeleteTable] (@schema nvarchar(max), @table nvarchar(max)) returns int
as
begin
declare @cmd nvarchar(max)
set @cmd = 'if exists (select * from information_schema.tables where table_schema = ''' + @schema + ''' and table_name = ''' + @table + ''') drop table ' + @schema + '.' + @table
exec sp_executesql @cmd
return 0
end
I know, now the perfect way, but it works. As I said earlier - I'm new to SQL environment. If you can think of any way of improving this solution - I'm open fr suggestions.
Thanks for all help!