Author: Lakshmi Kiran Reddy

How to re-queue an email in SQL that has been successfully sent at first time?

Let’s say you send an email from inside a SQL instance using something like the following:

EXEC msdb.dbo.sp_send_dbmail  
    @profile_name = 'SQLZEALOTS_Manager',  
    @recipients = '',  
    @body = 'This email Sent',  
    @subject = 'SqLZeaLoTs' ;

And recipient DOES successfully receive the email.

Now for one reason or another, they want the original email sent again (not an execution of msdb.dbo.sp_send_dbmail, but instead to basically re queue the original email to be sent again).

Step 1: Execute the following:

SELECT mailItem_id
       FROM [msdb].[dbo].[sysmail_mailitems]
       WHERE subject = 'SqLZeaLoTs'
       ORDER BY sent_date desc

Copy the mailItem_id value for the specific email you want to requeue, in this example 2738274.

Use msdb
,@sendmailxml VARCHAR(max)
,@mailID INT = 2738274 -- This is from the mailItem_ID column in [msdb].[dbo].[sysmail_mailitems] for the mail you want to resend. 2738274 is just an example.

SET @sendmailxml = '<requests:SendMail xmlns:xsi="" xsi:schemaLocation=" RequestTypes.xsd" xmlns:requests=""><MailItemId>'
                        + CONVERT(NVARCHAR(20), @mailID) + N'</MailItemId></requests:SendMail>'

    -- Send the send request on queue.
    EXEC @rc = sp_SendMailQueues @sendmailxml
    IF @rc <> 0
       RAISERROR(14627, 16, 1, @rc, 'send mail')

If the query completes successfully, it has queued the original email again and the original recipient will receive another email with exactly the same subject and body.

Step 2 has only been confirmed to work on SQL 2008 R2 SP3.

Note: If you want to try it for later SQL Server versions, you may need to verify the code of step 2 by following the steps.

Script out the system procedure: msdb.dbo.sp_send_dbmail and look for the section near the bottom that executes sp_SendMailQueues. The most important piece to verify is the “SET @sendmailxml…” statement. The reason for this is that sp_SendMailQueues sends the email to a queue that is monitored by a broker that calls DatabaseMail.exe. It is entirely possible that different versions of SQL may have different versions of DatabaseMail.exe that requires the XML to be formatted differently so you want to make sure that the XML is formatted for the version required.

Please try this out on your environment and feedback if you have any comments.

I’d like to grow my readership. If you enjoyed this blog post, please share it with your friends!

Configure BlockedProcessReport in SQL Server

In this blog we will discuss on how to configure Blocked Process report on a particular database. This will be useful for troubleshooting any processes which are getting blocked for more than a specific time threshold. Even though blocking is common behavior in sql, but if the block duration is longer than expected, then we can experience performance issues. We have many ways to identify the blocking in sql (which are not going to be discussed here) but I am going to cover one method using event notifications.

By default, the “blocked process threshold” is zero, meaning that SQL Server won’t generate the blocked process reports. We need to configure by the “blocked process threshold” to a specific value (in seconds) using the sp_configure option. For example, if we set it to 15 seconds, then the event will fire three times if a session is blocked for 45 seconds.

Lets see the step by step process how to configure the blocked process report:

First the database should be enabled for service broker.


We need to create QUEUE,SERVICE,ROUTE and EVENT NOTIFICATION to capture the blocking/deadlock events.



Please note in the above step , we have created a Event Notification at Server level , and on the database where you want to track the blocking for more than the configured value. All the above 3 steps are specific to DB level where as creating notification is at server level.  You can get the list of  already configured event notifications on your server with the below query:

SELECT * FROM sys.server_event_notifications

Now lets create a table to hold the blocking information

IF OBJECT_ID('[dbo].[BlockedProcessReports]') IS NULL
CREATE TABLE [dbo].[BlockedProcessReports]
blocked_process_id int IDENTITY(1,1) PRIMARY KEY,
database_name sysname,
post_time datetime,
blocked_process_report xml

let’s create a stored procedure to read the messages receives from service broker queue  and capture the event in the above table we created:

CREATE PROCEDURE [dbo].[ProcessBlockProcessReports]
DECLARE @message_body XML
,@message_type INT
,@subject VARCHAR(MAX)

WHILE (1 = 1)
-- Receive the next available message from the queue
RECEIVE TOP(1) -- just handle one message at a time
@message_type=message_type_id, --the type of message received
@message_body=CAST(message_body AS XML), -- the message contents
@dialog = conversation_handle -- the identifier of the dialog this message was received on
FROM dbo.BlockedProcessReportQueue
), TIMEOUT 1000 -- if the queue is empty for one second, give UPDATE and go away
-- If we didn't get anything, bail out

INSERT INTO [dbo].[BlockedProcessReports]
SELECT DB_NAME(CAST(@message_body AS
,CAST(@message_body AS XML).value('(/EVENT_INSTANCE/PostTime)[1]',
,CAST(@message_body AS XML)


-- Commit the transaction. At any point before this, we could roll
-- back - the received message would be back on the queue AND the response
-- wouldn't be sent.

The final step is to activate the queue to call the stored procedure

ALTER QUEUE BlockedProcessReportQueue
PROCEDURE_NAME = [dbo].[ProcessBlockProcessReports],

We will test this by creating a blocking scenario manually and lets check whether we can see the blocking information on the table we created:

Lets create an exclusive lock and shared locks on a table and see our table for blocking info.
create table
concurrent screen
table data
block xml.JPG

As you can see above we can see the queries which are involved in blocking from the above xml.

Hope you have enjoyed the post and share your comments

Python Dictionaries

A dictionary is a set of unordered key, value pairs. In a dictionary, the keys must be unique and they are stored in an unordered manner.

In this tutorial you will learn the basics of how to use the Python dictionary.

Creating a Dictionary:


Accessing Items:
You can access the items of a dictionary by referring to its key name, inside square brackets:


Updating Dictionary:
You can update a dictionary by adding a new entry or a key-value pair, modifying an existing entry, or deleting an existing entry as shown below in the simple example −


Loop Through a Dictionary:
You can loop through a dictionary by using a for loop.When looping through a dictionary, the return value are the keys of the dictionary, but there are methods to return the values as well.


Check if Exists:

You can test the presence of a key using ‘in’ or ‘not in’


Restrictions on Dictionary Keys:
Almost any type of value can be used as a dictionary key in Python. As an example,  integer, float, and Boolean objects are used as keys:


However, there are a couple restrictions that dictionary keys must abide by.

First, a given key can appear in a dictionary only once. Duplicate keys are not allowed. A dictionary maps each key to a corresponding value, so it doesn’t make sense to map a particular key more than once.
You could see below that when you assign a value to an already existing dictionary key, it does not add the key a second time, but replaces the existing value:


Restrictions on Dictionary Values:
By contrast, there are no restrictions on dictionary values. Literally none at all. A dictionary value can be any type of object Python supports, including mutable types like lists and dictionaries, and user-defined objects.There is also no restriction against a particular value appearing in a dictionary multiple times.

Hope you have enjoyed the post. Keep reading

Tuple in Python

Tuples in Python are immutable sequences of random objects. Once created, the objects within them cannot be replaced or removed,and new elements cannot be added.

Tuples have a similar syntax to lists except that they are delimited by parentheses rather than square brackets.Here’s a literal tuple containing a string, a float, and an integer.
We can access the elements of a tuple by zero-based index using square brackets, and we can determine the number of elements in the tuple using the built-in len function. We can iterate over tuples using the for loop, and we can concatenate tuples using the plus operator.


Sometimes a single element tuple is required. To write this, we can’t just use a simple number in parentheses. This is because Python pauses that as an integer enclosed in the president’s controlling parentheses of a math expression. To create a single element tuple, we make use of the trailing comma separator, which we’re allowed to use when specifying literal tuples, lists, and dictionaries.

A single element with a trailing comma is passed as a single element tuple. This leaves us with the problem of how to specify an empty tuple. In actuality the answer is simple.
We just use empty parentheses. here is the demo:


In many cases, the parentheses of literal tuples may be omitted. This feature is often used when returning multiple values from a function.

Here we make a function to return the minimum and maximum values of a series, the hard work being done by the two built-in functions min and max. Returning multiple values as tuple is often used in conjunction with a wonderful feature of Python called tuple unpacking.Tuple unpacking is a destructuring operation, which allows us to unpack data structures into named references. For example, we can assign the result of our minmax function to two new references like this.


Tuple unpacking works with arbitrarily nested tuples, although not with other data structures. Should you need to create a tuple from an existing collection object such as a list, you can use the tuple constructor, here also shown for strings.


Hope you have enjoyed the post. Please share your comments

List in python

Python lists such as those returned by the string’s split method are sequences of objects. Unlike strings, lists are mutable in so far as the elements within them can be replaced or removed, and new elements can be inserted or appended.
Literal lists are delimited by square brackets, and the items within the list separated by commas. Here is a list of three numbers and a list of three strings. We can retrieve elements by using square brackets with a zero-based index, and we can replace elements by assigning to a specific element. See how lists can be heterogeneous with respect to the types of the objects. We now have a list containing a string, an integer, and another string.


It’s often useful to create an empty list, which we can do using empty square brackets. We can modify the list in other ways.Let’s add some floats to the end of the list using the append method.


See also how we’re allowed to use an additional comma after the last element, an important maintainability feature.


Slicing is a form of extended indexing which allows us to refer to portions of a list.To use it, we pass the start and stop indices of a half-open range separated by a colon as the square brackets index argument.

Here we slice three words from the list by passing the start index 1 and the stop index 4. This facility can be combined with negative indexing. For example, to take all the elements except the first and last, slice between 1 and -1. Both the start and stop indices are optional. To slice all elements from the second to the end of the list, supply only 2: as the argument to the index operator.


we can copy one list object to another list object in the following ways:


You must be aware, that all of these techniques perform a shallow copy. That is, they create a new list containing the same object references as the source list, but don’t copy the referred to objects.

List repetition:

As for strings and tuples, lists support repetition using the multiplication operator. It’s simple enough to use. Here we repeat a list containing the integers 21 and 37 four times


To find an element in a list, use the index method passing the object you’re searching for. The elements are compared for equivalence or value equality until the one you’re looking for is found and its index returned. Here we create a list W containing few words , Searching for fox using the index method returns the integer 3, which of course allows us to get ahold of that element. If you search for a value that isn’t present like unicorn, you will receive a ValueError. Another means of searching is to count matching elements using the count method. Here we count occurrences of the word the. If you just want to test for membership, you can use the in operator or for nonmembership using the not in operator.


Elements from a list can be removed using del or remove as shown below.


 Before we move on from lists, let’s look at two operations which rearrange the elements in place, reversing and sorting. A list can be reversed in place simply by calling its reverse() method. Similarly, a list can be sorted in place using the sort() method. The sort() method accepts two optional arguments, key and reverse. The latter is self-explanatory, and when set to true gives a descending sort

Hope you have enjoyed the post.Please share your comments