{"action":"created","issue":{"url":"https://api.github.com/repos/dyatchenko/ServiceBrokerListener/issues/5","labels_url":"https://api.github.com/repos/dyatchenko/ServiceBrokerListener/issues/5/labels{/name}","comments_url":"https://api.github.com/repos/dyatchenko/ServiceBrokerListener/issues/5/comments","events_url":"https://api.github.com/repos/dyatchenko/ServiceBrokerListener/issues/5/events","html_url":"https://github.com/dyatchenko/ServiceBrokerListener/issues/5","id":69931746,"number":5,"title":"How to use your library","user":{"login":"tridip-bba","id":5213076,"avatar_url":"https://avatars.githubusercontent.com/u/5213076?v=3","gravatar_id":"","url":"https://api.github.com/users/tridip-bba","html_url":"https://github.com/tridip-bba","followers_url":"https://api.github.com/users/tridip-bba/followers","following_url":"https://api.github.com/users/tridip-bba/following{/other_user}","gists_url":"https://api.github.com/users/tridip-bba/gists{/gist_id}","starred_url":"https://api.github.com/users/tridip-bba/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tridip-bba/subscriptions","organizations_url":"https://api.github.com/users/tridip-bba/orgs","repos_url":"https://api.github.com/users/tridip-bba/repos","events_url":"https://api.github.com/users/tridip-bba/events{/privacy}","received_events_url":"https://api.github.com/users/tridip-bba/received_events","type":"User","site_admin":false},"labels":[],"state":"open","locked":false,"assignee":null,"milestone":null,"comments":17,"created_at":"2015-04-21T19:32:41Z","updated_at":"2015-04-28T13:10:01Z","closed_at":null,"body":"i am interested to know how to use your library instead of MS provided. it would be nice for all if you come with a tutorial like how to use this in any application and where it is different from built-in sql dependency. \r\n\r\nthanks"},"comment":{"url":"https://api.github.com/repos/dyatchenko/ServiceBrokerListener/issues/comments/97054874","html_url":"https://github.com/dyatchenko/ServiceBrokerListener/issues/5#issuecomment-97054874","issue_url":"https://api.github.com/repos/dyatchenko/ServiceBrokerListener/issues/5","id":97054874,"user":{"login":"tridip-bba","id":5213076,"avatar_url":"https://avatars.githubusercontent.com/u/5213076?v=3","gravatar_id":"","url":"https://api.github.com/users/tridip-bba","html_url":"https://github.com/tridip-bba","followers_url":"https://api.github.com/users/tridip-bba/followers","following_url":"https://api.github.com/users/tridip-bba/following{/other_user}","gists_url":"https://api.github.com/users/tridip-bba/gists{/gist_id}","starred_url":"https://api.github.com/users/tridip-bba/starred{/owner}{/repo}","subscriptions_url":"https://api.github.com/users/tridip-bba/subscriptions","organizations_url":"https://api.github.com/users/tridip-bba/orgs","repos_url":"https://api.github.com/users/tridip-bba/repos","events_url":"https://api.github.com/users/tridip-bba/events{/privacy}","received_events_url":"https://api.github.com/users/tridip-bba/received_events","type":"User","site_admin":false},"created_at":"2015-04-28T13:10:01Z","updated_at":"2015-04-28T13:10:01Z","body":"HI,\n\n \n\nOne urgent question that your ServiceBrokerListener class has been developed using VS2013 ?\n\n \n\nI am working with VS2010. Can you tell me how could I use your ServiceBrokerListener class from my VS2010 IDE ?\n\n \n\nGuide me in detail.\n\n \n\nThanks\n\nTridip\n\n \n\nFrom: Tridip [mailto:tridip@bba-reman.com] \nSent: Tuesday, April 28, 2015 3:00 PM\nTo: 'dyatchenko/ServiceBrokerListener'; 'dyatchenko/ServiceBrokerListener'\nSubject: RE: [ServiceBrokerListener] How to use your library (#5)\n\n \n\nHI,\n\n \n\nI have few question as follows\n\n \n\n1)      is it sure that when someone work with SqlDependencyEx then connection time out will not occurred because you said last time. Why do you think connection time out will not occurred when someone will work with your libraray SqlDependencyEx ?\n\n2)      you developed SqlDependencyEx. So did you develop this libraray from scratch or develop it extending SqlDependencyEx class ?\n\n3)      see the below script I have seen many people use the below script to clean up purpose. Did you use the same ? please see and answer.\n\n \n\nprivate void ClearOldSubscriptions()\n\n{\n\n    using (var connection = new SqlConnection(connectionString))\n\n    {\n\n        using (var command = new SqlCommand())\n\n        {\n\n            string sql =\n\n                ////@\"DECLARE @UniqueTimeout AS int = 3586; \" +\n\n                @\"DECLARE @SubscriptionId AS int; \" +\n\n                @\"DECLARE @Sql AS varchar(max); \" +\n\n                @\"DECLARE SubscriptionCursor CURSOR LOCAL FAST_FORWARD \" +\n\n                @\"    FOR \" +\n\n                @\"        SELECT id \" +\n\n                @\"        FROM sys.dm_qn_subscriptions \" +\n\n                @\"      WHERE database_id = DB_ID() \" +\n\n                @\"            AND timeout = @UniqueTimeout \" +\n\n                @\"OPEN SubscriptionCursor; \" +\n\n                @\"FETCH NEXT FROM SubscriptionCursor INTO @SubscriptionId; \" +\n\n                @\"WHILE @@FETCH_STATUS = 0 \" +\n\n                @\"BEGIN \" +\n\n                @\"    SET @Sql = 'KILL QUERY NOTIFICATION SUBSCRIPTION ' + CONVERT(varchar, @SubscriptionId); \" +\n\n                @\"    EXEC(@Sql); \" +\n\n                @\" \" +\n\n                @\"    FETCH NEXT FROM SubscriptionCursor INTO @SubscriptionId; \" +\n\n                @\"END\";\n\n \n\n            command.Connection = connection;\n\n            command.CommandType = CommandType.Text;\n\n            command.CommandText = sql;\n\n            command.Parameters.Add(\"@UniqueTimeout\", SqlDbType.Int).Value = 432000;\n\n \n\n            connection.Open();\n\n \n\n            command.ExecuteNonQuery();\n\n        }\n\n    }\n\n}\n\n \n\nprivate void ClearNotificationQueue()\n\n{\n\n    using (var connection = new SqlConnection(connectionString))\n\n    {\n\n        using (var command = new SqlCommand())\n\n        {\n\n            string sql =\n\n                @\"DECLARE @Conversation AS uniqueidentifier; \" +\n\n                @\"DECLARE ConversationCursor CURSOR LOCAL FAST_FORWARD  \" +\n\n                @\"    FOR \" +\n\n                @\"        SELECT CEP.conversation_handle FROM sys.conversation_endpoints CEP  \" +\n\n                @\"        WHERE CEP.state = 'DI' or CEP.state = 'CD' \" +\n\n                @\"     \" +\n\n                @\"OPEN ConversationCursor; \" +\n\n                @\"FETCH NEXT FROM ConversationCursor INTO @Conversation; \" +\n\n                @\"WHILE @@FETCH_STATUS = 0  \" +\n\n                @\"BEGIN \" +\n\n                @\"    END CONVERSATION @Conversation WITH CLEANUP; \" +\n\n                @\" \" +\n\n                @\"    FETCH NEXT FROM ConversationCursor INTO @Conversation; \" +\n\n                @\"END \" +\n\n                @\"\";\n\n \n\n            command.Connection = connection;\n\n            command.CommandType = CommandType.Text;\n\n            command.CommandText = sql;\n\n \n\n            connection.Open();\n\n \n\n            command.ExecuteNonQuery();\n\n        }\n\n    }\n\n}\n\n \n\nDid you use the above approach for clean up purpose ? if yes then first one script will not work when people has their database hosted in shared location. So tell me how do you library code manage to Clear Old Subscriptions from sql server when database is hosted in shared location. Our data base is hosted in shared location of ORCSWEB company and I talk to DB admin that I was getting exception when I try to clear old Subscriptions and they said to clear old Subscriptions one need super admin right which they cannot assign to me. So how your application clear old Subscriptions when db is hosted in shared location.\n\n \n\nPlease answer my question point wiese.\n\n \n\nThanks\n\nTridip\n\n \n\nFrom: Dmitriy Dyatchenko [mailto:notifications@github.com] \nSent: Monday, April 27, 2015 3:05 PM\nTo: dyatchenko/ServiceBrokerListener\nCc: tridip-bba\nSubject: Re: [ServiceBrokerListener] How to use your library (#5)\n\n \n\nHi,\n\nThe answers in the chaotic order:\n\n*\tIf an exemplar of the SqlDependencyEx doesn't fire NotificationProcessStopped be sure that everything is going fine. If you experienced this event just restarted the component with Stop() and Start() like I showed above. \n*\tYou don't need to unsubscribe from event notifications unless you dispose the object.\n*\tThe connection timeout event doesn't occur in SqlDependencyEx. \n\nRegards,\nDmitriy\n\n—\nReply to this email directly or view <https://github.com/dyatchenko/ServiceBrokerListener/issues/5#issuecomment-96584352>  it on GitHub.  <https://github.com/notifications/beacon/AE-LlK_oHaa3-qHaqjFgaqwqcti5F_Siks5oDfpZgaJpZM4EFcsP.gif> \n\n"}}