Link to home
Start Free TrialLog in
Avatar of Pumpernickel
PumpernickelFlag for United States of America

asked on

SQL to Excel Sending

I have this code setup as a Job in MSSQL and I'm trying to see how I can make this write to excel and email it?

USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'Reports',
      @recipients = 'user@domain.org',
      @subject = 'Subject',
      @body = 'Body Text Here
	  ',
      @execute_query_database = 'app_company',
      @query = 'SELECT SR_Service_RecID,Updated_By,Summary,Last_Update FROM SR_Service  WHERE SR_Status_RecID = 561';

Open in new window

Avatar of lcohan
lcohan
Flag of Canada image

You can do it easily as CSV if that is sufficient:

USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'Reports',
      @recipients = 'user@domain.org',
      @subject = 'Subject',
      @body = 'Body Text Here',
      @execute_query_database = 'app_company',
      @query = 'SELECT SR_Service_RecID,Updated_By,Summary,Last_Update FROM SR_Service  WHERE SR_Status_RecID = 561',
      @attach_query_result_as_file= 1,
      @query_attachment_filename='AttachmentFile.csv',
      @query_result_no_padding = 1,
      @query_result_separator='      '
Another alternative is to create an SSIS package, deploy it to your server, and setup a SQL Agent job to execute the package.

This is certainly more involved and unless you plan on making this a regular task I wouldn't bother and would go with Icohan's solution.
Avatar of Pumpernickel

ASKER

lcohan,
Would I just put this into the jobs in sql how the other one is now?
Assuming the SQL Agent startup account that will run the code has sufficient rights to do all is needed - yes.
Icohan,
Your script worked, but its not by column.  Its like the email smushed into a csv.  How can I have it so each column from the query is in a column?
most likely copy/paste tab messed up and please try with @query_result_separator = ',' as separator like:

USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'Reports',
      @recipients = 'user@domain.org',
      @subject = 'Subject',
      @body = 'Body Text Here',
      @execute_query_database = 'app_company',
      @query = 'SELECT SR_Service_RecID,Updated_By,Summary,Last_Update FROM SR_Service  WHERE SR_Status_RecID = 561',
      @attach_query_result_as_file= 1,
      @query_attachment_filename='AttachmentFile.csv',
      @query_result_no_padding = 1,
@query_result_separator = ','


You can also format your query like

@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 = '*.csv',
@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;
So this should work?

USE msdb
    EXEC sp_send_dbmail
      @profile_name = 'Reports',
      @recipients = 'user@domain.org',
      @subject = 'Subject',
      @body = 'Body Text Here',
      @execute_query_database = 'app_company',
      @query = 'SELECT SR_Service_RecID,Updated_By,Summary,Last_Update FROM SR_Service  WHERE SR_Status_RecID = 561',
      @attach_query_result_as_file= 1,
      @query_attachment_filename='AttachmentFile.csv',
      @query_result_no_padding = 1,
@query_result_separator = ','

Open in new window

ASKER CERTIFIED SOLUTION
Avatar of lcohan
lcohan
Flag of Canada image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial