Feeds:
Posts
Comments

Archive for June, 2014

SQL SERVER – How to delete recent connection from Connect to Server window in SSMS

Posted in Solutions, SQL SERVER, tagged , , , , , , on June 29, 2014| 1 Comment »

How to delete recent connection from Connect to Server window in SSMS is a very common issue and it becomes frustrated if you connect many servers on daily basis and SQL Server Management Studio (SSMS) pile up all the new server names in the list of Connect to Server window as shown below.

list of connection.1.1

I have been facing this common problem since SQL Server 2005 and it has been reported on Connect as well. Fortunately we do have a solution explained in this article that demonstrate step by step how to delete the recent connection list using mru.dat and SqlStudio.bin in SQL Server 2005  and SQL Server 2008 respectively. However, this is NOT smart solutions because it deletes all your recent connections including the active ones as well.

In SQL Server 2012, a proper solution came in the picture and I believe, this is much better than the earlier solution. Let me demonstrate it step by step.

Step 1 :
In order to open “Connect to Server” window, first of all you need to select file menu and click on connect object explorer.. in SSMS as shown below.

list of connection.1.3

Step 2 :
Once you open the Connect to Server window, you will find all recent connections in the server name as shown below but the drawback is you cannot determine what belongs to SQL Authentication and what to Windows.

list of connection.1.1

Step 3 :
Now, you need to eliminate the connection. In order to do it, you need to select the particular connection and press DELETE button from the keyboard. Once you press delete button, it will delete that particular connection but it will remain as selected in the server name dropdown list as shown below. If you need to delete multiple connections, you need to select server one by one and press delete button to delete  it,  as multiple selection is NOT allowed.

list of connection.1.2

Let me know if you come across this scenario and how did you resolve it.

Read Full Post »

SQL SERVER – Sending mail when a procedure has been executed

Posted in Solutions, SQL SERVER, tagged , , , , , on June 28, 2014| 1 Comment »

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 »

SQL SERVER – How to refresh all views related to modified tables

Posted in Solutions, SQL SERVER, tagged , , , on June 15, 2014| 2 Comments »

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 »