Is it possible to create a SQL JOB that will query a db and send the result out as a excel attachment.
If so what syntax should i use? I AM ABLE TO CREATE A JOB THAT SENDS THE ATTACHMENT VIA CSV but cant do the same for excel . I am resorting to create a SSIS JOB, but doing it in SQL SERVER WOULD BE MORE IDEAL
my current sql
@query='
SET NOCOUNT ON;
select ''sep=;''
select ''Col1'',''Col2'',''Col3'',''Col3''
select CONVERT(NVARCHAR,Col1),ISNULL(Col2, ''''),Col4
FROM ...
SET NOCOUNT OFF;
',
--Additional settings
@query_attachment_filename = '*.xlsx',
@query_result_separator = ';',
@attach_query_result_as_file = 1,
@query_result_no_padding= 1,
@exclude_query_output =1,
@append_query_error = 0,
@query_result_header =0;
does not work PLEASE ADVISE
You should look at a SSIS package to do that. Out of the box, SQL Server provides no easy ways to generate Excel files.
Additionally, many orgs (like mine) won't allow Office products to be installed on servers running SQL Server.
The only way to do it directly from SQL Server would be to build the Excel XML file manually. Since this is a lot of work that isn't worth the time and effort - i don't think anyone has ever done anything like that...you would have to build it out yourself.
That really leaves a couple of options - as stated above SSIS is one option. I prefer using SSRS myself - as it is much easier to create a report and then export that report as an Excel spreadsheet and you get the benefit of structuring the Excel file to the point of creating multiple named sheets if you want.
You can also schedule the report to be executed and delivered using email quite easily.
Using SSIS with Office can be a problem - especially if you are trying to develop on your x64 machine with x64 Office installed which doesn't work. You have to install the x86 version of Office so that SSIS can access the Excel drivers.
i just went the ssis route to create the excel attachment
THANKS ALL