TMSEventNotification - new Connection

[MSE]

How do I prevent the creation of a new connection when using the TMSChangeNotification?
Best Mathias

[azyk]

ChangeNotification functionality is implemented so, that we create a new connection that waits for a message from Service Broker. We can't use the main connection for these purpose, since this will hang the main application thread.

[MSE]

Ok. But, I need on the database the differences between the main-connection and the connection of TMSChangeNotification. How can I find it?
Best regards
Mathias

[azyk]

Please clarify the location of the difference between the main connection and the connection created by ChangeNotification: in the result of the sp_who T-SQL function, in the application or somewhere else?

[MSE]

Hello,
i have a logout-queue and service to delete data when the application is terminated.
I want to drop the queue SDAC_NS_xx_QUEUE and service SDAC_NS_xx if the application crashes. My problem is, i cannot identified the SPID which is used for xx.

Sample Code StoredProcedure:

Code: Select all

	WHILE (1 = 1)
	BEGIN
		DECLARE @messageBody VARBINARY(MAX);
		DECLARE @messageTypeName NVARCHAR(256);
	    WAITFOR ( 
			RECEIVE TOP(1) @messageTypeName = message_type_name, @messageBody = message_body
			FROM Queue_Audit_LogOut
		), TIMEOUT 500
		IF @@ROWCOUNT = 0
		BEGIN
			BREAK;
		END 
      IF (@messageTypeName = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification')
	  BEGIN
		DECLARE  @xmldata xml
		DECLARE  @LoginName nvarchar(128)
		DECLARE  @ApplicationName nvarchar(128)
		DECLARE  @SPID int
		DECLARE  @SID int
		SET @xmldata = CAST(@messageBody AS XML);
		SET @LoginName = @xmldata.value('(/EVENT_INSTANCE/LoginName)[1]', 'nvarchar(128)');
		SET @ApplicationName = @xmldata.value('(/EVENT_INSTANCE/ApplicationName)[1]', 'nvarchar(128)');
		SET @SPID = @xmldata.value('(/EVENT_INSTANCE/SPID)[1]', 'int');

	    if upper(@ApplicationName)='[my Application_Name]'
		BEGIN
		-- clear any data
		END
	    if upper(@ApplicationName)='[my Application_TMSNOTIFICATION]'
			BEGIN
		-- DROP SERVICE SDAC___ if exists
		-- DROP QUEUE SDAC___ if exists
			END
...

best Mathias

[azyk]

In the names: Queues SDAC_NS_xx_QUEUE or Services SDAC_NS_xx, that are created by SDAC, xx is SPID of the main connection. For example, for a Queue named SDAC_NS_63_QUEUE the SPID of the main connection is 63.