Oracle column search to see existance of character from A-Z

One of the dev came to me and told that app is storing date in a character column.. So now they need to use some function to show it as date.

Obviously to_date () was the function I need to use, but when I executed to_date, I found that column is not only storing dates but some character as well.

So I have to extract those characters. This introduced me oracle "regexp_like".

Sql:
select column from table where  regexp_like(column,'[A-Z]','i');

and it's done :)

For details you can refer to the following url:
http://docs.oracle.com/cd/B12037_01/appdev.101/b10795/adfns_re.htm

MSSQL Auto Backup and Dedicated Alerts for Success and Failure

I have configured MSSQl auto backup with schedule routine  for one of our client.We have to send notification to 2 email addresses for success and failure separately.

 USE msdb
  GO
 

--============================================================================
--         Email setup
-- Email setup will require if have not already set .
--read line comments for change
--Execute one by one
--============================================================================

-- Create a Database Mail profile
--Enter suitable profile name and description
EXECUTE msdb.dbo.sysmail_add_profile_sp @profile_name = 'JProfile',
    @description = 'Notification service for SQL Server' ;

-- Create a Database Mail account
-- Enter email server details. validate before excuting. Other valriable can set as per required
EXECUTE msdb.dbo.sysmail_add_account_sp
    @account_name = 'SQL Server Notification Service',
    @description = 'SQL Server Notification Service',
    @email_address = 'youremail@domain', -- Your email address
    @replyto_address = 'youremail@domain',
    @display_name = 'SQL Server Notification Service',
    @mailserver_name = 'smtp.gmail.com', --Your SMTP server
    @username = 'youremail@domain', -- Your smtp user name
     @password = 'my_password', --set your password
    @port = 587,                -- Check with your admin for correct port
    @enable_ssl = True ;        -- Enable ssl communication;

-- Add the account to the profile
-- Profile and Account name should match above created
EXECUTE msdb.dbo.sysmail_add_profileaccount_sp @profile_name = 'Jprofile',
    @account_name = 'SQL Server Notification Service', @sequence_number = 1 ;

-- Grant access to the profile to the DBMailUsers role
EXECUTE msdb.dbo.sysmail_add_principalprofile_sp @profile_name = 'JProfile',
    @principal_id = 0, @is_default = 1 ;

--SELECT * FROM msdb.dbo.sysmail_profile
--SELECT * FROM msdb.dbo.sysmail_account
--System configurations
Execute sp_CONFIGURE 'show advanced', 1 ;
GO
RECONFIGURE;
GO
Execute  sp_CONFIGURE 'Database Mail XPs', 1
GO
RECONFIGURE
GO

 ---test Email

  EXEC msdb..sp_send_dbmail @profile_name = 'JProfile',
    @recipients = 'youremail@domain', @subject = 'Test message',
    @body = 'This is the body of the test message.
Congrats Database Mail Received By you Successfully.'

USE msdb
--========================================================================================
--                            Operator Creation
--========================================================================================
-- Change name as required
-- Change email address to required , this email address(s) will use to nofity failure
EXEC msdb.dbo.sp_add_operator @name=N'OprYourOperator',
        @enabled=1,
        @weekday_pager_start_time=90000,
        @weekday_pager_end_time=180000,
        @saturday_pager_start_time=90000,
        @saturday_pager_end_time=180000,
        @sunday_pager_start_time=90000,
        @sunday_pager_end_time=180000,
        @pager_days=0,
        @email_address=N'yourEmail@domain',
        @category_name=N'[Uncategorized]'
GO


--==================================================================================================
--                                    Backup Job Creation
-- Read line commments to set values as required
--
-- under "Backup step-Step 1", you have to set some value against @command variable
--                SET @path = ''C:\JDB_BKP\'' to you path. observe ' format
--
--                WHERE name NOT IN (''model'',''tempdb'') -- exclude these databases. except these names
--                all databases with get backup   
--
--under "Backup step -Step 2" SET @recipients = ''email@domain'' to your required for success alerts.
--
-- under "Schedule of your backup job" : you can set your schedule.Current is 3AM. current @active_start_time = 30000 (HHMMSS)
--==================================================================================================
BEGIN TRANSACTION
DECLARE @ReturnCode INT
SELECT  @ReturnCode = 0

--adding job
DECLARE @jobId BINARY(16)
EXEC @ReturnCode = msdb.dbo.sp_add_job
        @job_name = N'DBBackup', -- Change job name to your suitable
        @enabled=1,
        @notify_level_eventlog=3,
        @notify_level_email=2,
        @notify_level_netsend=0,
        @notify_level_page=0,
        @delete_level=0,
        @description=N'database backup',
        @notify_email_operator_name = N'OprYourOperator', -- Change it to one you have created
        @job_id = @jobId OUTPUT
IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollback

--=================
-- Backup step
--================
-- Step 1
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep
    @job_id = @jobId,
    @step_name = N'DbBackup',
    @step_id=1,
        @cmdexec_success_code=0,
        @on_success_action=3,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=2,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0,
    @subsystem = N'TSQL',
    @command = N'--variable Declaration
DECLARE @name VARCHAR(50) -- database name 
DECLARE @path VARCHAR(256) -- path for backup files 
DECLARE @fileName VARCHAR(256) -- filename for backup 
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = ''C:\JDB_BKP\'' 

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_bkp_cursor CURSOR FOR 
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN (''model'',''tempdb'')  -- exclude these databases

OPEN db_bkp_cursor  
FETCH NEXT FROM db_bkp_cursor INTO @name  

WHILE @@FETCH_STATUS = 0  
BEGIN  
       SET @fileName = @path + @name +@fileDate+''.BAK'' 
       BACKUP DATABASE @name TO DISK = @fileName WITH  INIT


       FETCH NEXT FROM db_bkp_cursor INTO @name  
END  

CLOSE db_bkp_cursor  
DEALLOCATE db_bkp_cursor', @database_name = N'master', @flags = 0
IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollback
   
--============== Step 2 : On Success Delivery email
EXEC @ReturnCode = msdb.dbo.sp_add_jobstep @job_id=@jobId, @step_name=N'SuccesslStep',
        @step_id=2,
        @cmdexec_success_code=0,
        @on_success_action=1,
        @on_success_step_id=0,
        @on_fail_action=2,
        @on_fail_step_id=0,
        @retry_attempts=0,
        @retry_interval=0,
        @os_run_priority=0,
        @subsystem=N'TSQL',
        @command=N'DECLARE  @job_name varchar(256)
DECLARE  @subject varchar(256)
DECLARE  @recipients varchar(256)


-- Change your email address to email distribution group or person who needs to get
-- Success Notification
SET @recipients = ''youremail@domain''
SELECT @job_name = name
FROM msdb..sysjobs
WHERE job_id = $(ESCAPE_SQUOTE(JOBID))
SET @subject = ''SQL Server Job System: ['' + @job_name + ''] succeeded on ['' + @@servername + '']''


EXEC msdb.dbo.sp_send_dbmail
@recipients = @recipients,
@body = @subject,
@subject = @subject ,
 @body_format = ''html'';',
        @database_name=N'master',
        @flags=0
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
EXEC @ReturnCode = msdb.dbo.sp_update_job @job_id = @jobId, @start_step_id = 1
IF (@@ERROR <> 0 OR @ReturnCode <> 0) GOTO QuitWithRollback
   
--============================
---Schedule of your backup job
--=============================
EXEC @ReturnCode = msdb.dbo.sp_add_jobschedule @job_id = @jobId,
    @name = N'3AmBackup', -- name of schedule , you  can change it to required one
    @enabled = 1, @freq_type = 4, @freq_interval = 1, @freq_subday_type = 1,
    @active_start_date = 20130411, --YYYYMMDD , start date
    @active_end_date = 99991231, --YYYYMMDD , end date
    @active_start_time = 30000
 --HHMMSS, currently 3AM (24 Hr )
       
IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollback

-- Adding you job to server
EXEC @ReturnCode = msdb.dbo.sp_add_jobserver @job_id = @jobId
IF ( @@ERROR <> 0
     OR @ReturnCode <> 0
   )
    GOTO QuitWithRollback
COMMIT TRANSACTION
GOTO EndSave
QuitWithRollback:
IF ( @@TRANCOUNT > 0 )
    ROLLBACK TRANSACTION
EndSave:

GO

USE msdb
--=======================================
-- Alert Creation
-- set required variables and  execcute from declare till end
--======================================

EXEC msdb.dbo.sp_add_alert @name=N'DbBackupAlert', -- your Alert name
        @message_id=0,
        @severity=1,
        @enabled=1,
        @delay_between_responses=0,
        @include_event_description_in=1;
--===== ================================
-- Add notification via email
--==================================
EXEC msdb.dbo.sp_Add_notification @alert_name=N'DbBackupAlert', @operator_name=N'OprYourOperator', @notification_method = 1;

Go