Howto: send e-mail from SQL Server 2005 – sp_send_dbmail

Problem: I need a stored procedure that checks bugs in the support system and find out if any of them have not been responded or completed in the scheduled time and send out emails to different people according to how urgent they are. The stored procedure is fairly easy to write except to work out the working days. Anyway, the focus here is how to send email from SQL Server.

Solution: The stored procedure xp_sendmail is not recommended to use in 2005, so now we use sp_send_dbmail. First, we need to enable database mail. Right-click on Management node and select Configure Database Mail option, you will be prompted to enable this option if it was not enabled, and setup through the wizard.

Continue reading

Advertisements

Howto: simply concatenate multiple rows of data into a variable in SQL Server 2005

Problem: I have a select statement that returns more than one rows of result, and I need to concatenate all rows into one. For example, if I have a table called ‘Game’ and it returns the following result when you select all and you just want to get a list of games’ names:

gameId Name
1 Rayman: Raving Rabbids
2 Naruto: Rise of a Ninja
3 Guitar Hero III: Legends of Rock
4 Dynasty Warriors 6

Solution: usually I would thought of to loop through it or in more complex cases, to use a cursor. However, I found a really easy way today (you don’t need to declare the variable asNVARCHAR(MAX), just in case if you have heaps of games and happen to use SQL Server 2005 or above).

DECLARE @games NVARCHAR(MAX)
SELECT @games=@games + Name +', ' AS GameList FROM Games
SELECT @games

Result?

GameList
Rayman: Raving Rabbids, Naruto: Rise of a Ninja,Guitar Hero III: Legends of Rock,Dynasty Warriors 6,…

Of course you’ll need to get rid of the last ‘,’ from the variable at the end. How simple, and how could I never knew this before!