Email Notification when a SQL Agent job status has changed.

We can setup email alerts whenever a SQL Server Agent job fails, completes or succeeds. But what do we do when a SQL job is disabled by someone. It can go unnoticed and lead to many problems.

Today I found that one of my production DB Backup jobs was disabled by someone and I did not know it for a few days.

Here is a trigger that you can use to send you an email whenver a job status is changed.

USE [msdb]
GO

/****** Object:  Trigger [dbo].[tu_sysjobs]    Script Date: 9/5/2012 5:09:21 PM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TRIGGER [dbo].[tu_sysjobs] ON [dbo].[sysjobs]
FOR UPDATE
AS
/*
  Author : Siva Ramasamy
  Date   : 09/05/2012
 
  Description :  This trigger will send an email notification to the operator specified in the variable @v_operator_name whenever a job status is changed.
                 This trigger uses the default profile that is existing to send email notification.

  Pre Requisites : Database mail must be configured inorder for this trigger to fire.
 
*/
BEGIN  

    DECLARE @v_mail_body      VARCHAR(245)
    DECLARE @v_profile_name   VARCHAR(245)
    DECLARE @v_operator_name  VARCHAR(245)
    DECLARE @v_mail_subject   VARCHAR(245)


    SET @v_operator_name = '******PUT YOUR EMAIL ADDRESS HERE******'
    SET @v_mail_subject  = 'TMP PRODUCTION SQL Agent Job Status Change notification'

    SELECT    @v_profile_name = mp.name
    FROM      msdb.dbo.sysmail_profile mp
             INNER JOIN msdb.dbo.sysmail_principalprofile pp
             ON         mp.profile_id = pp.profile_id
    WHERE   pp.is_default = 1

    IF UPDATE(enabled)
    BEGIN      
        SELECT  @v_mail_body = 'Status of the job "'+
                              s.name + '" has changed from ' +
                              CASE d.ENABLED WHEN 1 THEN 'enabled'  ELSE 'disabled' END + ' to ' +
                              CASE s.ENABLED WHEN 1 THEN 'enabled.'  ELSE 'disabled.' END
        FROM  sysjobs s
        INNER JOIN DELETED d
        ON  s.job_id = d.job_id
        WHERE    s.enabled <> d.enabled

        PRINT 'profile name '+@v_profile_name

        EXEC msdb.dbo.sp_send_dbmail
             @profile_name = @v_profile_name,
             @recipients   = @v_operator_name,
             @body         = @v_mail_body ,
             @subject      = @v_mail_subject;
    END 
END
GO

Create database using an existing data files and no log file

If you only have a data file and do not have a log file, you can use the following command to create the database.

This  command will create a log file in the default log file location for the instance.

CREATE DATABASE "DB_NAME" ON (FILENAME = 'FILE_LOCATION') FOR ATTACH_REBUILD_LOG

  

Query to get the List of all Foreign Keys in a database

Please change the name of the database and run it.

USE "database name"

go

SELECT

      OBJECT_NAME(constraint_object_id) FOREIGN_KEY_NAME,

      OBJECT_NAME(parent_object_id)     TABLE_NAME,

      (SELECT name FROM sys.columns WHERE object_id = parent_object_id AND column_id = parent_column_id) COLUMN_NAME,

      OBJECT_NAME(referenced_object_id) REFERENCED_TABLE_NAME,

      (SELECT name FROM sys.columns WHERE object_id = referenced_object_id AND referenced_column_id = column_id) REFERENCED_COLUMN_NAME

FROM sys.foreign_key_columns

ORDER BY REFERENCED_TABLE_NAME

go

Changing SQL Server Instance Name after renaming the physical machine or virtual machine

After a long timeout, I have decided to register something that I learned today.

Here is the scenario.

There is a named SQL Server instance running on a virtual machine. An Image of the entire machine was taken and restored with a different virtual machine name. (This was done as an exercise to create a new staging environment as a look a like copy of the production environment).

After the new virtual machine was created, there is a  named SQL Server instance is now running on the new virtual machine. But the @@SERVERNAME property is still displaying the old machine name.

Here is what you do to change the @@SERVERNAME property.

sp_dropserver  'OLD_HOST_NAME\INSTANCE_NAME'

sp_addserver @server = 'NEW_HOST_NAME\INSTANCE_NAME', @local = 'local'

Restart the SQL Server Instance using SQL Server configuration Manager.

Now execute SELECT @@SERVERNAME or 
                    SELECT SERVERPROPERTY('SERVERNAME')

You will notice that the new host name is displayed correctly.

JDBC Connectivity for SQL Server 2008 R2 Express Named Instances

I have a SQL Server 2008 R2 Express Server running on a development environment. It only had a default instance. I got a request from the development manager to create a named instance on that server.

I created a named instance, tested connectivity from the server and from a remote client using SSMS. It worked fine..

I got an email saying that the dev manager wasn't able to connect to the named instance using "MyEclipse" and his app is also having the same connectivity issue.

I was confused..because I was able to connect the named instance from my laptop using SSMS. I installed SSMS on the Dev Manager's laptop and was able to connect to the named instance as well.. So I wasn't sure where to go.

I also installed SQL Server 2008 R2 Standard on the same server and created a named instance..It did not have any issues with JDBC Connectivity.

After some research through MSDN website..I found out the following solution.


Go to Registry
--------------
HKEY_LOCAL_MACHINE -> SOFTWARE -> Microsoft -> Microsoft SQL Server -> 100 ->

Add a New Key value (If not found)
SQLBrowser

Add a String value inside SQLBrowser
Ssrplistener -  "1"

Restart the SQL Browser Service.

It worked....!!

Select MON-YYYY from DATETIME and GROUP and ORDER BY MON-YYYY

One of my Developers needed my help to write a query.

Here is the requirement.
Table has two columns COL_A (DATETIME) and COL_B (INT)
Data in the table looks like this.

COL_A                                 COL_B
01/01/2012 04:30:000             5
01/04/2012 05:30:000             9
02/05/2012 06:50:000           10
02/14/2012 07:50:000           15
05/14/2012 08:50:000           17
05/22/2012 09:45:000             1
12/12/2012 07:45:000             2

We had to generate a report that would get the maximum value of COL_B for every month and the result should not have any date value for COL_A as the requirement was for month. The result should also be ordered by MONTH.

Here is a query that would work for this scenario.

SELECT substring(convert(varchar(24), dateadd(mm,datediff(mm,0,COL_A),0), 113),4,8),
               max(COL_B)    
FROM 
               TABLE
GROUP BY
              dateadd(mm,datediff(mm,0,COL_A),0)
ORDER BY
              dateadd(mm,datediff(mm,0,COL_A),0)          


So, the result should look like this.

Result:
Month         Max(COL_B)  
Jan-2012       9
Feb-2012     15
May-2012   17
Dec-2012      2

MCTS - 70 432 - Certified

I have been trying to pass the MCTS - 70-432 exam for a long time. I bought the Microsoft Press book and started preparing. But was not confident enough to take it for a while. I used measureup practice tests as well. Finally, I went ahead and took the exam on 01/14/2012 and surprise...I passed with a score of 940 (Minimum Passing score is 700).
I thought of sharing my experience about the MCTS - 70-432 exam..and here I am. I will explain about my preparation methods and areas that need special interest in my next blogs..