Nest Software

★Microsoft SQL server suites★

Microsoft SQL Server is a relational database engine from Microsoft that has been supporting business applications for multiple decades.

SQL Server Management Studio (SSMS) - Data storage, management and real-time query processing.

SQL Server Integration Services (SSIS) - Perform export, import, transformation and loading of data.

SQL Server Reporting Services (SSRS) - Report authoring, management and delivery toolset.

SQL Server Analysis Services (SSAS) - Build, manage, analyze, aggregate and roll-up data for Business Intelligence.

SQL Server Agent - Scheduling and notification engine that ships with the relational engine.

Power BI data visualization - Reporting tool with both desktop and cloud based options.

SQL Server Business Intelligence(SSBI): Learn how to take complex data and turn it into business insights using a comprehensive, enterprise-ready business intelligence (BI) platform.

Nest Software

Configure Database Mail in SQL Server

--SELECT DATABASE msdb
sp_configure 'show advanced options', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'Database Mail XPs', 1;
GO
RECONFIGURE WITH OVERRIDE;
GO
sp_configure 'show advanced options', 0;
GO
RECONFIGURE WITH OVERRIDE;
GO

-- Create a Database Mail profile
EXECUTE msdb.dbo.sysmail_add_profile_sp
= 'Notifications',
= 'Profile used for sending outgoing notifications using Gmail.' ;
GO

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp
= 'Notifications',
= 'public',
= 1 ;
GO

-- Create a Database Mail account as your mail server
EXECUTE msdb.dbo.sysmail_add_account_sp
= 'Dipankar',
= 'Mail account for sending outgoing notifications.',
= '[email protected]',
= 'Automated Mailer',
= 'smtp.xyz.com',
= 25,
-- = 0,
= '[email protected]',
= '123456' ;
GO

-- Add the account to the profile
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp
= 'Notifications',
= 'Dipankar',
=1 ;
GO

--If for some reason, ex*****on of the code above returns an error, use the following code to roll back the changes:

EXECUTE msdb.dbo.sysmail_delete_profileaccount_sp = 'Notifications'
EXECUTE msdb.dbo.sysmail_delete_principalprofile_sp = 'Notifications'
EXECUTE msdb.dbo.sysmail_delete_account_sp = 'Dipankar'
EXECUTE msdb.dbo.sysmail_delete_profile_sp = 'Notifications'

--Test Database Mail configuration
EXEC msdb.dbo.sp_send_dbmail
= 'Notifications',
= '[email protected]',
= 'The database mail configuration was completed successfully.',
= 'Automated Success Message';
GO

--Troubleshooting Database Mail
sp_configure 'show advanced', 1;
GO
RECONFIGURE;
GO
sp_configure;
GO

To send e-mails, the user must also be a member of the DatabaseMailUserRole server role. Members of the sysadmin fixed server role and msdb db_owner role are members automatically. This can be easily checked by going to Security > Logins, right-click the current user and select Properties. In the Login Properties dialog click the ‘Server Roles’ page and make sure that the ‘sysadmin’ server role is checked

The Database Mail system logs e-mail activity in the ‘msdb’ database. To view the error messages returned by Database Mail, execute the following code:

SELECT * FROM msdb.dbo.sysmail_event_log;

Send e-mail from a trigger

USE AdventureWorks2014
GO

IF OBJECT_ID ('Production.iProductNotification', 'TR') IS NOT NULL
DROP TRIGGER Purchasing.iProductNotification
GO

CREATE TRIGGER iProductNotification ON Production.Product
FOR INSERT
AS
DECLARE nvarchar(255);
SELECT
= 'A new product, ' + Name + ', is now available for $' + CAST(StandardCost AS nvarchar(20)) + '!'
FROM
INSERTED i;
EXEC msdb.dbo.sp_send_dbmail
= 'Notifications',
= 'Use a valid e-mail address',
= ,
= 'New product notification'
GO

Some suggested troubleshooting steps:

Try to send a message through the same SMTP server, using the same machine as SQL Server, using a client mail program like Outlook. You may get a more meaningful error message.

The port may be blocked. Try sending mail to any other SMTP server using port 587.

Try changing to port 465.

Try unchecking the "requires a secure connection" bit in your profile.

Try using an SMTP server inside your network to relay to GMail. This might be easier to set up and get running. You'll need to install IIS and the SMTP components if you don't have a local SMTP server available to handle the relay.

Read Gmail messages on other email clients using POP - Gmail Help You can open your messages from Gmail in other mail clients that support POP, like Microsoft Outlook. Step 1: Make sure POP is the best way to read your emails IMAP and POP are both ways to rea