Send emails automatically via SQL Server Express Edition

Question

I've looked all over the internet, but could not find a solution. Is there any way to send e-mail reports automatically using SQL Server Express Edition? I was able to configure my server and it sends e-mail if I run the code manually, but I need it to send the e-mail once a week.

Here is a sample of my script:

exec msdb.dbo.sp_send_dbmail 
@profile_name = 'MailTest3', 
@recipients = '[email protected]', 
@subject = 'Mail Test 2', 
@body = 'Mail Sent Successfully', 
@body_format = 'text'

I set up everything as Andrey Nikolov in the comment below suggested, but it did not worked out. No e-mail's automatically were send.

After checking the logs I found this message:

The EXECUTE permission was denied on the object 'sp_send_dbmail', database 'msdb', schema 'dbo'.as

So it looks like permission issue. But while trying to grant dbo user permissions with

EXEC msdb.dbo.sp_addrolemember @rolename = 'DatabaseMailUserRole'
    ,@membername = 'db_username';
GO

script I got a message, that user could not be found:

Msg 15410, Level 11, State 1, Procedure msdb.dbo.sp_addrolemember, Line 35 [Batch Start Line 0] User or role 'db_username' does not exist in this database.

This script worked only than I tried with domain user.

And the *.bat file started to work only after I ran it with domain user

sqlcmd -S localhost -E -Q "EXEC Send_email_Friday" -o "C:\Log_email.txt"

Answer 1

The problem with SQL Server Express is that there is no Agent to run scripts on schedule. But you can do this using Windows Task Scheduler and sqlcmd.

1. Write a script to generate your report and send it by e-mail and save it to a file. Let's say it is D:\Scripts\WeeklyReport.sql.
2. Run Task scheduler and create a new basic task.
3. Give it a name and define a schedule, weekly in your case.
4. Choose to start a program and browse for sqlcmd.exe. Usually you can find it in C:\Program Files\Microsoft SQL Server\xxx\Tools\Binn, where xxx depends on your version of SQL Server, e.g. 120.
5. Add the arguments on the command line to specify to which server/database to connect, how to authenticate and which script to execute. For example -S MyComputer\SQLEXPRESS -d MyDatabaseName -U myusername -P mypassword -i D:\Scripts\WeeklyReport.sql (in case you will connect using user name and password, i.e. SQL authentication) or omit the parameters for username and password and specify -E to use Windows authentication. In the later case it will connect in the context of the login, which will run the scheduled task.

When you create the scheduled task, open the properties dialog and check the security options. You may want to change them, to run the command even if there is no user currently logged on:

Close the dialog and try to run the task to confirm it works properly. For diagnosting purposes you may want to specify an output file, where the execution results will be saved (add -o D:\Scripts\WeeklyReport.txt).