SQL Job - Export query to Excel and email

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 ='[e-mail]',
@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?

Answer 1

User1535942433 posted

Hi RuthlessRoth,

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:

https://stackoverflow.com/questions/8143471/export-from-sql-server-to-excel-and-email-as-attachment-through-sql-agent

Best regards,

Yijing Sun

Answer 2

User1215529056 posted

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?

Andy

Answer 3

User1535942433 posted

Hi RuthlessRoth,

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?

More details,you could refer to below articles:

https://serverfault.com/questions/35505/cant-connect-to-sql-server-using-sa-account-what-im-missing

Save package:

https://docs.microsoft.com/en-us/sql/integration-services/save-packages?view=sql-server-ver15

SQL Server Agent Jobs for Packages:

https://docs.microsoft.com/en-us/sql/integration-services/packages/sql-server-agent-jobs-for-packages?view=sql-server-ver15

Best regards,

Yijing Sun

Answer 4

User1215529056 posted

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.

Answer 5

User753101303 posted

Hi,

This is an additional service on the SQL Server side and the package needs to be published to be available : https://docs.microsoft.com/en-us/sql/integration-services/service/integration-services-service-ssis-service?view=sql-server-ver15

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 better...