Feeds:
Posts
Comments

Archive for the ‘System Stored Procedure’ Category

This is a very common issue, when you work on long running queries or procedures, and the PROBLEM is that you need to check the stored procedure from time to time whether it has been executed successfully or failed or it is still running. Sometimes you are busy with some other stuff and you forget to check the status. I came across this scenario many times specially, when I am working on parallel projects and each of them taking time specially if it is a migration and the database size is too big.

Let me demonstrate step by step, how I resolved the issue and made my life easier.

Step 1 :
First of all you need to configure the database mail in each SQL Server using this article and make sure that is working.

Step 2 :
You need to alter your stored procedure and add few additional scripts. These additional lines of script are basically the TRY…CATCH block and sp_send_dbmail to send Email in case of success or failure. Whenever, the procedure will come across any error or if it is executed successfully, it will generate an email and notify the admin. In addition you can format the email and add any additional information that can help you to trace the error.

Given below is the sample script in which I created a table and inserted records using stored procedure and generated an email whether it has been executed successfully or failed.
This script may take few minutes to execute.

--This script is compatible with SQL Server 2005 and above.
USE tempdb
GO
DROP TABLE tbl_sample
GO
--Create Sample table
CREATE TABLE tbl_sample
(
 [ID] INT IDENTITY(1,1),
 [Name] varchar(10)
)
GO

--DROP PROCEDURE usp_test
--GO
--Create sample stored procedure
CREATE PROCEDURE usp_test
AS
BEGIN TRANSACTION;
BEGIN TRY
	DECLARE @int INT
	SET @int = 1
	WHILE (@int <=400000)
	BEGIN
	    INSERT INTO tbl_sample VALUES ('Test')
		SET @int = @int + 1
	END
END TRY
BEGIN CATCH
    IF @@TRANCOUNT > 0
	 BEGIN
		DECLARE @ErrorMessage NVARCHAR(4000);
		DECLARE @ErrorSeverity INT;
		DECLARE @ErrorState INT;

		SET @ErrorMessage = 'Dear Admin,<BR><BR>Due to some error
        , your procedure ''usp_test'' at Server ' + @@Servername +
        ' has been rollback.<BR>'
        + 'Given below is the error message.
        <BR><BR> ErrorMessage : ' +  ERROR_MESSAGE()

        ROLLBACK TRANSACTION;
		EXEC msdb.dbo.sp_send_dbmail @profile_name='My Profile',
		@recipients='admin@raresql.com',
		@subject='Procedure Failed & Rollback',
        @body= @ErrorMessage,
        @body_format = 'HTML',
		@execute_query_database = 'tempdb'
     END
END CATCH;

IF @@TRANCOUNT > 0
  BEGIN
        COMMIT TRANSACTION;
	    EXEC msdb.dbo.sp_send_dbmail @profile_name='My Profile',
		@recipients='admin@raresql.com',
		@subject='Procedure Executed Successfully',
		@execute_query_database = 'tempdb'
  END
GO

--Execute the Stored Procedure
EXEC usp_test
GO

I found it very handy, let me know if you come across this issue and its resolution.

Read Full Post »

In my earlier article, I explained the importance of sp_refreshview and its implementation with examples. Recently I was working on a project where I used sp_refreshview quite frequently in view of the bulk customization in the database structure. But I needed to customize the standard script to refresh multiple views simultaneously due to the scenarios given below:

  1. If one table has been modified, then all the views related to that particular table should be refreshed.
  2. If multiple tables have been modified, then all views related to those tables should be refreshed.

Let me demonstrate the solution for both issues.

1. If one table has been modified, then all the views related to that particular table should be refreshed.

When I came across this scenario, I thought of finding all views related to the modified table and refresh it one by one but it is obviously time consuming. So alternatively I designed the given below script to accommodate this scenario.

--This script is compatible with SQL Server 2005 and above.
USE tempdb --Change the database name
GO
SELECT DISTINCT 'EXEC sp_refreshview ''' + name + ''''  As [Text]
FROM sys.objects AS A
INNER JOIN sys.sql_expression_dependencies AS B
    ON A.object_id = B.referencing_id
WHERE A.type = 'V'
AND B.referenced_id = OBJECT_ID('tbl_A') --Change the table name
GO

Sp_refreshview_1_1

Once you have the above result set, just execute it in a new query window then all the views related to the above table would be refreshed.

2. If multiple tables have been modified, then all views related to those tables should be refreshed.

This is also very common scenario when you modify multiple tables and you need to refresh all views related to these tables. The standard script of standard script cannot accommodate this scenario, so I designed another script that fits this scenario. You just need to pass the table modification date and it picks all the views related to those tables and refreshes it. Given below is the script.

--This script is compatible with SQL Server 2005 and above.
USE tempdb --Change the database name
GO
DECLARE @Date AS DATETIME
SET @Date='2014-06-15'
--Change the table modification date

SELECT 'EXEC sp_refreshview ''' + name + ''''  As [Text]
 FROM sys.objects AS A
INNER JOIN sys.sql_expression_dependencies AS B
    ON A.object_id = B.referencing_id
WHERE A.type = 'V'
AND B.referenced_id IN (
    SELECT object_id FROM sys.tables
WHERE CONVERT(varchar(11),modify_date)=@Date)
GO

Sp_refreshview_1_2

Once you have the above result set, just execute it in a new query window then all the views related to the modified tables would be refreshed.

Let me know if you come across any scenario like this and its solution.

Read Full Post »