TMSEventNotification - new Connection

Discussion of open issues, suggestions and bugs regarding SDAC (SQL Server Data Access Components) for Delphi, C++Builder, Lazarus (and FPC)
Post Reply
MSE
Posts: 3
Joined: Sun 13 Sep 2015 09:45

TMSEventNotification - new Connection

Post by MSE » Sun 13 Sep 2015 09:49

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

azyk
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSEventNotification - new Connection

Post by azyk » Wed 16 Sep 2015 11:49

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
Posts: 3
Joined: Sun 13 Sep 2015 09:45

Re: TMSEventNotification - new Connection

Post by MSE » Thu 17 Sep 2015 10:28

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSEventNotification - new Connection

Post by azyk » Fri 18 Sep 2015 11:49

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
Posts: 3
Joined: Sun 13 Sep 2015 09:45

Re: TMSEventNotification - new Connection

Post by MSE » Fri 18 Sep 2015 12:07

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
Devart Team
Posts: 1057
Joined: Fri 11 Apr 2014 11:47
Location: Alpha Centauri A

Re: TMSEventNotification - new Connection

Post by azyk » Tue 22 Sep 2015 13:08

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.

Post Reply