Range Non Existence(^) Searches Using PatIndex

We all know that PatIndex is one of the cool features of T-SQL. It helps to locate the position of the pattern in a given string.

We can also use PatIndex to do range searches. For Example, "[A-Z]" would search for any alphabetical character. "[0-9]" would search for any numeric character.  With the use of these range searches, it gets much easier to find out data issues.

Let us execute the following queries to generate some data for our learning.

  CREATE TABLE #t1 (PATCHECK VARCHAR(20))

  INSERT INTO #T1 (PATCHECK) VALUES ('ABCDEFGHIJKLMN')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA PAT INDEX CHECK')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA 123')

  INSERT INTO #T1 (PATCHECK) VALUES ('SIVA 123 !@#')

  INSERT INTO #T1 (PATCHECK) VALUES ('123456789')

  INSERT INTO #T1 (PATCHECK) VALUES ('!@#$%^&')

if you select everything from this table, this is how it will look like.

Query  - 0
  SELECT * FROM #T1
    ABCDEFGHIJKLMN
    SIVA PAT INDEX CHECK
    SIVA 123
    SIVA 123 !@#
    123456789
    !@#$%^&


Now, Let us execute a simple PatIndex Search. 

Query-1

  SELECT *
  FROM #T1 
  WHERE PATINDEX('%SIVA%',PATCHECK) > 0 

This query would return any row that has the string "SIVA" in it. The results will look like below.     
   SIVA PAT INDEX CHECK
   SIVA 123
   SIVA 123 !@#

Range Search
Here is the example for a range search.
The below mentioned query means that any row that does not have an alphabetical character. 

Query - 2

  SELECT *
  FROM #T1 
  WHERE PATINDEX('%[A-Z]%',PATCHECK)  = 0

results would look like

  123456789

    !@#$%^&

The below mentioned query means that any row that contains (notice the difference in the operator. Previous one was =0, this one below is > 0) alphabetical character. 

Query - 3

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[A-Z]%',PATCHECK)  > 0

results would look like 

   ABCDEFGHIJKLMN

   SIVA PAT INDEX CHECK

   SIVA 123

   SIVA 123 !@#

Range Search with Not Operator

"Not Operator" is added in front of the range set like this [^A-Z] . This means that any non alphabetical character. 

Query - 4 

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[^A-Z]%',PATCHECK)  > 0

results would look like 

SIVA PAT INDEX CHECK

SIVA 123

SIVA 123 !@#

123456789

!@#$%^&

Note that even though query 2 and Query 4 looks similar, they are not producing the same results. 

Multiple Range Searches 

It is also possible to combine multiple range searches in a single query. The below mentioned query would return any row that does have only alphabets or numeric. 

Query - 5

  SELECT *

  FROM #T1 

  WHERE PATINDEX('%[^0-9][^A-Z]%',PATCHECK)  = 0

results would look like. 

ABCDEFGHIJKLMN

123456789

Hope this helps..!!

   

Status of All Constraints in a Database.

One of my developers today reached out to me about knowing the status of the constraints and triggers in a database. He wanted to know whether a constraint was in enabled or disabled state.

The reason behind this question was that he was planning a huge data load in that database and was planning to disable all the constraints and triggers before the load and put the constraints back in the same state after the data load. He wanted to know if some constraints were disabled on purpose and did not want to enable them by mistake after the data load.

Here are some facts about the status change of constrains

• Default, Unique and Primary Key constraints cannot be disabled. 
• Check or Foreign Key constraints can be disabled and enabled.

So, If a default, Unique or Primary Key constraint exists on the database, it can only be in the active state.

The following two queries can give the list of Check and Foreign Key constraints with their status.

SELECT name AS check_constraint_name,

       OBJECT_NAME(parent_object_id) Parent_Object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.check_constraints

SELECT name AS ForeignKeyName,

       OBJECT_NAME(parent_object_id) AS TableName,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.foreign_keys

I gave him one more query that would give the list of triggers in the database with their status.

SELECT name as trigger_name,

       OBJECT_NAME(parent_id) parent_object,

       CASE is_disabled WHEN 1 THEN 'DISABLED' ELSE 'ENABLED' END AS STATUS

FROM   sys.triggers

WHERE  OBJECT_NAME(parent_id) IS NOT NULL

 

Hope this helps.

Thanks and Regards,

Siva.

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..