Archive

Archive for the ‘SQL SERVER 2008’ Category

Query Multiple Servers – SQL Server 2008

In this thread I would like to share my experience with multi server queries, a few days ago I received a requirement to query both the development and production servers at a single instance in SQL Server 2008. In brief, the requirement is for querying the stage and fact tables for load count – if matches then success else failure – for both production and development servers. Then I googled a lot about multi server queries and came up with the following query/procedure to validate the load in single instance.

I want you all to take a look at the query below and if you want to change/modify anything please do post a comment which will be a great enhancement for this query

Version: SQL Server 2008

Production Server’s SQL Instance Name: Production

Development Server’s SQL Instance Name: Development

Before querying multiple servers we need to add those servers as linked servers, this can be done either by stored procedures or on Linked Servers (Right click and add new) as shown in screenshot below,

linked servers

Procedure:

ALTER PROCEDURE [dbo].[Factloadtiebackreport]
AS

EXEC Sp_addlinkedserver
'Production'

EXEC Sp_addlinkedserver
'Development'

DECLARE @Stgfactcount INT,
@Factcount INT,
@LoadSuccessful BIT

-- - - Validation in Development Server - - -

SELECT @Stgfactcount = Count(*)
FROM [Development].[Training].dbo.stg_salesa

SELECT @Factcount = Count(*)
FROM [Development].[Training].dbo.factsales

IF @Factcount = @Stgfactcount
BEGIN
PRINT 'The fact load is successful'
PRINT 'Row Count –'
+ Cast(@FactCount AS NVARCHAR(5))
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Status Message – Development'
SET @LoadSuccessful = 1
END
ELSE
BEGIN
PRINT 'Load Failure - Data mismatch'
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'There is a data mismatch between STG and Fact',
@subject = 'Automated Status Message – Development'
END
-- - -  Validation in Production Server - - -

IF @LoadSuccessful = 1
BEGIN
SELECT @Stgfactcount = Count(*)
FROM [Production].[Training].dbo.stg_salesa
SELECT @Factcount = Count(*)
FROM [Production].[Training].dbo.factsales
IF @Factcount = @Stgfactcount
BEGIN
PRINT ‘The fact load is successful'
PRINT 'Row Count –'
+ Cast(@Factcount AS NVARCHAR(5))
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'The stored procedure finished successfully.',
@subject = 'Automated Status Message – Production'
END
ELSE
BEGIN
PRINT 'Load Failure - Data mismatch'
EXEC msdb.dbo.Sp_send_dbmail
@profile_name = 'Gmail',
@recipients = 'yourid.com',
@body = 'There is a data mismatch between STG and Fact',
@subject = 'Automated Status Message – Production'
END
END

EXEC Sp_dropserver
'Production'
EXEC Sp_dropserver
'Development'

Please refer my other blog for Database Mail that is used in this procedure,

https://rdineshkumar.wordpress.com/2011/06/20/database-mail-sql-server-2008/

 

Thanks

Using database mail in sql server 2008

Previously, in one my threads I have explained how to configure database mail for various service providers [Google, Yahoo, Live, etc]. Now let’s see where this database mail can be used in a highly effective manner.

Following are the areas where we can use database mails,

  • During ETL load – for sending the load status
  • During Trigger on Insert, Update and Delete

For Delivering Status:
See the following t-sql code where i have created a table variable holding some sample data [think of an data in some ETL logging process]. For starting, it is now simple with package name and its status

This code will mail the status of load [data from table variable] to the respective mail ids mentioned in @recipients parameter. The same can be implemented in ETL package with an SQL task. I will post such a example in my future threads,

To configure the SQL Server database mail, please follow the steps in my other thread which is as follows,

https://rdineshkumar.wordpress.com/2011/06/20/database-mail-sql-server-2008/

DECLARE @LoadStatus Table (PackageName varchar (50), Result varchar( 50))
INSERT INTO @LoadStatus
SELECT 'Package1' , 'Success' union
SELECT 'Package2' , 'Failed' union
SELECT 'Package3' , 'Inprogress' union
SELECT 'Package4' , 'Failed' union
SELECT 'Package5' , 'Success' union
SELECT 'Package6' , 'Success'

SELECT * FROM @LoadStatus

declare @tableHTML nvarchar (max)
SET @tableHTML =
                                     N'<H3>Load Status</H3>' +
                                     N'<table border="1">' +
                                     N'<tr><th>PackageName</th>' +
                                     N'<th>Result</th></tr>' +
                                     CAST ((
                                     select
                                     case t. Result
                                     WHEN 'failed' then 'Red'
                                     WHEN 'Success' THEN 'Lime'
                                     else 'orange' end AS [@bgcolor] ,
                                     td = PackageName,         '',
                                     td = result, ''
                                     FROM (SELECT Packagename,result from @Loadstatus) t
                                                  ORDER BY Packagename  ASC
                                                  FOR XML PATH( 'tr'), TYPE
                                      )AS NVARCHAR( MAX) ) +

                                     N'</table>' ;

---PRINT @tableHtml

EXEC msdb. dbo.sp_send_dbmail

                            @profile_name='profile-A' ,
                              @recipients ='*****@gmail.com',
                              @from_address = '*****@gmail.com' ,
                              @subject = 'Load Status' ,
                              @body = @tableHTML,
                              @body_format = 'HTML' ;

Output:

You will receive an email like the one below,

Capture

 

 

 

 

 

 

 

 

 

Happy Querying 😉

Database Mail – SQL SERVER 2008

DATABASE MAIL

In this Article, I would like to show a feature from SQL SERVER 2008 which is used to send mail from the database, its DATABASE MAIL

Let’s see how database mail works, and how to configure it for Gmail server.

Since I don’t have a Domain of my own, I have tried it for Gmail server. Configuring database mail is very simple and similar to your outlook mail configuration. All you have to know are the details of your email service provider that you like to configure. There are many email service providers like hotmail, Gmail, yahoo etc,

In my example, I have shown how to configure your database mail for Gmail Server, it is simple to know the details of your service provider, just Google it and there you are.

 

Email Service providers SMTP server name Port number
Hotmail

smtp.live.com

587
Gmail smtp.gmail.com 587
Yahoo smtp.mail.yahoo.com 25
AOL smtp.aol.com 587

a. table 1

The following screen shots will illustrate the steps in configuring the mail server.

Step 1:

Open Database Mail from Management folder, and click next to continue

image

Step 2:

To step a new account db mail account, select the following option.

image

Step 3:

Give a name to your profile and click ‘Add’ to include a new account to profile.

image

Step 4:

Check in Google about the server name and port number of Gmail and other service providers. Fill the details of your email provider. Please refer the initial table for other email service providers (a. table 1)

image

Step 5:

Click next >,

image

image

image

image

The database mail is now configured for Gmail server,

Note: Check the latest Gmail Server’s port number and server name in Google before configuring these settings.

To test the database mail,  Right click on the Database Mail, and click send test mail.

image

Enter the email id and click OK,

The test option will end up as a mail in your inbox.

then, check the mail’s inbox , the test message will be received.

Uses:

  • Send ETL audit information to mail id’s
  • Send Success/Failure notification on any database process
  • Alerting process through Triggers (whenever insert, delete and update happens)
%d bloggers like this: