SQL SERVER: Configure Database Mail with SQL SERVER 2005

We used Database mail to send mail to client on each updates.

This is a very simple process to configure. Let me share how to configure Database mail with sql server 2005 with all of you.

After setting up Profile and Account properly, you just need to write following code to send a mail to client:

Step 1:

Step 2:

Step 3:

Step 4: You might get this message:

Step 5: Create Profile

Step 6 : Create Account

That's it.

exec msdb.dbo.sp_send_dbmail
@profile_name = 'ProfileName', IN our CASE, 'Tejas'
@recipients = 'Client Email Address' ,
@blind_copy_recipients = 'BCC Address',
@subject = 'Subject',
@BODY = 'Message Body',
@body_format = 'Message Type', it could be text OR html

Let me know if you have any complexity or comments in setting up Database mail.

SQL SERVER: Read values from Comma Separated variable

As we have seen, How to generate Comma separated List in SQL. Today we know, how to get values from Comma separated column. Many times developers asked, How can I read comma separated values from variable? There are many ways to get solution for this. Lets discuss about the best way, I think so. We can use XML to read values from comma separated values. XML made our life easy.

 

Example:

I have created one procedure which has one parameter VARCHAR(100). This procedure contains the parameter having value like '1,5,6,20'. The requirement is: Update the records having ID = 1,5,6,20 (Any of them). This is the one way that we can prepare the SQL query.

 

Proposed Solution:

Convert VARCHAR variable to XML as follows:

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

So this statement will generate XML from VARCHAR value as follows:

  1
  6
  7
  8
  20

So, Now this is the XML, which can be easily read in SQL SERVER (2005 and above) as:

SELECT x.v.value('.','INT')
FROM @xmlIDs.nodes('/IDs/ID') x(v)

This will give me result set as: ( as separate table)

So that’s it, Now I can easily use this result set in my query, to update the rows accordingly.

So my Procedure looks like:

CREATE PROC Test_ReadValuesFromCommaSeparatedVariable
    @str    VARCHAR(100)
AS

DECLARE @XmlIDs XML

SET @xmlIDs =    '
                    ' + REPLACE(@str, ',', '') + '' +
                ''

UPDATE    TableName
SET        Flag = 1
WHERE    ID IN(
                SELECT x.v.value('.','INT')
                FROM @xmlIDs.nodes('/IDs/ID') x(v)
            )
            

 

So, it is very easy to read values from Comma separated value.

 

Let me know if it helps you.