 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / Other Technologies / Service Broker / September 2006How to get rid of old entries in sys.transmission_queue
Hello ! Possibly, someone can help me. I am currently experimenting the the broker service and now, my check: select * from sys.transmission_queue returns a lot of messages, which are several days old and contain, in field "transmission_status", the following text: "The session keys for this conversation could not be created or accessed. The database master key is required for this operation." So, what is the session key and how to get rid of this messages? My test-app worked without having any keys and it resides completely within the same db. Thanks a lot, Manfred Hello Manfred, END CONVERSATION ON those Conversion IDs. Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ Hi ! First, thanks for your asnwer! But, get you tell me how? I read BOL and found that, but I do not understand to do it, I get mostly syntax errors using the samples;So I surely dont understand ... END CONVERSATION needs a dialoghandle, how can I get it? The dialog has happend days before. the sys..transmission_queue contains a field named 'conversation_handle', but no dialog handle and so the statements DECLARE @dialog_handle UNIQUEIDENTIFIER; set @dialog_handle = cast('989F12A6-A721-DB11-A467-00E0815B1503' as uniqueidentifier) END CONVERSATION @dialog_handle fails with: The conversation handle "989F12A6-A721-DB11-A467-00E0815B1503" is not found. Thanks, Manfred > Hello Manfred, > [quoted text clipped - 3 lines] > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ Hello mabra, Conversations are essentially groupings of dialogs between services. ENDING a conversation disposes of all of the messages sent in dialogs between services. Try this: declare @ch uniqueidentifier declare @s nvarchar(4000) select top(1) @ch=conversation_handle from {whatever_your_service_name_is} set @ch = newid() set @s='end conversation '''+ cast(@ch as nvarchar(4000)) + '''' execute(@s) Later, rise, repeat as needed. Cheers, kt Hi ! Thanks again. But this gives me a syntax-error, I could solve :-( This happens in {whatever_your_service_name_is} So this this type of error is unexpected, but my service do no longer exist! Anyway, thanks a lot for your help! The sample from Kent solved the problem! Best regards, Manfred > Hello mabra, > [quoted text clipped - 13 lines] > Cheers, > kt Hello mabra, Sorry, I meant Queue, not service. It was a long day... kt A session key is created for each dialog for use in authentication and encryption of the dialog. The session keys are stored encrypted with the database master key so without a database master key you can't create the session keys so your messages won't be sent. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'password'will create a master key in the current database. Remember the password in case you need to move the database to another SQL Server instance someday.  SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Hello ! > [quoted text clipped - 15 lines] > Thanks a lot, > Manfred Hello ! Thank you. I have never had/set a master key. From your description, this would imply, that my broker experiments all would not run [you said:so your messages won't be sent], but they do. Finally, days later, I saw the messages in the queue and would like to get rid of them. Thanks so far and best regards, Manfred > A session key is created for each dialog for use in authentication and > encryption of the dialog. The session keys are stored encrypted with the [quoted text clipped - 3 lines] > key in the current database. Remember the password in case you need to move > the database to another SQL Server instance someday. Obviously the messages still in the queue didn't get sent. That's why they're still in the queue. You may have eventually set encryption to off in you begin dialog statements so later experiments worked. Creating the master key will cause the messages to be sent. If you want to do the end dialog with cleanup as Kent suggested to get rid of them instead of creating the master key, use the conversation handle in the sys.conversation_endpoints table: declare @handle uniqueidentifier declare conv cursor for select conversation_handle from sys.conversation_endpoints open conv fetch NEXT FROM conv into @handle while @@FETCH_STATUS = 0 Begin END Conversation @handle with cleanup fetch NEXT FROM conv into @handle End close conv deallocate conv SignatureThis posting is provided "AS IS" with no warranties, and confers no rights. Use of included script samples are subject to the terms specified at http://www.microsoft.com/info/cpyright.htm > Hello ! > [quoted text clipped - 16 lines] >> key in the current database. Remember the password in case you need to >> move the database to another SQL Server instance someday. Hello Roger, Neat trick, Chief. :) kt Hi ! Thanks a lot! Very interesting stuff, this helped!!!!! Additionally, your explanation hit the nail onto the head, so I understand the problem with the key. Finally, I think, this material is not for small-brainers ;-) I read a lot, several times, but I cannot get the picture. Although my T-SQL based sample works. It seems to be too complicated for me to use. Especially beginning/ending dialogs, conversations and conversation groups :-( :-( Additionally, I need to write some parts in CLR, because I need to do a lot of parsing, where I need regular expressions, I a missing an OO concept to use service broker from CLR. All you do there is to write T-SQL :-( All I need is a insert trigger, which does not run as part of the inserting transaction and which runs one/multiple stored procedure(s). I just think to write a CLR trigger, which uses a local MSMQ and threads. I do not need the transaction, gurantee, etc. Thanks a lot, Manfred > Obviously the messages still in the queue didn't get sent. That's why > they're still in the queue. You may have eventually set encryption to off [quoted text clipped - 26 lines] > > deallocate conv Hello mabra, > Finally, I think, this material is not for small-brainers ;-) > I read a lot, several times, but I cannot get the picture. > Although my T-SQL based sample works. It seems to be too complicated > for > me to use. Especially beginning/ending dialogs, conversations and > conversation groups :-( :-( I *highly* recommend reading Roger's book. He does a good of covering this stuff. Or you could come to our class, of course... ;) Roger's book is at http://www.mannpublishing.com/Catalog/BookDetail.aspx?BookID=48 > Additionally, I need to write some parts in CLR, because I need to do > a lot of parsing, where I need regular expressions, I a missing an OO > concept to use service broker from CLR. All you do there is to write > T-SQL :-( Your write the required functionality as an assembly, catalog that then map that to a stored procedure, or what might easier to right now -- write CLR based functions and call that from your T-SQL code. Three pieces of advice: a.) Don't give up so easily. b.) I'd make sure you able to get the service broker stuff working first as it is harder than getting the CLR stuff working -- at least IMHO. c.) Practice makes perfect. You really need to write to write smaller applications first then work your way up. > All I need is a insert trigger, which does not run as part of the > inserting transaction and which runs one/multiple stored procedure(s). > I just think to write a CLR trigger, which uses a local MSMQ and > threads. I do not need the transaction, gurantee, etc. The problem with an insert trigger on a table is that it does run as part of the implicit transaction on an insert. Table triggers also aren't reliable in that they can be disable or deleted thus defeating their purpose. You'll also find using MSMQ and threading more challenging in SQLCLR than you might expect. What are you really trying to do? Thanks, Kent Tegels, DevelopMentor http://staff.develop.com/ktegels/ Hi ! And thanks for your extensive help! I have really some question about the workflow of all parts of the broker service and I am planning to get the book. About me:I left development as windows appeared ;-) I came from PC/VAX-VMS background and introduced WIndows NT 3.1 ;-) I moved to the environment team, long time ago, but cannot exist without hacking ;-) My job is mainly planning, maintaining and also operation in a larger environment, sql is effected, but I have not much knowledge on it :-( For your interest:I developed a set of applications - some time ago - which I call "EventlogCollectorServices", it's main purpose is to provide a centralized event store for Windows NT Eventlogs, it' small on it's analyzis :-( There are two services and a web-application. The main services uses WMI to collect the events and they are stored to MSMQ - for several reasons. The DBWriter services stores them into SQL Server. It's running now for over two years, monitoring about 100 servers. Shortly, I saw the 20 million'th event ;-) [If you are interested on more details, go to http://www.manfbraun.de/EventlogCollectorServices/ {needs IE!}] This is currently not published and the source is not currently available, due to the bad docs and the missing setup :-( It is planned to provide it as Open Source, if I am finally able to provide some minimal support ;-) I waited a long time for SQL2005, just because analysing events is really very strange, many, many things have to be decided onto the content of the message text :-( I ended with huge T-SQL scripts just anylysing one message, because it appears in english, german, from Windows NT, Windows 2000 and Windows 2003 and so, even the "equal" events are different :-( That's the hour of .Net and Regular expressions. :-) I've just probed some stored procedure in C# and this is good hell :-) To my insert trigger, the starting point of my interest to the SQL Server Broker:Sometimes I get 500 inserts/per second. If I would use the usual way, the insert transaction would be delayed too long. My new addition the my EventlogCollectorServices should provide e-mail notifications and actions, based on event evaluation. So I have to look for a control table, the event history and something like this. If my broker service is activated, it must work on all events, which have arrived since the trigger was running, may be this are 10 or 20, depending on the current insert rate. So I cannot - for performance reasons - work on one message at a time. But it's currently too early to start developing, not all work to do has been planned yet. I'll take the time for the book and come back with more "consolidated question" in a new thread!!! ;-) Thanks so far and best regards, Manfred > Hello mabra, > [quoted text clipped - 43 lines] > Kent Tegels, DevelopMentor > http://staff.develop.com/ktegels/ Hi Manfred! If you need further information on Service Broker I can also offer you some help, because I'm from Vienna/Austria and I'm not so far away from you :-) I'm also currently working on a book about Service Broker so discussions with you will also help me to deliver a great book on Service Broker. Thanks Klaus Aschenbrenner www.csharp.at http://www.sqljunkies.com/weblog/klaus.aschenbrenner Klaus.Aschenbrenner@csharp.at > Hi ! > [quoted text clipped - 104 lines] >> Kent Tegels, DevelopMentor >> http://staff.develop.com/ktegels/ Hi Klaus ! Thanks for your reply and your offer. Have you read my latest/large post? What do you think? On inserting data, I would just only need some async notification. So far I can understand the service broker scenario, that all would have too much overhead for me, but I am not completely sure. My scenario is best described as "fire-and-gorget". Because the insertion process usually inserts a lot of records per second, I would not make a problem, if one "event" would be missed. So I came to the idea, to build my own trigger, based on CLR and MSMQ;But this will give other probles, like managing activation etc. For example, if a new record is inserted into my table, evaluation on some of it's values would allow me to implement new features in my application. With the "old" style of insert triggers - running inside the inserting transacrion, this would not be possible, due to the long time the evaluating process would need. So I don't have this feature currently, but I originally planned to use a SQL Server Agent Job doing this each minute, which is a bit of late ;-) but finally, until now, I found no time to implement this. I delayed all my wishes to SQL2005, because I would always need .Net regular expressions to evaluate the message content. I implemented some test procedures [UDF/C#], which is really easy. For the inserted new values, I have to execute sql query(ie)s to evaluate the importence of some messages. While this would proceed, new messages would be inserted at the [nearly] "same" time. If the service broker comes into the game, the good feature I see is activation. My stored procedure [SP] would be simply actived by the insert trigger. Additionally, several activations could be done in parallel. The activated SP should just take "all pending messages" and evaluate on them like a batch. But currently, I don't understand conversation groups and process all "pending messages" at once. Am I right?: If I explicitely specify a conversation goup - I see no statements which allow me to do that - than I could just select all of them and instead of doing: RECEIVE TOP(1) I would RECEIVE INTO TABLE[-variable] instead? This would possibly bring me onto the right track ;-) It would be really great, to understand this better, a good book would help me. I was just right on the track to order Rogers book, but I saw, it is based on the beta-preview. BTW, the .Net samples provided by MS: I've expected more OO-alike concepts .... ;-) They just do nothing than to wrap T-SQL. Thanks so far and best regards, viele Grüße, Manfred You could mail me also at: 1manfred.braun1 _AT_ manfbraun.country Remove the "1"'s and my domain's country is de ;-) > Hi Manfred! > [quoted text clipped - 115 lines] >>> Kent Tegels, DevelopMentor >>> http://staff.develop.com/ktegels/ |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.