Dot Net Tips and Tricks

I just found this great thread:

An easier way: Service Listing Manager
Setting up Service Broker routes, endpoints and security is just too hard. One has to run pages and pages of Transact-SQL code just to get the ‘Hello, world’ example work between two separate SQL Instances and the chances of making a mistake are overwhelming.

Well, not anymore! I’ve just uploaded into the Service Broker team code gallery a new GUI tool for doing just that: easily configure two services to be able to have conversations. The tool uses the ‘Service Listing’ concept. A Service Listing is like an identity card for a service. It is an XML document that contains all the necessary information needed to establish a conversation with that service. When two parties need to establish a conversation, they can exchange the Service Listings of the two services and the tool will create the entire infrastructure needed to establish the conversation. Optionally it can also create the message types and contracts supported by a target service in the initiator service database….

Here is a link to it:
Service Listing Manager

While testing a Notification Services install I found I had alot of dead conversations left in my SYS.CONVERSATION_ENDPOINTS

DECLARE @HANDLE UNIQUEIDENTIFIER
DECLARE C CURSOR FAST_FORWARD FOR SELECT CONVERSATION_HANDLE FROM SYS.CONVERSATION_ENDPOINTS
OPEN C;
FETCH NEXT FROM C INTO @HANDLE;
WHILE @@fetch_status = 0
BEGIN
END CONVERSATION @HANDLE WITH CLEANUP
FETCH NEXT FROM C INTO @HANDLE;
END
CLOSE C;
DEALLOCATE C;
GO

You would need to add a check to make sure the timeout period has expired but this works nicely to cleanup any outstanding conversations.

For more info: Microsoft Blog Post

I ran into the problem where I would send messages to the a remote service and the xml validation would fail. After checking the intiator side I couldn’t find an error message being returned from the receiving end. No event logs, nothing inserted into my error table… nothing. This got me digging around and I found a serious bug in my code.

I had purchased the Rational Guide to Service Broker 2005 by Roger Wolter and his pattern details the following:

DECLARE @conversationHandle uniqueidentifier

Begin Transaction

– Begin a dialog to the Order Service
BEGIN TRY
BEGIN DIALOG @conversationHandle
FROM SERVICE [TestCarolina_Message_Service]
TO SERVICE ‘Carolina_Message_Service’
ON CONTRACT [CarolinaOnly_Contract]
WITH ENCRYPTION = OFF, LIFETIME = 600;

– Send a message on the dialog
SEND ON CONVERSATION @conversationHandle
MESSAGE TYPE [msg_to_carolina]
(@Message)

END CONVERSATION @conversationHandle

END TRY
BEGIN CATCH
ROLLBACK
INSERT GENERAL.ERRORS (ERROR)
VALUES( ERROR_MESSAGE())
– Reenable queue
ALTER QUEUE TestCarolina_Message_Queue WITH STATUS = ON
END CATCH

commit

There is one glaring problem with this pattern. If an error happens on the receiving side, the conversation has already been ended on the sender’s side. When the Error is sent from the receiver, the message gets dropped, No EventLog, nothing. The conversation that the receiver is sending on does not exist on the sender anymore.

This leaves the question, how do we cleanup the conversation? Allow the receiver to end the conversation rather than explicitly end in on the sender. This requires the addition of an activated procedure on the sender’s queue that will handle the end conversations, but it adds the ability to handle error messages also. The following in pattern would be the correct way:

CREATE PROCEDURE usp_Message_Queue_Activation
AS
DECLARE @dh UNIQUEIDENTIFIER;
DECLARE @message_type SYSNAME;
DECLARE @message_body NVARCHAR(4000);
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]), TIMEOUT 1000;
WHILE @dh IS NOT NULL
BEGIN
IF @message_type = N’http://schemas.microsoft.com/SQL/ServiceBroker/Error’
BEGIN
INSERT Service_Broker_Errors
(error, [message]) values(N’Received error %s from service [Target]‘, @message_body);
END
END CONVERSATION @dh;
COMMIT;
SELECT @dh = NULL;
BEGIN TRANSACTION;
WAITFOR (
RECEIVE @dh = [conversation_handle],
@message_type = [message_type_name],
@message_body = CAST([message_body] AS NVARCHAR(4000))
FROM [InitiatorQueue]
), TIMEOUT 1000;
END
COMMIT;
GO

– Create queue for messages to go to
CREATE QUEUE Test_Message_Queue
WITH ACTIVATION (
STATUS = ON,
MAX_QUEUE_READERS = 1,
PROCEDURE_NAME = [usp_Message_Queue_Activation],
EXECUTE AS OWNER);
GO

This would allow errors that occur during transmission to be handled gracefully.

I found a nice page for finding the hex code of a particular color.

Colors in ASP.Net

Another nice option is the FireFox’s extension Colorzilla.

This provides a mouse pointer RGB representation of the color under the pointer.

Snippy

I recently stumbled across this little program for managing your code snippets in Visual Studio.

The program is called Snippy the Visual Studio Code Snippet Editor. It made creating common code snippets for my company a breeze. Check it out at Snippy.