Pumpernickel
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';
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.
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.
ASKER
lcohan,
Would I just put this into the jobs in sql how the other one is now?
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.
ASKER
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?
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_B y,Summary, Last_Updat e FROM SR_Service WHERE SR_Status_RecID = 561',
@attach_query_result_as_fi le= 1,
@query_attachment_filename ='Attachme ntFile.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),ISN ULL(Col2, ''''),Col4
FROM ...
SET NOCOUNT OFF;
',
--Additional settings
@query_attachment_filename = '*.csv',
@query_result_separator = ';',
@attach_query_result_as_fi le = 1,
@query_result_no_padding= 1,
@exclude_query_output =1,
@append_query_error = 0,
@query_result_header =0;
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_B
@attach_query_result_as_fi
@query_attachment_filename
@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''
select CONVERT(NVARCHAR,Col1),ISN
FROM ...
SET NOCOUNT OFF;
',
--Additional settings
@query_attachment_filename
@query_result_separator = ';',
@attach_query_result_as_fi
@query_result_no_padding= 1,
@exclude_query_output =1,
@append_query_error = 0,
@query_result_header =0;
ASKER
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 = ','
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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_B
@attach_query_result_as_fi
@query_attachment_filename
@query_result_no_padding = 1,
@query_result_separator=' '