locked
SQL Job - Export query to Excel and email RRS feed

  • Question

  • User1215529056 posted

    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>Description</th>'
    + N'<th>On Hand</th>'
    + N'<th>Last Ship</th></tr>' + CAST((
    SELECT
    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'),
    TYPE
    ) AS NVARCHAR(MAX)) + N'</table>';

    EXEC msdb.dbo.sp_send_dbmail @profile_name = 'SQL2012Mail',
    @recipients ='andy@neyenesch.com',
    @body = @q,
    @subject = 'Jenny Craig Weekly On Hand Inventory',
    @body_format = 'HTML'
    GO

    Anyone have any suggestions on ways to get the results, create an excel spreadsheet and email it out?

    Monday, February 24, 2020 5:05 PM

All replies