Last post Feb 25, 2020 06:35 PM by PatriceSc
Feb 24, 2020 05:05 PM|RuthlessRoth|LINK
I have a job that the query results are formatted and email to end users.
It has been requested to have it sent as an excel attached file instead of the results in the email.
The results can easily be copied and pasted from the email into excel.
Here's the current code.
DECLARE @q NVARCHAR(MAX)
SET @q = N'<H4>Weekly On Hand Balance Inventory <br> (01/01/1900 Indicates no shipment yet) </H4>'
+ N'<table border="1">'
+ N'<tr><th>Material No.</th>'
+ N'<th>On Hand</th>'
+ N'<th>Last Ship</th></tr>' + CAST((
td = MaterialCode,
td = Description,
'right' AS 'td/@align',
td = Cast(LastOnHand AS INT),
'right' AS 'td/@align',
td =Convert(varchar(11), lastship,101),
FROM v_JC_OnHand Order by materialcode
FOR XML PATH('tr'),
) AS NVARCHAR(MAX)) + N'</table>';
EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL2012Mail',
@body = @q,
@subject = 'Jenny Craig Weekly On Hand Inventory',
@body_format = 'HTML'
Anyone have any suggestions on ways to get the results, create an excel spreadsheet and email it out?
Feb 25, 2020 02:55 AM|yij sun|LINK
Accroding to your description,as far as I think,there are two solutions:
1.You could build a little SSIS package,deploy it to your server, then schedule it in the SQL Agent
It have a Data Flow task to read the stored procedure output and write to the Excel file, then a Send Mail task handle the emailing of the resulting spreadsheet.
2.If you don't have SSIS on the server available
You could email attached CSV files using sp_send_dbmail.
More details,you could refer to below articles:
Feb 25, 2020 04:52 AM|RuthlessRoth|LINK
Thanks. I have been playing with SSIS today. Made a package that works. The problem I am having now is having the server login with Win. Authen. to schedule the job.
it won't let me login in with SA. Once I get that solved is there a best practice place to save the package?
Any links on setting up the Job and using a package?
Feb 25, 2020 10:06 AM|yij sun|LINK
Accroding to your description,I don't understand your requirment clearly.
Do you tell us what your current issue?Whether the issue that you cann't login in with sa is resolved?
SQL Server Agent Jobs for Packages:
Feb 25, 2020 06:19 PM|RuthlessRoth|LINK
Sorry about not being clear.
1) I have a fully functional SSIS Package. Tested in Visual Studio and it executed as designed.
2) Logged into SS Management Studio with Widows Authentication
3) New Job in SQL Server Agent
4) New Step Type SSIS Package, Use Windows Authentication then in the package file I browse for the package and all I get is Integration Services Catalogs and I can' find may package. Not sure where I need to save the package while in V.S. so I can find
it in Job Agent. All software in on that server.
Feb 25, 2020 06:35 PM|PatriceSc|LINK
This is an additional service on the SQL Server side and the package needs to be published to be available :
If done already it might a permission issue for the account you'll use to run the package. It needs to be "authorized" using https://www.mssqltips.com/sqlservertutorial/220/scheduling-ssis-packages-with-sql-server-agent/
If you need further help a specialized SQL Server forum such as https://social.msdn.microsoft.com/Forums/sqlserver/en-US/home?category=sqlserver will be likely