In this exercise, you will implement the EmailService service. This service will receive customer details and use SQLiMail to send a welcome e-mail to the customer. It will also send responses to the service that called it.
The C:\Program Files\Microsoft Learning\2734\Labfiles\Lab04\Solution\ ImplementEmailService.sqlfile provides a solution to this exercise if you need to check your Transact-SQL statements.
!
Configure SQLiMailIn this lab you will use SQLiMail to send an e-mail. SQLiMail is not a requirement for Service Broker solutions, but it does depend on Service Broker.
1. In Object Explorer, expand Management.
2. Right-click SQLiMail, and click Configure SQLiMail.
3. On the Welcome to SQLiMail Configuration Wizard page, click Next. 4. Ensure Set up SQLiMail by performing the following tasks is selected
and click Next.
5. In the Database name box, select AdventureWorks, and then click Next. 6. On the New Profile page, enter the following details:
Property Value
Profile name Email Service Mail Profile
Description Profile for Adventure Works E-mail service.
7. Click Add to add a new account. In the New SQLiMail Account dialog box, enter the following details and then click OK:
Property Value
Account name Adventure Works Email Service
Description AW e-mail service account
E-mail address [email protected]
Display name Adventure Works
Reply e-mail [email protected]
Server name localhost
Port number 25 SMTP Server requires authentication Unselected 8. Click Next.
9. On the Manage Profile Security page, click Next. 10. On the Configure System Parameters page, click Next.
Tip
11. Click Finish.
12. When the SQLiMail objects have been created, click Close.
! Configure the AdventureWorks database for SQLiMail
1. On the standard toolbar, click New Query, and then click New SQL Server Query.
2. In the Connect to SQL Server dialog box, enter or confirm the following details and then click Connect:
User Interface Element Value
Server type SQL Server
Server name localhost
Authentication Windows Authentication
3. Enter the following Transact-SQL statement to allow SQLiMail to operate with the AdventureWorks database.
USE AdventureWorks GO
sp_changedbowner sa GO
ALTER DATABASE AdventureWorks SET TRUSTWORTHY ON GO
4. On the Query menu, click Execute.
If you encounter any errors, fix the errors and run the query again.
!
Implement the service program1. On the File menu, click Open, and click File.
2. In the Open File dialog box, locate C:\Program Files\Microsoft
Learning\2734\Labfiles\Lab04\Starter\ImplementEmailService.sqland click
Open.
3. In the Connect to SQL Server dialog box, enter or verify the following details and click Connect:
User Interface Element Value
Server type SQL Server
Server instance localhost
Authentication Windows Authentication
4. Find the comment ##### Add code to process messages here #####. 5. After the comment, declare the following local variables.
Variable Name Type
@conversationHandle UNIQUEIDENTIFIER
@messageTypeName NVARCHAR(256)
6. After the variable declaration, receive a single message from the
EmailService.NewCustomerEmailQueue queue. Store the following columns in the local variables.
Column Name Variable
conversation_handle @conversationHandle message_type_name @messageTypeName message_body @messageBody
Your code should look like the following sample.
DECLARE @conversationHandle UNIQUEIDENTIFIER,
@messageTypeName NVARCHAR(256), @messageBody NVARCHAR(MAX) ;RECEIVE TOP(1) @conversationHandle = conversation_handle, @messageTypeName = message_type_name, @messageBody = message_body FROM EmailService.NewCustomerEmailQueue
7. Add the following code to check to see if a message was found.
IF @@ROWCOUNT = 0 BEGIN
EXEC EmailService.LogMessage 'NewCustomerEmailQueue', 'No further messages found.' RETURN
END
8. Create an IFblock to compare the @messageTypeName variable against the following string values using the ORoperator:
• 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' • 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' 9. Within the IFstatement’s BEGINblock, end the conversation using
@conversationHandle.
10. After ending the conversation, log the message using the
EmailService.LogMessage stored procedure, and return to the top of the WHILEloop using the CONTINUE keyword.
The code in the IF block should look like the following sample.
IF @messageTypeName =
'http://schemas.microsoft.com/SQL/ServiceBroker/Error' OR @messageTypeName =
'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' BEGIN
END CONVERSATION @conversationHandle
EXEC EmailService.LogMessage 'NewCustomerEmailQueue', @messageTypeName, @conversationHandle CONTINUE
11. Create another IFblock, and compare the @messageTypeName variable against the '//Adventure-Works.com/Sales/CustomerDetails' message type name. If the variable does not match the expected message type name, end the conversation with the following error values, log the message, and return to the top of the WHILE loop.
Error Parameter Value
Error number 500
Description 'Invalid message type.'
The IF block should look similar to the following sample.
IF @messageTypeName <>
'//Adventure-Works.com/Sales/CustomerDetails' BEGIN
END CONVERSATION @conversationHandle WITH ERROR = 500
DESCRIPTION = 'Invalid message type.'
EXEC EmailService.LogMessage 'NewCustomerEmailQueue', 'Invalid message type found.',
@conversationHandle CONTINUE
END
12. Add Transact-SQL code to extract the e-mail address from the
@messageBody by typing the following Transact-SQL.
DECLARE @customerName name, @emailAddress Name EXEC EmailService.GetDetailsFromXML @messageBody, @customerName OUTPUT, @emailAddress OUTPUT
13. Add Transact-SQL code to send an e-mail through SQLiMail by typing the following Transact-SQL code:
EXEC dbo.sendimail_sp
@profile_name = 'Email Service Mail Profile',
@recipients= @emailAddress,
@subject='Welcome to Adventure Works',
@body='Your account has been created'
14. Add Transact-SQL code to log the message by using the following Transact-SQL.
DECLARE @output NVARCHAR(MAX) SET @output = 'Received Customer ' + @customerName + '. '
+ 'Email sent to ' + @emailAddress + '.'
EXEC EmailService.LogMessage 'NewCustomerEmailQueue', @output, @conversationHandle
15. End the conversation by using @conversationHandle. 16. On the Query menu, click Execute.
If you encounter any errors, fix the errors and reexecute the query. 17. Keep SQL Server Management Studio open for the next exercise.