Send Stored Procedure details by email from MSSQL

Question

User-1901014284 posted

Hi,

I am wondering if it is possible to send an email (using a gmail account) containing data using a stored procedure to a specified email address? 

Any assistance would be greatly appreciated.

Many thanks

Jonny

Answer 1

User753101303 posted

Hi,

See https://www.mssqltips.com/sqlservertip/2578/setup-sql-server-database-mail-to-use-a-gmail-hotmail-yahoo-or-aol-account/ and https://docs.microsoft.com/en-us/sql/relational-databases/database-mail/configure-database-mail?view=sql-server-2017 to configure database mail.

Then https://docs.microsoft.com/en-us/sql/relational-databases/system-stored-procedures/sp-send-dbmail-transact-sql?view=sql-server-2017 to send mails...

Answer 2

User-1901014284 posted

Hi PatriceSc,

Thank you for your reply, unfortunately I only have SQL Server Express so the Database Mail is not an option I am afraid. Do you know of any ways around this?

Thanks

Jonny

Answer 3

User61956409 posted

Hi Jonny,

As you mentioned, SQL Express seems not support the Database Mail feature. To achieve your requirement of sending email form SQL Server Express, you can try to use CLR Stored Procedure.

I do a test using CLR Stored Procedure, which work well on my side, you can refer to the following steps and code snippet to achieve it:

1) Create a library project in VS with the following code

namespace SendEmailTest1
{
    public class SendEmail
    {
        [SqlProcedure]
        public static void Send()
        {
            //your code logic here
            SqlContext.Pipe.Send("Hello world! is comming from CLR SP." + Environment.NewLine);
        }
    }
}

2) Build the library project and verify dll copied in output folder

3) Install CLR procedure in MSSQL Server

-- step1: Creat eassembly
--CREATE ASSEMBLY testassembly1 from 'D:\xxx\SendEmailTest1\obj\Debug\SendEmailTest1.dll' WITH PERMISSION_SET = SAFE

-- step2: Create procedure

--CREATE PROCEDURE SendEmail_SP  
--AS    
--EXTERNAL NAME testassembly1.[SendEmailTest1.SendEmail].Send 


-- step3: Enable "clr enabled" configuration option

--sp_configure 'show advanced options', 1;    
--GO    
--RECONFIGURE;    
--GO    
--sp_configure 'clr enabled', 1;    
--GO    
--RECONFIGURE;    
--GO

4) Execute CLR procedure and check the returned result

With Regards, 

Fei Han

Answer 4

User753101303 posted

Hi,

Then with CLR integration you could use something such as https://www.vnypatel.com/sending-emails-with-clr-sql-server-express-editions

Those classes are reading their configuration from the application config file  ("smtpSettings") and it seems you might be able to configure one using https://stackoverflow.com/questions/28183917/does-sql-server-clr-integration-support-configuration-files

Answer 5

User-1901014284 posted

Thank you all for your responses, I am still looking into this while completing other tasks also. I will respond as soon as I get my head fully around the help you have provided me.

Many thanks for all your help it is greatly appreciated.

Jonny