Tuesday, July 26, 2016

Find text in Stored Procedures, View, Trigger, and Function


http://www.codeproject.com/Tips/603593/Find-text-in-Stored-Procedures-View-Trigger-and-Fu

DECLARE @SEARCHSTRING VARCHAR(255), @notcontain Varchar(255)
SELECT @SEARCHSTRING = 'foobar', @notcontain = ''

SELECT DISTINCT sysobjects.name AS [Object Name] ,
case when sysobjects.xtype = 'P' then 'Stored Proc'
when sysobjects.xtype = 'TF' then 'Function'
when sysobjects.xtype = 'TR' then 'Trigger'
when sysobjects.xtype = 'V' then 'View'
end as [Object Type]
FROM sysobjects,syscomments
WHERE sysobjects.id = syscomments.id
AND sysobjects.type in ('P','TF','TR','V')
AND sysobjects.category = 0
AND CHARINDEX(@SEARCHSTRING,syscomments.text)>0
AND ((CHARINDEX(@notcontain,syscomments.text)=0 
or CHARINDEX(@notcontain,syscomments.text)<>0)) 

CREATE AN EMPTY COPY OF DATABASE

http://stackoverflow.com/questions/3912221/ms-sql-2008-create-a-copy-of-the-database-without-the-data

----------------------------------------------------------------------------------------
-- CREATE AN EMPTY COPY OF DATABASE
----------------------------------------------------------------------------------------
/* PART 1: Backup the good database */
BACKUP DATABASE [OriginalDB] 
        TO  DISK = N'd:\backup.bak' WITH NOFORMAT, INIT,  
            NAME = N'OriginalDB-Full Database Backup', SKIP, 
        NOREWIND, NOUNLOAD,  STATS = 33
GO

/* PART 2: If your destination database already exists, drop it */
USE master -- Make sure to include this -- it allows you to reuse script in same SSMS session
DROP DATABASE [migration]

/* PART 3: Restore the backup to the new location */
RESTORE DATABASE [TargetDB] 
        FROM  DISK = N'D:\backup.bak' WITH  FILE = 1,  
        MOVE N'OriginalDB' TO N'D:\sql data\TargetDB.mdf',  
        MOVE N'OriginalDB' TO N'C:\SQL Data\TargetDB_1.ldf',  
        NOUNLOAD,  STATS = 33
GO

/* PART 4: Delete all tables' data in the migration testing target */
PRINT N'Clearing [TargetDB]'
USE [TargetDB]
EXEC sp_msforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"       -- disable all constraints
EXEC sp_MSForEachTable "DELETE FROM ?"                  -- delete data in all tables
exec sp_msforeachtable "ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"  -- enable all constraints
----------------------------------------------------------------------------------------
-- BLANK DATABASE COPY CREATED, READY FOR TESTING
----------------------------------------------------------------------------------------

Monday, July 25, 2016

Sending Automated Job Email Notifications in SQL Server with SMTP

http://www.howtogeek.com/howto/database/sending-automated-job-email-notifications-in-sql-server-with-smtp/

When you have automated backup jobs running on your database server, sometimes you forget that they are even running. Then you forget to check to see if they are running successfully, and don’t realize until your database crashes and you can’t restore it since you don’t have a current backup.
That’s where email notifications come in, so you can see the job status every morning when you are sipping your coffee and pretending you are working.
SQL Server provides a built-in method of sending emails, but unfortunately it requires you to have Outlook and a profile installed on the server, which isn’t necessarily the ideal way to send an email. Thankfully there is another method, that involves installing a stored procedure on your server that will allow you to send email via SMTP.
************************ SP to send mail*******************
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO

CREATE PROCEDURE [dbo].[sp_SQLNotify] 
   @From varchar(100) ,
   @To varchar(100) ,
   @Subject varchar(100)=" ",
   @Body varchar(4000) = "Motley Fool Inc."
/*********************************************************************

This stored procedure takes the above parameters and sends an e-mail. 
All of the mail configurations are hard-coded in the stored procedure. 
Comments are added to the stored procedure where necessary.
Reference to the CDOSYS objects are at the following MSDN Web site:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_messaging.asp

***********************************************************************/ 
   AS
   Declare @iMsg int
   Declare @hr int
   Declare @source varchar(255)
   Declare @description varchar(500)
   Declare @output varchar(1000)

--************* Create the CDO.Message Object ************************
   EXEC @hr = sp_OACreate 'CDO.Message', @iMsg OUT

--***************Configuring the Message Object ******************
-- This is to configure a remote SMTP server.
-- http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cdosys/html/_cdosys_schema_configuration_sendusing.asp
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/sendusing").Value','2'
-- This is to configure the Server Name or IP address. 
-- Replace MailServerName by the name or IP of your SMTP Server.
   EXEC @hr = sp_OASetProperty @iMsg, 'Configuration.fields("http://schemas.microsoft.com/cdo/configuration/smtpserver").Value', '10.1.1.10' 

-- Save the configurations to the message object.
   EXEC @hr = sp_OAMethod @iMsg, 'Configuration.Fields.Update', null

-- Set the e-mail parameters.
   EXEC @hr = sp_OASetProperty @iMsg, 'To', @To
   EXEC @hr = sp_OASetProperty @iMsg, 'From', @From
   EXEC @hr = sp_OASetProperty @iMsg, 'Subject', @Subject

-- If you are using HTML e-mail, use 'HTMLBody' instead of 'TextBody'.
   EXEC @hr = sp_OASetProperty @iMsg, 'TextBody', @Body
   EXEC @hr = sp_OAMethod @iMsg, 'Send', NULL

-- Sample error handling.
   IF @hr <>0 
     select @hr
     BEGIN
       EXEC @hr = sp_OAGetErrorInfo NULL, @source OUT, @description OUT
       IF @hr = 0
         BEGIN
           SELECT @output = '  Source: ' + @source
           PRINT  @output
           SELECT @output = '  Description: ' + @description
           PRINT  @output
         END
       ELSE
         BEGIN
           PRINT '  sp_OAGetErrorInfo failed.'
           RETURN
         END
     END

-- Do some error handling after each step if you need to.
-- Clean up the objects created.
   EXEC @hr = sp_OADestroy @iMsg
   
   PRINT 'Mail Sent!'



GO
SET QUOTED_IDENTIFIER OFF 
GO
SET ANSI_NULLS ON 
GO
******************************************
You will want to edit one line in the stored procedure to put the IP address of your SMTP server:
EXEC @hr = sp_OASetProperty @iMsg, ‘Configuration.fields(“http://schemas.microsoft.com/cdo/configuration/smtpserver”).Value’, ‘10.1.1.10’
Install the stored procedure into the master database, so it can be easily used from wherever needed.
Open up the SQL Server Agent \ Jobs list, and select the properties for the job you are trying to create a notification for:
Click on the Steps tab, and you should see a screen that looks like this:
Click the New button to create a new job step. We will use this step to send the email notification on success.
Step Name: Email Notification Success
Enter this SQL into the Command window as seen below. You will want to customize the email addresses and message subject to match your environment:
exec master.dbo.sp_SQLNotify ‘server@localserver.com’,’admin@localserver.com’,’Backup Job Success’,’The Backup Job completed successfully’
Click OK and then click the New button again to create another step. This will be the failure notification step.
Step Name: Email Notification Failure
SQL:
exec master.dbo.sp_SQLNotify ‘server@localserver.com’,’admin@localserver.com’,’Backup Job Failure,’The Backup Job failed’
Now the idea is to make the items follow a specific workflow. First click Edit on step 1, and set the properties as shown here:
What we are saying is that on success, go to the success step, and on failure, go to the failure step. Pretty simple stuff.
Now edit the second step, the one labled “Email Notification Success”, and set the properties as seen here:
We are saying that if the notification job is successful, then just quit the job without running step 3. If we don’t specify this, then we will end up getting two emails, one with success and one with failure.
Now edit the third step, the one labled “Email notification failure”, and set the properties as seen here:
 
Now your job steps should look like this:
You should now have email notifications in your inbox for either success or failure.

Note: The stored procedure used in this article was found here, although that may not be the original source.