SQL Server does not need the built-in SQLMAIL mail Method

There is an SQL server in the company database, which needs to send emails during the storage process, but the server does not have OUTLOOK installed. it is inconvenient to use the SQL mail component to send emails, so you need to start from other aspects.

SQLSERVER can have extended components. With this feature, we can write an email sending method in advanced languages and register it as a component that can be accessed by SQL, you can call this method in SQL statements to send emails, which is simple and convenient.
Here we will not introduce the mail class, but will focus on how to register it as a component and call it in SQL.
Suppose we have written a DLL component named ExtenStoreProc in C. dll, which contains the mail method. there is an import tool named regasm in the dotnet installation directory. EXE.. NET developed components are registered as system components.
REGASM Syntax: Microsoft (R). NET Framework Assembly regiity Utility 1.1.4322.573
Copyright (C) Microsoft Corporation 1998-2002. All rights reserved.

Algorithm: RegAsm AssemblyPath [Optional]
Optional values:
/Unregister cancel the unique register type
/Tlb [: FileName] route the component out to the specified type other program and upload it again
/Regfile [: FileName] produces a login token that uses the specified name, instead of the dynamic type.
This option cannot be used with/u or/tlb.
/Codebase set the program development base in Login
/Registered login tests the registered Models
/Nologo do not allow RegAsm to display product information
/Silent non-interest mode. Do not display the success message of the primary row.
/Verbose displays other related information
/? Or/help indicates the usage information

Run the following command: regasm/tlb: ExtenStoreProc. tlb ExtenStoreProc. dll/codebase

If the prompt is successful, this component has been imported into the system component, and we can access this component in SQL.

For example, the next Stored PROCEDURE shows how to call this component create procedure ExtenStoreProcTest

Declare @ object int
DECLARE @ hr int
DECLARE @ property varchar (255)
DECLARE @ return varchar (255)
DECLARE @ src varchar (255), @ desc varchar (255)

-- Initialization
EXEC @ hr = sp_OACreate 'extenstoreproc. extenstoreproc', @ object OUT -- namespace. classname
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END

-- Ignore rows
Declare @ subject varchar (255), @ content varchar (8000), @ mailfrom varchar (255), @ mailto varchar (255), @ cmd varchar (8000)
Set @ subject = 'SQL Mail Test'
Set @ content = 'this is SQL Procedure Send mail'
Set @ mailfrom = 'mailfrom @ company.com'
Set @ mailto = 'mailto @ company.com'
Set @ cmd = 'sendmail ("'+ @ subject +'", "'+ @ content +'", "'+ @ mailfrom + '", "'+ @ mailto +'") '-- SendMail is the method in the component.
Print @ cmd

EXEC @ hr = sp_OAMethod @ object, @ cmd, @ return OUT -- method
-- EXEC @ hr = sp_OAMethod @ object, 'main', @ return OUT -- method
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
PRINT @ return

-- Zookeeper
EXEC @ hr = sp_OADestroy @ object
IF @ hr <> 0
BEGIN
EXEC sp_OAGetErrorInfo @ object, @ src OUT, @ desc OUT
SELECT hr = convert (varbinary (4), @ hr), Source = @ src, Description = @ desc
RETURN
END
GO

The above is the whole process of using SQL to call external components to send emails.