|
sql
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
How to Enqueue Messages With Service Broker in a Timely Fashion
instance. The problem is that when the database or network is down the application has nowhere to put the data. To overcome this, I have decided to save the data to a local instance and use Service Broker to move the data from the local table to the remote table. I have the mechanism and understanding now to create the SB objects on the remote instance to receive the messages, but I'm not sure how to get the data into the local queue. What I am looking for is a timely method to remove rows from the local table and put them into the queue, when SB will take over. Can anyone suggest a method of doing this? What I imagine is some SQL Server process that waits for a row to be inserted into the local table and then moves it to the queue. It would then go back and wait for the next row, and so on. If this were in a sproc, I don't know how it would be activated, and the fact that it would run forever might be a problem too. I should add that I am not looking for the method of enqueuing the data - which is well-documented - but the method of looping on the local table removing rows to the queue ad infinitum. Any ideas welcome. TIA Charles
Show quote
Hide quote
"Charles" <bl***@nowhere.com> wrote in message Your best bet sounds like an insert trigger on the table, so that every time news:uNSfzJC#JHA.4204@TK2MSFTNGP04.phx.gbl... > I have an application that puts data into a table in a remote database > instance. The problem is that when the database or network is down the > application has nowhere to put the data. To overcome this, I have decided > to save the data to a local instance and use Service Broker to move the > data from the local table to the remote table. > > I have the mechanism and understanding now to create the SB objects on the > remote instance to receive the messages, but I'm not sure how to get the > data into the local queue. > > What I am looking for is a timely method to remove rows from the local > table and put them into the queue, when SB will take over. > Hi Charles, rows are inserted, the trigger fires. In the body of the trigger there will be a special in-memory table called INSERTED which will contain the rows that have just been inserted into the table. You can then copy those into an XML message and place them into the queue. HTH Hi Bob
A trigger was one thing I wondered about. My initial aim was to make this as bolt-on as possible, so that meant not modifying any existing objects, but as you have also suggested a trigger that makes me think that I should now consider the option. Thanks Charles Show quoteHide quote "bob Simms" <bob.si***@qa.com> wrote in message news:9967B6E8-B366-4CB5-AA42-018B73D0E356@microsoft.com... > "Charles" <bl***@nowhere.com> wrote in message > news:uNSfzJC#JHA.4204@TK2MSFTNGP04.phx.gbl... >> I have an application that puts data into a table in a remote database >> instance. The problem is that when the database or network is down the >> application has nowhere to put the data. To overcome this, I have decided >> to save the data to a local instance and use Service Broker to move the >> data from the local table to the remote table. >> >> I have the mechanism and understanding now to create the SB objects on >> the remote instance to receive the messages, but I'm not sure how to get >> the data into the local queue. >> >> What I am looking for is a timely method to remove rows from the local >> table and put them into the queue, when SB will take over. >> > Hi Charles, > > Your best bet sounds like an insert trigger on the table, so that every > time rows are inserted, the trigger fires. In the body of the trigger > there will be a special in-memory table called INSERTED which will contain > the rows that have just been inserted into the table. You can then copy > those into an XML message and place them into the queue. > > HTH > -- > Bob Simms > Senior Learning Consultant > QA - transforming performance through learning > www.qa.com As Bob stated a trigger might be the best bet. But if you only want certain
rows put into the queue (IE: only ones inserted from a particular app etc.) you may find that if you use a stored procedure to insert the rows that might be a good place to put the data into the q. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Charles" <bl***@nowhere.com> wrote in message news:uNSfzJC%23JHA.4204@TK2MSFTNGP04.phx.gbl... >I have an application that puts data into a table in a remote database >instance. The problem is that when the database or network is down the >application has nowhere to put the data. To overcome this, I have decided >to save the data to a local instance and use Service Broker to move the >data from the local table to the remote table. > > I have the mechanism and understanding now to create the SB objects on the > remote instance to receive the messages, but I'm not sure how to get the > data into the local queue. > > What I am looking for is a timely method to remove rows from the local > table and put them into the queue, when SB will take over. > > Can anyone suggest a method of doing this? What I imagine is some SQL > Server process that waits for a row to be inserted into the local table > and then moves it to the queue. It would then go back and wait for the > next row, and so on. If this were in a sproc, I don't know how it would be > activated, and the fact that it would run forever might be a problem too. > > I should add that I am not looking for the method of enqueuing the data - > which is well-documented - but the method of looping on the local table > removing rows to the queue ad infinitum. > > Any ideas welcome. > > TIA > > Charles > > Hi Andrew
In this case, the intention is to move every row to the remote database. As I replied to Bob, my first thought was to avoid modifying existing objects; including sprocs. However, the local table is filled by a sproc, so it would be a straight-forward step to write to the queue as well. Cheers Charles Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:ORb12YD%23JHA.1252@TK2MSFTNGP04.phx.gbl... > As Bob stated a trigger might be the best bet. But if you only want > certain rows put into the queue (IE: only ones inserted from a particular > app etc.) you may find that if you use a stored procedure to insert the > rows that might be a good place to put the data into the q. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Charles" <bl***@nowhere.com> wrote in message > news:uNSfzJC%23JHA.4204@TK2MSFTNGP04.phx.gbl... >>I have an application that puts data into a table in a remote database >>instance. The problem is that when the database or network is down the >>application has nowhere to put the data. To overcome this, I have decided >>to save the data to a local instance and use Service Broker to move the >>data from the local table to the remote table. >> >> I have the mechanism and understanding now to create the SB objects on >> the remote instance to receive the messages, but I'm not sure how to get >> the data into the local queue. >> >> What I am looking for is a timely method to remove rows from the local >> table and put them into the queue, when SB will take over. >> >> Can anyone suggest a method of doing this? What I imagine is some SQL >> Server process that waits for a row to be inserted into the local table >> and then moves it to the queue. It would then go back and wait for the >> next row, and so on. If this were in a sproc, I don't know how it would >> be activated, and the fact that it would run forever might be a problem >> too. >> >> I should add that I am not looking for the method of enqueuing the data - >> which is well-documented - but the method of looping on the local table >> removing rows to the queue ad infinitum. >> >> Any ideas welcome. >> >> TIA >> >> Charles >> >> > Charles (bl***@nowhere.com) writes:
Show quoteHide quote > I have an application that puts data into a table in a remote database To me it sounds that you have just described a Service Broker queue. Why> instance. The problem is that when the database or network is down the > application has nowhere to put the data. To overcome this, I have > decided to save the data to a local instance and use Service Broker to > move the data from the local table to the remote table. > > I have the mechanism and understanding now to create the SB objects on the > remote instance to receive the messages, but I'm not sure how to get the > data into the local queue. > > What I am looking for is a timely method to remove rows from the local > table and put them into the queue, when SB will take over. > > Can anyone suggest a method of doing this? What I imagine is some SQL > Server process that waits for a row to be inserted into the local table > and then moves it to the queue. It would then go back and wait for the > next row, and so on. If this were in a sproc, I don't know how it would > be activated, and the fact that it would run forever might be a problem > too. save it to a table, and then queue it with Service Broker? Why not just send it? Then Service Broker will store it its internal tables, until it is delivered. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx Hi Erland
I was trying to avoid changing the existing schema. If I accept that this is a change to the original design then it does make most sense to simply write to the queue and dispense with the local table idea. I don't actually have a need for the local table, so I think I will head down this route and see where it leads me. Thanks Charles Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... > Charles (bl***@nowhere.com) writes: >> I have an application that puts data into a table in a remote database >> instance. The problem is that when the database or network is down the >> application has nowhere to put the data. To overcome this, I have >> decided to save the data to a local instance and use Service Broker to >> move the data from the local table to the remote table. >> >> I have the mechanism and understanding now to create the SB objects on >> the >> remote instance to receive the messages, but I'm not sure how to get the >> data into the local queue. >> >> What I am looking for is a timely method to remove rows from the local >> table and put them into the queue, when SB will take over. >> >> Can anyone suggest a method of doing this? What I imagine is some SQL >> Server process that waits for a row to be inserted into the local table >> and then moves it to the queue. It would then go back and wait for the >> next row, and so on. If this were in a sproc, I don't know how it would >> be activated, and the fact that it would run forever might be a problem >> too. > > To me it sounds that you have just described a Service Broker queue. Why > save it to a table, and then queue it with Service Broker? Why not just > send it? Then Service Broker will store it its internal tables, until it > is delivered. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > Hi Erland
A thought occurs to me. I might quite like to keep the data locally, in a 'visible' form, until I know it has been transferred successfully to the remote table. I'm thinking that my existing sproc could continue to write the data to a local table, and also put it into the queue. Then, when the remote end has received the message and put the data into the remote table, it sends a reply message containing the PK of the data it was sent. When the reply gets back to the sender it can use the PK to remove the row from the local table. That might all seem unnecessarily complicated, and I don't know what happens if the reply doesn't get back, if indeed that can ever happen? I imagine that the delivery of the reply is guaranteed just like the outgoing message. Is a reply actually necessary? The books and blogs I have read all show the receiver sending a reply, but perhaps that is redundant if all messages are guaranteed? Obviously, for my modified scheme to work, I would need the reply, but then again if I go with your original suggestion perhaps I don't need a reply, and that would make things run more quickly? Charles Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... > Charles (bl***@nowhere.com) writes: >> I have an application that puts data into a table in a remote database >> instance. The problem is that when the database or network is down the >> application has nowhere to put the data. To overcome this, I have >> decided to save the data to a local instance and use Service Broker to >> move the data from the local table to the remote table. >> >> I have the mechanism and understanding now to create the SB objects on >> the >> remote instance to receive the messages, but I'm not sure how to get the >> data into the local queue. >> >> What I am looking for is a timely method to remove rows from the local >> table and put them into the queue, when SB will take over. >> >> Can anyone suggest a method of doing this? What I imagine is some SQL >> Server process that waits for a row to be inserted into the local table >> and then moves it to the queue. It would then go back and wait for the >> next row, and so on. If this were in a sproc, I don't know how it would >> be activated, and the fact that it would run forever might be a problem >> too. > > To me it sounds that you have just described a Service Broker queue. Why > save it to a table, and then queue it with Service Broker? Why not just > send it? Then Service Broker will store it its internal tables, until it > is delivered. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > That is what service broker does for you (among other things). It is a
guaranteed delivery system so the messages won't get lost. There is no need for an intermediate table, just place the messages in the queue and have another service do the delivery and insert them on the remote server. -- Show quoteHide quoteAndrew J. Kelly SQL MVP Solid Quality Mentors "Charles" <bl***@nowhere.com> wrote in message news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... > Hi Erland > > A thought occurs to me. I might quite like to keep the data locally, in a > 'visible' form, until I know it has been transferred successfully to the > remote table. I'm thinking that my existing sproc could continue to write > the data to a local table, and also put it into the queue. Then, when the > remote end has received the message and put the data into the remote > table, it sends a reply message containing the PK of the data it was sent. > When the reply gets back to the sender it can use the PK to remove the row > from the local table. > > That might all seem unnecessarily complicated, and I don't know what > happens if the reply doesn't get back, if indeed that can ever happen? I > imagine that the delivery of the reply is guaranteed just like the > outgoing message. > > Is a reply actually necessary? The books and blogs I have read all show > the receiver sending a reply, but perhaps that is redundant if all > messages are guaranteed? Obviously, for my modified scheme to work, I > would need the reply, but then again if I go with your original suggestion > perhaps I don't need a reply, and that would make things run more quickly? > > Charles > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >> Charles (bl***@nowhere.com) writes: >>> I have an application that puts data into a table in a remote database >>> instance. The problem is that when the database or network is down the >>> application has nowhere to put the data. To overcome this, I have >>> decided to save the data to a local instance and use Service Broker to >>> move the data from the local table to the remote table. >>> >>> I have the mechanism and understanding now to create the SB objects on >>> the >>> remote instance to receive the messages, but I'm not sure how to get the >>> data into the local queue. >>> >>> What I am looking for is a timely method to remove rows from the local >>> table and put them into the queue, when SB will take over. >>> >>> Can anyone suggest a method of doing this? What I imagine is some SQL >>> Server process that waits for a row to be inserted into the local table >>> and then moves it to the queue. It would then go back and wait for the >>> next row, and so on. If this were in a sproc, I don't know how it would >>> be activated, and the fact that it would run forever might be a problem >>> too. >> >> To me it sounds that you have just described a Service Broker queue. Why >> save it to a table, and then queue it with Service Broker? Why not just >> send it? Then Service Broker will store it its internal tables, until it >> is delivered. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Links for SQL Server Books Online: >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >> SQL 2000: >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > > Hi Andrew
The reason I thought of using an [intermediate] local table was so that I could inspect the data in the normal way in the event that it can't be transferred to the remote server. I haven't decided yet whether I will want to do anything with it locally, but I can see that unless I have good reason I should forego the local table. Thanks Charles Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:O9Iue%23L%23JHA.1492@TK2MSFTNGP03.phx.gbl... > That is what service broker does for you (among other things). It is a > guaranteed delivery system so the messages won't get lost. There is no > need for an intermediate table, just place the messages in the queue and > have another service do the delivery and insert them on the remote server. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Charles" <bl***@nowhere.com> wrote in message > news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >> Hi Erland >> >> A thought occurs to me. I might quite like to keep the data locally, in a >> 'visible' form, until I know it has been transferred successfully to the >> remote table. I'm thinking that my existing sproc could continue to write >> the data to a local table, and also put it into the queue. Then, when the >> remote end has received the message and put the data into the remote >> table, it sends a reply message containing the PK of the data it was >> sent. When the reply gets back to the sender it can use the PK to remove >> the row from the local table. >> >> That might all seem unnecessarily complicated, and I don't know what >> happens if the reply doesn't get back, if indeed that can ever happen? I >> imagine that the delivery of the reply is guaranteed just like the >> outgoing message. >> >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? >> >> Charles >> >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>> Charles (bl***@nowhere.com) writes: >>>> I have an application that puts data into a table in a remote database >>>> instance. The problem is that when the database or network is down the >>>> application has nowhere to put the data. To overcome this, I have >>>> decided to save the data to a local instance and use Service Broker to >>>> move the data from the local table to the remote table. >>>> >>>> I have the mechanism and understanding now to create the SB objects on >>>> the >>>> remote instance to receive the messages, but I'm not sure how to get >>>> the >>>> data into the local queue. >>>> >>>> What I am looking for is a timely method to remove rows from the local >>>> table and put them into the queue, when SB will take over. >>>> >>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>> Server process that waits for a row to be inserted into the local table >>>> and then moves it to the queue. It would then go back and wait for the >>>> next row, and so on. If this were in a sproc, I don't know how it would >>>> be activated, and the fact that it would run forever might be a problem >>>> too. >>> >>> To me it sounds that you have just described a Service Broker queue. Why >>> save it to a table, and then queue it with Service Broker? Why not just >>> send it? Then Service Broker will store it its internal tables, until it >>> is delivered. >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Links for SQL Server Books Online: >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>> SQL 2000: >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >> >> > You can do a SELECT on a queue.
-- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Charles" <bl***@nowhere.com> wrote in message The reason I thought of using an [intermediate] local table was so that Inews:OgaGBHN%23JHA.4692@TK2MSFTNGP02.phx.gbl... Hi Andrew could inspect the data in the normal way in the event that it can't be transferred to the remote server. I haven't decided yet whether I will want to do anything with it locally, but I can see that unless I have good reason I should forego the local table. Thanks Charles Show quoteHide quote "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message news:O9Iue%23L%23JHA.1492@TK2MSFTNGP03.phx.gbl... > That is what service broker does for you (among other things). It is a > guaranteed delivery system so the messages won't get lost. There is no > need for an intermediate table, just place the messages in the queue and > have another service do the delivery and insert them on the remote server. > > -- > Andrew J. Kelly SQL MVP > Solid Quality Mentors > > > "Charles" <bl***@nowhere.com> wrote in message > news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >> Hi Erland >> >> A thought occurs to me. I might quite like to keep the data locally, in a >> 'visible' form, until I know it has been transferred successfully to the >> remote table. I'm thinking that my existing sproc could continue to write >> the data to a local table, and also put it into the queue. Then, when the >> remote end has received the message and put the data into the remote >> table, it sends a reply message containing the PK of the data it was >> sent. When the reply gets back to the sender it can use the PK to remove >> the row from the local table. >> >> That might all seem unnecessarily complicated, and I don't know what >> happens if the reply doesn't get back, if indeed that can ever happen? I >> imagine that the delivery of the reply is guaranteed just like the >> outgoing message. >> >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? >> >> Charles >> >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>> Charles (bl***@nowhere.com) writes: >>>> I have an application that puts data into a table in a remote database >>>> instance. The problem is that when the database or network is down the >>>> application has nowhere to put the data. To overcome this, I have >>>> decided to save the data to a local instance and use Service Broker to >>>> move the data from the local table to the remote table. >>>> >>>> I have the mechanism and understanding now to create the SB objects on >>>> the >>>> remote instance to receive the messages, but I'm not sure how to get >>>> the >>>> data into the local queue. >>>> >>>> What I am looking for is a timely method to remove rows from the local >>>> table and put them into the queue, when SB will take over. >>>> >>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>> Server process that waits for a row to be inserted into the local table >>>> and then moves it to the queue. It would then go back and wait for the >>>> next row, and so on. If this were in a sproc, I don't know how it would >>>> be activated, and the fact that it would run forever might be a problem >>>> too. >>> >>> To me it sounds that you have just described a Service Broker queue. Why >>> save it to a table, and then queue it with Service Broker? Why not just >>> send it? Then Service Broker will store it its internal tables, until it >>> is delivered. >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Links for SQL Server Books Online: >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>> SQL 2000: >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >> >> > Hi Tom
I would get back the XML message though, wouldn't I? I realise I could then 'unformat' the message but it's not in what I think of as its native table form. I admit, still doable though. Charles Show quoteHide quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OB7irUN%23JHA.200@TK2MSFTNGP05.phx.gbl... > You can do a SELECT on a queue. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Charles" <bl***@nowhere.com> wrote in message > news:OgaGBHN%23JHA.4692@TK2MSFTNGP02.phx.gbl... > Hi Andrew > > The reason I thought of using an [intermediate] local table was so that I > could inspect the data in the normal way in the event that it can't be > transferred to the remote server. I haven't decided yet whether I will > want > to do anything with it locally, but I can see that unless I have good > reason > I should forego the local table. > > Thanks > > Charles > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:O9Iue%23L%23JHA.1492@TK2MSFTNGP03.phx.gbl... >> That is what service broker does for you (among other things). It is a >> guaranteed delivery system so the messages won't get lost. There is no >> need for an intermediate table, just place the messages in the queue and >> have another service do the delivery and insert them on the remote >> server. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Charles" <bl***@nowhere.com> wrote in message >> news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >>> Hi Erland >>> >>> A thought occurs to me. I might quite like to keep the data locally, in >>> a >>> 'visible' form, until I know it has been transferred successfully to the >>> remote table. I'm thinking that my existing sproc could continue to >>> write >>> the data to a local table, and also put it into the queue. Then, when >>> the >>> remote end has received the message and put the data into the remote >>> table, it sends a reply message containing the PK of the data it was >>> sent. When the reply gets back to the sender it can use the PK to remove >>> the row from the local table. >>> >>> That might all seem unnecessarily complicated, and I don't know what >>> happens if the reply doesn't get back, if indeed that can ever happen? I >>> imagine that the delivery of the reply is guaranteed just like the >>> outgoing message. >>> >>> Is a reply actually necessary? The books and blogs I have read all show >>> the receiver sending a reply, but perhaps that is redundant if all >>> messages are guaranteed? Obviously, for my modified scheme to work, I >>> would need the reply, but then again if I go with your original >>> suggestion perhaps I don't need a reply, and that would make things run >>> more quickly? >>> >>> Charles >>> >>> >>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>>> Charles (bl***@nowhere.com) writes: >>>>> I have an application that puts data into a table in a remote database >>>>> instance. The problem is that when the database or network is down the >>>>> application has nowhere to put the data. To overcome this, I have >>>>> decided to save the data to a local instance and use Service Broker to >>>>> move the data from the local table to the remote table. >>>>> >>>>> I have the mechanism and understanding now to create the SB objects on >>>>> the >>>>> remote instance to receive the messages, but I'm not sure how to get >>>>> the >>>>> data into the local queue. >>>>> >>>>> What I am looking for is a timely method to remove rows from the local >>>>> table and put them into the queue, when SB will take over. >>>>> >>>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>>> Server process that waits for a row to be inserted into the local >>>>> table >>>>> and then moves it to the queue. It would then go back and wait for the >>>>> next row, and so on. If this were in a sproc, I don't know how it >>>>> would >>>>> be activated, and the fact that it would run forever might be a >>>>> problem >>>>> too. >>>> >>>> To me it sounds that you have just described a Service Broker queue. >>>> Why >>>> save it to a table, and then queue it with Service Broker? Why not just >>>> send it? Then Service Broker will store it its internal tables, until >>>> it >>>> is delivered. >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>> >>>> Links for SQL Server Books Online: >>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>>> SQL 2000: >>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>> >>> >>> >> > > Well, SQL 2005 has XML as a native datatype, with associated methods, so
reconstituting the data in tabular form would be straightforward. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Charles" <bl***@nowhere.com> wrote in message I would get back the XML message though, wouldn't I? I realise I could thennews:ObvwdKO%23JHA.4560@TK2MSFTNGP05.phx.gbl... Hi Tom 'unformat' the message but it's not in what I think of as its native table form. I admit, still doable though. Charles Show quoteHide quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:OB7irUN%23JHA.200@TK2MSFTNGP05.phx.gbl... > You can do a SELECT on a queue. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Charles" <bl***@nowhere.com> wrote in message > news:OgaGBHN%23JHA.4692@TK2MSFTNGP02.phx.gbl... > Hi Andrew > > The reason I thought of using an [intermediate] local table was so that I > could inspect the data in the normal way in the event that it can't be > transferred to the remote server. I haven't decided yet whether I will > want > to do anything with it locally, but I can see that unless I have good > reason > I should forego the local table. > > Thanks > > Charles > > > "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message > news:O9Iue%23L%23JHA.1492@TK2MSFTNGP03.phx.gbl... >> That is what service broker does for you (among other things). It is a >> guaranteed delivery system so the messages won't get lost. There is no >> need for an intermediate table, just place the messages in the queue and >> have another service do the delivery and insert them on the remote >> server. >> >> -- >> Andrew J. Kelly SQL MVP >> Solid Quality Mentors >> >> >> "Charles" <bl***@nowhere.com> wrote in message >> news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >>> Hi Erland >>> >>> A thought occurs to me. I might quite like to keep the data locally, in >>> a >>> 'visible' form, until I know it has been transferred successfully to the >>> remote table. I'm thinking that my existing sproc could continue to >>> write >>> the data to a local table, and also put it into the queue. Then, when >>> the >>> remote end has received the message and put the data into the remote >>> table, it sends a reply message containing the PK of the data it was >>> sent. When the reply gets back to the sender it can use the PK to remove >>> the row from the local table. >>> >>> That might all seem unnecessarily complicated, and I don't know what >>> happens if the reply doesn't get back, if indeed that can ever happen? I >>> imagine that the delivery of the reply is guaranteed just like the >>> outgoing message. >>> >>> Is a reply actually necessary? The books and blogs I have read all show >>> the receiver sending a reply, but perhaps that is redundant if all >>> messages are guaranteed? Obviously, for my modified scheme to work, I >>> would need the reply, but then again if I go with your original >>> suggestion perhaps I don't need a reply, and that would make things run >>> more quickly? >>> >>> Charles >>> >>> >>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>>> Charles (bl***@nowhere.com) writes: >>>>> I have an application that puts data into a table in a remote database >>>>> instance. The problem is that when the database or network is down the >>>>> application has nowhere to put the data. To overcome this, I have >>>>> decided to save the data to a local instance and use Service Broker to >>>>> move the data from the local table to the remote table. >>>>> >>>>> I have the mechanism and understanding now to create the SB objects on >>>>> the >>>>> remote instance to receive the messages, but I'm not sure how to get >>>>> the >>>>> data into the local queue. >>>>> >>>>> What I am looking for is a timely method to remove rows from the local >>>>> table and put them into the queue, when SB will take over. >>>>> >>>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>>> Server process that waits for a row to be inserted into the local >>>>> table >>>>> and then moves it to the queue. It would then go back and wait for the >>>>> next row, and so on. If this were in a sproc, I don't know how it >>>>> would >>>>> be activated, and the fact that it would run forever might be a >>>>> problem >>>>> too. >>>> >>>> To me it sounds that you have just described a Service Broker queue. >>>> Why >>>> save it to a table, and then queue it with Service Broker? Why not just >>>> send it? Then Service Broker will store it its internal tables, until >>>> it >>>> is delivered. >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>> >>>> Links for SQL Server Books Online: >>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>>> SQL 2000: >>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>> >>> >>> >> > > Agreed. I think I have decided not to use a local table; just the queue.
Cheers Charles Show quoteHide quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:ObH8SvO%23JHA.2824@TK2MSFTNGP03.phx.gbl... > Well, SQL 2005 has XML as a native datatype, with associated methods, so > reconstituting the data in tabular form would be straightforward. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Charles" <bl***@nowhere.com> wrote in message > news:ObvwdKO%23JHA.4560@TK2MSFTNGP05.phx.gbl... > Hi Tom > > I would get back the XML message though, wouldn't I? I realise I could > then > 'unformat' the message but it's not in what I think of as its native table > form. I admit, still doable though. > > Charles > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:OB7irUN%23JHA.200@TK2MSFTNGP05.phx.gbl... >> You can do a SELECT on a queue. >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS >> SQL Server MVP >> Toronto, ON Canada >> https://mvp.support.microsoft.com/profile/Tom.Moreau >> >> >> "Charles" <bl***@nowhere.com> wrote in message >> news:OgaGBHN%23JHA.4692@TK2MSFTNGP02.phx.gbl... >> Hi Andrew >> >> The reason I thought of using an [intermediate] local table was so that I >> could inspect the data in the normal way in the event that it can't be >> transferred to the remote server. I haven't decided yet whether I will >> want >> to do anything with it locally, but I can see that unless I have good >> reason >> I should forego the local table. >> >> Thanks >> >> Charles >> >> >> "Andrew J. Kelly" <sqlmvpnooospam@shadhawk.com> wrote in message >> news:O9Iue%23L%23JHA.1492@TK2MSFTNGP03.phx.gbl... >>> That is what service broker does for you (among other things). It is a >>> guaranteed delivery system so the messages won't get lost. There is no >>> need for an intermediate table, just place the messages in the queue and >>> have another service do the delivery and insert them on the remote >>> server. >>> >>> -- >>> Andrew J. Kelly SQL MVP >>> Solid Quality Mentors >>> >>> >>> "Charles" <bl***@nowhere.com> wrote in message >>> news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >>>> Hi Erland >>>> >>>> A thought occurs to me. I might quite like to keep the data locally, in >>>> a >>>> 'visible' form, until I know it has been transferred successfully to >>>> the >>>> remote table. I'm thinking that my existing sproc could continue to >>>> write >>>> the data to a local table, and also put it into the queue. Then, when >>>> the >>>> remote end has received the message and put the data into the remote >>>> table, it sends a reply message containing the PK of the data it was >>>> sent. When the reply gets back to the sender it can use the PK to >>>> remove >>>> the row from the local table. >>>> >>>> That might all seem unnecessarily complicated, and I don't know what >>>> happens if the reply doesn't get back, if indeed that can ever happen? >>>> I >>>> imagine that the delivery of the reply is guaranteed just like the >>>> outgoing message. >>>> >>>> Is a reply actually necessary? The books and blogs I have read all show >>>> the receiver sending a reply, but perhaps that is redundant if all >>>> messages are guaranteed? Obviously, for my modified scheme to work, I >>>> would need the reply, but then again if I go with your original >>>> suggestion perhaps I don't need a reply, and that would make things run >>>> more quickly? >>>> >>>> Charles >>>> >>>> >>>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>>> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>>>> Charles (bl***@nowhere.com) writes: >>>>>> I have an application that puts data into a table in a remote >>>>>> database >>>>>> instance. The problem is that when the database or network is down >>>>>> the >>>>>> application has nowhere to put the data. To overcome this, I have >>>>>> decided to save the data to a local instance and use Service Broker >>>>>> to >>>>>> move the data from the local table to the remote table. >>>>>> >>>>>> I have the mechanism and understanding now to create the SB objects >>>>>> on >>>>>> the >>>>>> remote instance to receive the messages, but I'm not sure how to get >>>>>> the >>>>>> data into the local queue. >>>>>> >>>>>> What I am looking for is a timely method to remove rows from the >>>>>> local >>>>>> table and put them into the queue, when SB will take over. >>>>>> >>>>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>>>> Server process that waits for a row to be inserted into the local >>>>>> table >>>>>> and then moves it to the queue. It would then go back and wait for >>>>>> the >>>>>> next row, and so on. If this were in a sproc, I don't know how it >>>>>> would >>>>>> be activated, and the fact that it would run forever might be a >>>>>> problem >>>>>> too. >>>>> >>>>> To me it sounds that you have just described a Service Broker queue. >>>>> Why >>>>> save it to a table, and then queue it with Service Broker? Why not >>>>> just >>>>> send it? Then Service Broker will store it its internal tables, until >>>>> it >>>>> is delivered. >>>>> >>>>> >>>>> -- >>>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>>> >>>>> Links for SQL Server Books Online: >>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>>>> SQL 2000: >>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>>> >>>> >>>> >>> >> >> > > Charles (bl***@nowhere.com) writes:
> A thought occurs to me. I might quite like to keep the data locally, in Well, there is one reason the you would never get a reply back: it is> a 'visible' form, until I know it has been transferred successfully to > the remote table. I'm thinking that my existing sproc could continue to > write the data to a local table, and also put it into the queue. Then, > when the remote end has received the message and put the data into the > remote table, it sends a reply message containing the PK of the data it > was sent. When the reply gets back to the sender it can use the PK to > remove the row from the local table. > > That might all seem unnecessarily complicated, and I don't know what > happens if the reply doesn't get back, if indeed that can ever happen? I > imagine that the delivery of the reply is guaranteed just like the > outgoing message. for some reason not possible to deliver the message on the other end. Everytime your activation procedure retrieves and processes the message there is an error (supposedly because of a bug in your code). Eventually the queue is turned off, and someone decides to receive the posion message into thin air to make the queue start running. > Is a reply actually necessary? The books and blogs I have read all show My initial thinking was that a reply would not be needed, and certainly> the receiver sending a reply, but perhaps that is redundant if all > messages are guaranteed? Obviously, for my modified scheme to work, I > would need the reply, but then again if I go with your original > suggestion perhaps I don't need a reply, and that would make things run > more quickly? skipping replies would be better for performance. But it would also mean an increase in risk for data being dropped entirely. But assuming that your activation routine would be very simple, the risk for a bug occuring in production may be low, and thus you may prefer to ignore it. -- Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se Links for SQL Server Books Online: SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx In this particular case, there will be no constraints on the receiving table
that will stop the message form being stored. The only reason I can think of, therefore, why the message could not be saved is disk full, which would be rather fatal. There would be other alarm bells going off if that happened, so perhaps I can ditch the reply. Thanks Charles Show quoteHide quote "Erland Sommarskog" <esq***@sommarskog.se> wrote in message news:Xns9C39EF6292AD4Yazorman@127.0.0.1... > Charles (bl***@nowhere.com) writes: >> A thought occurs to me. I might quite like to keep the data locally, in >> a 'visible' form, until I know it has been transferred successfully to >> the remote table. I'm thinking that my existing sproc could continue to >> write the data to a local table, and also put it into the queue. Then, >> when the remote end has received the message and put the data into the >> remote table, it sends a reply message containing the PK of the data it >> was sent. When the reply gets back to the sender it can use the PK to >> remove the row from the local table. >> >> That might all seem unnecessarily complicated, and I don't know what >> happens if the reply doesn't get back, if indeed that can ever happen? I >> imagine that the delivery of the reply is guaranteed just like the >> outgoing message. > > Well, there is one reason the you would never get a reply back: it is > for some reason not possible to deliver the message on the other end. > Everytime your activation procedure retrieves and processes the message > there is an error (supposedly because of a bug in your code). Eventually > the queue is turned off, and someone decides to receive the posion message > into thin air to make the queue start running. > >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? > > My initial thinking was that a reply would not be needed, and certainly > skipping replies would be better for performance. But it would also mean > an increase in risk for data being dropped entirely. > > But assuming that your activation routine would be very simple, the > risk for a bug occuring in production may be low, and thus you may > prefer to ignore it. > > > -- > Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se > > Links for SQL Server Books Online: > SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx > SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx > SQL 2000: > http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx > > Is a reply actually necessary? The books and blogs I have read all show You must never "fire and forget" with Service Broker in order to avoid > the receiver sending a reply, but perhaps that is redundant if all > messages are guaranteed? Obviously, for my modified scheme to work, I > would need the reply, but then again if I go with your original suggestion > perhaps I don't need a reply, and that would make things run more quickly? leaking conversation handles. When the target ends the conversation, an EndDialog (or Error) message is send back to the initiator. The initiator needs to issue an END CONVERSATION in response to clean things up. A common pattern is to use an initiator queue activated proc for this purpose. The initiator queue activated proc just issues an END CONVERSATION in response to messages and optionally logs unexpected messages (e.g. Error) to an error table for troubleshooting and to facilitate monitoring. Show quoteHide quote "Charles" <bl***@nowhere.com> wrote in message news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... > Hi Erland > > A thought occurs to me. I might quite like to keep the data locally, in a > 'visible' form, until I know it has been transferred successfully to the > remote table. I'm thinking that my existing sproc could continue to write > the data to a local table, and also put it into the queue. Then, when the > remote end has received the message and put the data into the remote > table, it sends a reply message containing the PK of the data it was sent. > When the reply gets back to the sender it can use the PK to remove the row > from the local table. > > That might all seem unnecessarily complicated, and I don't know what > happens if the reply doesn't get back, if indeed that can ever happen? I > imagine that the delivery of the reply is guaranteed just like the > outgoing message. > > Is a reply actually necessary? The books and blogs I have read all show > the receiver sending a reply, but perhaps that is redundant if all > messages are guaranteed? Obviously, for my modified scheme to work, I > would need the reply, but then again if I go with your original suggestion > perhaps I don't need a reply, and that would make things run more quickly? > > Charles > > > "Erland Sommarskog" <esq***@sommarskog.se> wrote in message > news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >> Charles (bl***@nowhere.com) writes: >>> I have an application that puts data into a table in a remote database >>> instance. The problem is that when the database or network is down the >>> application has nowhere to put the data. To overcome this, I have >>> decided to save the data to a local instance and use Service Broker to >>> move the data from the local table to the remote table. >>> >>> I have the mechanism and understanding now to create the SB objects on >>> the >>> remote instance to receive the messages, but I'm not sure how to get the >>> data into the local queue. >>> >>> What I am looking for is a timely method to remove rows from the local >>> table and put them into the queue, when SB will take over. >>> >>> Can anyone suggest a method of doing this? What I imagine is some SQL >>> Server process that waits for a row to be inserted into the local table >>> and then moves it to the queue. It would then go back and wait for the >>> next row, and so on. If this were in a sproc, I don't know how it would >>> be activated, and the fact that it would run forever might be a problem >>> too. >> >> To me it sounds that you have just described a Service Broker queue. Why >> save it to a table, and then queue it with Service Broker? Why not just >> send it? Then Service Broker will store it its internal tables, until it >> is delivered. >> >> >> -- >> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >> >> Links for SQL Server Books Online: >> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >> SQL 2000: >> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >> > > Hi Dan
Thanks for the reply. So, I need a reply to allow a conversation to be ended at both ends, but what if the conversation never ends? In my scenario there will be a continuous stream of messages from the initiator to the target, forever; or at least until there is an exceptional situation, like the database is taken off-line or it fails over or something like that. It seems to me that the conversation shouldn't end in normal operation. Or, is a conversation a one-message-only dialog? I had thought that the stream of messages would all be part of the same conversation, or have I got that wrong? Charles Show quoteHide quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:%23Q4LnRk%23JHA.1252@TK2MSFTNGP04.phx.gbl... >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? > > You must never "fire and forget" with Service Broker in order to avoid > leaking conversation handles. When the target ends the conversation, an > EndDialog (or Error) message is send back to the initiator. The initiator > needs to issue an END CONVERSATION in response to clean things up. > > A common pattern is to use an initiator queue activated proc for this > purpose. The initiator queue activated proc just issues an END > CONVERSATION in response to messages and optionally logs unexpected > messages (e.g. Error) to an error table for troubleshooting and to > facilitate monitoring. > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "Charles" <bl***@nowhere.com> wrote in message > news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >> Hi Erland >> >> A thought occurs to me. I might quite like to keep the data locally, in a >> 'visible' form, until I know it has been transferred successfully to the >> remote table. I'm thinking that my existing sproc could continue to write >> the data to a local table, and also put it into the queue. Then, when the >> remote end has received the message and put the data into the remote >> table, it sends a reply message containing the PK of the data it was >> sent. When the reply gets back to the sender it can use the PK to remove >> the row from the local table. >> >> That might all seem unnecessarily complicated, and I don't know what >> happens if the reply doesn't get back, if indeed that can ever happen? I >> imagine that the delivery of the reply is guaranteed just like the >> outgoing message. >> >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? >> >> Charles >> >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>> Charles (bl***@nowhere.com) writes: >>>> I have an application that puts data into a table in a remote database >>>> instance. The problem is that when the database or network is down the >>>> application has nowhere to put the data. To overcome this, I have >>>> decided to save the data to a local instance and use Service Broker to >>>> move the data from the local table to the remote table. >>>> >>>> I have the mechanism and understanding now to create the SB objects on >>>> the >>>> remote instance to receive the messages, but I'm not sure how to get >>>> the >>>> data into the local queue. >>>> >>>> What I am looking for is a timely method to remove rows from the local >>>> table and put them into the queue, when SB will take over. >>>> >>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>> Server process that waits for a row to be inserted into the local table >>>> and then moves it to the queue. It would then go back and wait for the >>>> next row, and so on. If this were in a sproc, I don't know how it would >>>> be activated, and the fact that it would run forever might be a problem >>>> too. >>> >>> To me it sounds that you have just described a Service Broker queue. Why >>> save it to a table, and then queue it with Service Broker? Why not just >>> send it? Then Service Broker will store it its internal tables, until it >>> is delivered. >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Links for SQL Server Books Online: >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>> SQL 2000: >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >> >> > It's possible to have a 1-message conversation, followed by END DIALOG. You
keep the conversation open only as long as necessary. For example, if you called home one a day, each of those is a separate conversation and there is no need to keep the phone off the hook. -- Tom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau "Charles" <bl***@nowhere.com> wrote in message Thanks for the reply. So, I need a reply to allow a conversation to be endednews:ezhx2%23n%23JHA.1492@TK2MSFTNGP03.phx.gbl... Hi Dan at both ends, but what if the conversation never ends? In my scenario there will be a continuous stream of messages from the initiator to the target, forever; or at least until there is an exceptional situation, like the database is taken off-line or it fails over or something like that. It seems to me that the conversation shouldn't end in normal operation. Or, is a conversation a one-message-only dialog? I had thought that the stream of messages would all be part of the same conversation, or have I got that wrong? Charles Show quoteHide quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:%23Q4LnRk%23JHA.1252@TK2MSFTNGP04.phx.gbl... >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? > > You must never "fire and forget" with Service Broker in order to avoid > leaking conversation handles. When the target ends the conversation, an > EndDialog (or Error) message is send back to the initiator. The initiator > needs to issue an END CONVERSATION in response to clean things up. > > A common pattern is to use an initiator queue activated proc for this > purpose. The initiator queue activated proc just issues an END > CONVERSATION in response to messages and optionally logs unexpected > messages (e.g. Error) to an error table for troubleshooting and to > facilitate monitoring. > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ > > "Charles" <bl***@nowhere.com> wrote in message > news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >> Hi Erland >> >> A thought occurs to me. I might quite like to keep the data locally, in a >> 'visible' form, until I know it has been transferred successfully to the >> remote table. I'm thinking that my existing sproc could continue to write >> the data to a local table, and also put it into the queue. Then, when the >> remote end has received the message and put the data into the remote >> table, it sends a reply message containing the PK of the data it was >> sent. When the reply gets back to the sender it can use the PK to remove >> the row from the local table. >> >> That might all seem unnecessarily complicated, and I don't know what >> happens if the reply doesn't get back, if indeed that can ever happen? I >> imagine that the delivery of the reply is guaranteed just like the >> outgoing message. >> >> Is a reply actually necessary? The books and blogs I have read all show >> the receiver sending a reply, but perhaps that is redundant if all >> messages are guaranteed? Obviously, for my modified scheme to work, I >> would need the reply, but then again if I go with your original >> suggestion perhaps I don't need a reply, and that would make things run >> more quickly? >> >> Charles >> >> >> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>> Charles (bl***@nowhere.com) writes: >>>> I have an application that puts data into a table in a remote database >>>> instance. The problem is that when the database or network is down the >>>> application has nowhere to put the data. To overcome this, I have >>>> decided to save the data to a local instance and use Service Broker to >>>> move the data from the local table to the remote table. >>>> >>>> I have the mechanism and understanding now to create the SB objects on >>>> the >>>> remote instance to receive the messages, but I'm not sure how to get >>>> the >>>> data into the local queue. >>>> >>>> What I am looking for is a timely method to remove rows from the local >>>> table and put them into the queue, when SB will take over. >>>> >>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>> Server process that waits for a row to be inserted into the local table >>>> and then moves it to the queue. It would then go back and wait for the >>>> next row, and so on. If this were in a sproc, I don't know how it would >>>> be activated, and the fact that it would run forever might be a problem >>>> too. >>> >>> To me it sounds that you have just described a Service Broker queue. Why >>> save it to a table, and then queue it with Service Broker? Why not just >>> send it? Then Service Broker will store it its internal tables, until it >>> is delivered. >>> >>> >>> -- >>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>> >>> Links for SQL Server Books Online: >>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>> SQL 2000: >>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>> >> >> > Hi Tom
Each message is distinct and stands alone. They are all on the same topic, and relate to the same thing, as each is a real-time update of information previously sent, and they can come at a rate of 10 per second, forever. I suppose I am concerned that by striking up a new conversation 10 times per second it will affect throughput. At that rate of messaging, what would you say is the appropriate strategy? Charles Show quoteHide quote "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message news:Of8JGQo%23JHA.5092@TK2MSFTNGP03.phx.gbl... > It's possible to have a 1-message conversation, followed by END DIALOG. > You > keep the conversation open only as long as necessary. For example, if you > called home one a day, each of those is a separate conversation and there > is > no need to keep the phone off the hook. > > -- > Tom > > ---------------------------------------------------- > Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS > SQL Server MVP > Toronto, ON Canada > https://mvp.support.microsoft.com/profile/Tom.Moreau > > > "Charles" <bl***@nowhere.com> wrote in message > news:ezhx2%23n%23JHA.1492@TK2MSFTNGP03.phx.gbl... > Hi Dan > > Thanks for the reply. So, I need a reply to allow a conversation to be > ended > at both ends, but what if the conversation never ends? In my scenario > there > will be a continuous stream of messages from the initiator to the target, > forever; or at least until there is an exceptional situation, like the > database is taken off-line or it fails over or something like that. It > seems > to me that the conversation shouldn't end in normal operation. Or, is a > conversation a one-message-only dialog? I had thought that the stream of > messages would all be part of the same conversation, or have I got that > wrong? > > Charles > > > "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message > news:%23Q4LnRk%23JHA.1252@TK2MSFTNGP04.phx.gbl... >>> Is a reply actually necessary? The books and blogs I have read all show >>> the receiver sending a reply, but perhaps that is redundant if all >>> messages are guaranteed? Obviously, for my modified scheme to work, I >>> would need the reply, but then again if I go with your original >>> suggestion perhaps I don't need a reply, and that would make things run >>> more quickly? >> >> You must never "fire and forget" with Service Broker in order to avoid >> leaking conversation handles. When the target ends the conversation, an >> EndDialog (or Error) message is send back to the initiator. The >> initiator >> needs to issue an END CONVERSATION in response to clean things up. >> >> A common pattern is to use an initiator queue activated proc for this >> purpose. The initiator queue activated proc just issues an END >> CONVERSATION in response to messages and optionally logs unexpected >> messages (e.g. Error) to an error table for troubleshooting and to >> facilitate monitoring. >> >> >> -- >> Hope this helps. >> >> Dan Guzman >> SQL Server MVP >> http://weblogs.sqlteam.com/dang/ >> >> "Charles" <bl***@nowhere.com> wrote in message >> news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >>> Hi Erland >>> >>> A thought occurs to me. I might quite like to keep the data locally, in >>> a >>> 'visible' form, until I know it has been transferred successfully to the >>> remote table. I'm thinking that my existing sproc could continue to >>> write >>> the data to a local table, and also put it into the queue. Then, when >>> the >>> remote end has received the message and put the data into the remote >>> table, it sends a reply message containing the PK of the data it was >>> sent. When the reply gets back to the sender it can use the PK to remove >>> the row from the local table. >>> >>> That might all seem unnecessarily complicated, and I don't know what >>> happens if the reply doesn't get back, if indeed that can ever happen? I >>> imagine that the delivery of the reply is guaranteed just like the >>> outgoing message. >>> >>> Is a reply actually necessary? The books and blogs I have read all show >>> the receiver sending a reply, but perhaps that is redundant if all >>> messages are guaranteed? Obviously, for my modified scheme to work, I >>> would need the reply, but then again if I go with your original >>> suggestion perhaps I don't need a reply, and that would make things run >>> more quickly? >>> >>> Charles >>> >>> >>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>>> Charles (bl***@nowhere.com) writes: >>>>> I have an application that puts data into a table in a remote database >>>>> instance. The problem is that when the database or network is down the >>>>> application has nowhere to put the data. To overcome this, I have >>>>> decided to save the data to a local instance and use Service Broker to >>>>> move the data from the local table to the remote table. >>>>> >>>>> I have the mechanism and understanding now to create the SB objects on >>>>> the >>>>> remote instance to receive the messages, but I'm not sure how to get >>>>> the >>>>> data into the local queue. >>>>> >>>>> What I am looking for is a timely method to remove rows from the local >>>>> table and put them into the queue, when SB will take over. >>>>> >>>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>>> Server process that waits for a row to be inserted into the local >>>>> table >>>>> and then moves it to the queue. It would then go back and wait for the >>>>> next row, and so on. If this were in a sproc, I don't know how it >>>>> would >>>>> be activated, and the fact that it would run forever might be a >>>>> problem >>>>> too. >>>> >>>> To me it sounds that you have just described a Service Broker queue. >>>> Why >>>> save it to a table, and then queue it with Service Broker? Why not just >>>> send it? Then Service Broker will store it its internal tables, until >>>> it >>>> is delivered. >>>> >>>> >>>> -- >>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>> >>>> Links for SQL Server Books Online: >>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>>> SQL 2000: >>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>> >>> >>> >> > > If you are sending a stream of messages from one database to another, the
best practice would be to open a conversation and use it forever. Conversations are persistent objects so unless some kind of error causes the conversation to terminate you can use the same one for months as long as you don't specify a lifetime. If you receive a conversation error message, just end the one you are using and open another one. Some people have created pools of conversations to handle high message volumes but one conversation should handle 10 messages a second with no problem. Opening a conversation and leaving it open doesn't violate the fire and forget rule. -- Show quoteHide quoteThis 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 "Charles" <bl***@nowhere.com> wrote in message news:uzefhip%23JHA.4560@TK2MSFTNGP05.phx.gbl... > Hi Tom > > Each message is distinct and stands alone. They are all on the same topic, > and relate to the same thing, as each is a real-time update of information > previously sent, and they can come at a rate of 10 per second, forever. > > I suppose I am concerned that by striking up a new conversation 10 times > per second it will affect throughput. > > At that rate of messaging, what would you say is the appropriate strategy? > > Charles > > > "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message > news:Of8JGQo%23JHA.5092@TK2MSFTNGP03.phx.gbl... >> It's possible to have a 1-message conversation, followed by END DIALOG. >> You >> keep the conversation open only as long as necessary. For example, if >> you >> called home one a day, each of those is a separate conversation and there >> is >> no need to keep the phone off the hook. >> >> -- >> Tom >> >> ---------------------------------------------------- >> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS >> SQL Server MVP >> Toronto, ON Canada >> https://mvp.support.microsoft.com/profile/Tom.Moreau >> >> >> "Charles" <bl***@nowhere.com> wrote in message >> news:ezhx2%23n%23JHA.1492@TK2MSFTNGP03.phx.gbl... >> Hi Dan >> >> Thanks for the reply. So, I need a reply to allow a conversation to be >> ended >> at both ends, but what if the conversation never ends? In my scenario >> there >> will be a continuous stream of messages from the initiator to the target, >> forever; or at least until there is an exceptional situation, like the >> database is taken off-line or it fails over or something like that. It >> seems >> to me that the conversation shouldn't end in normal operation. Or, is a >> conversation a one-message-only dialog? I had thought that the stream of >> messages would all be part of the same conversation, or have I got that >> wrong? >> >> Charles >> >> >> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >> news:%23Q4LnRk%23JHA.1252@TK2MSFTNGP04.phx.gbl... >>>> Is a reply actually necessary? The books and blogs I have read all show >>>> the receiver sending a reply, but perhaps that is redundant if all >>>> messages are guaranteed? Obviously, for my modified scheme to work, I >>>> would need the reply, but then again if I go with your original >>>> suggestion perhaps I don't need a reply, and that would make things run >>>> more quickly? >>> >>> You must never "fire and forget" with Service Broker in order to avoid >>> leaking conversation handles. When the target ends the conversation, an >>> EndDialog (or Error) message is send back to the initiator. The >>> initiator >>> needs to issue an END CONVERSATION in response to clean things up. >>> >>> A common pattern is to use an initiator queue activated proc for this >>> purpose. The initiator queue activated proc just issues an END >>> CONVERSATION in response to messages and optionally logs unexpected >>> messages (e.g. Error) to an error table for troubleshooting and to >>> facilitate monitoring. >>> >>> >>> -- >>> Hope this helps. >>> >>> Dan Guzman >>> SQL Server MVP >>> http://weblogs.sqlteam.com/dang/ >>> >>> "Charles" <bl***@nowhere.com> wrote in message >>> news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >>>> Hi Erland >>>> >>>> A thought occurs to me. I might quite like to keep the data locally, in >>>> a >>>> 'visible' form, until I know it has been transferred successfully to >>>> the >>>> remote table. I'm thinking that my existing sproc could continue to >>>> write >>>> the data to a local table, and also put it into the queue. Then, when >>>> the >>>> remote end has received the message and put the data into the remote >>>> table, it sends a reply message containing the PK of the data it was >>>> sent. When the reply gets back to the sender it can use the PK to >>>> remove >>>> the row from the local table. >>>> >>>> That might all seem unnecessarily complicated, and I don't know what >>>> happens if the reply doesn't get back, if indeed that can ever happen? >>>> I >>>> imagine that the delivery of the reply is guaranteed just like the >>>> outgoing message. >>>> >>>> Is a reply actually necessary? The books and blogs I have read all show >>>> the receiver sending a reply, but perhaps that is redundant if all >>>> messages are guaranteed? Obviously, for my modified scheme to work, I >>>> would need the reply, but then again if I go with your original >>>> suggestion perhaps I don't need a reply, and that would make things run >>>> more quickly? >>>> >>>> Charles >>>> >>>> >>>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>>> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>>>> Charles (bl***@nowhere.com) writes: >>>>>> I have an application that puts data into a table in a remote >>>>>> database >>>>>> instance. The problem is that when the database or network is down >>>>>> the >>>>>> application has nowhere to put the data. To overcome this, I have >>>>>> decided to save the data to a local instance and use Service Broker >>>>>> to >>>>>> move the data from the local table to the remote table. >>>>>> >>>>>> I have the mechanism and understanding now to create the SB objects >>>>>> on >>>>>> the >>>>>> remote instance to receive the messages, but I'm not sure how to get >>>>>> the >>>>>> data into the local queue. >>>>>> >>>>>> What I am looking for is a timely method to remove rows from the >>>>>> local >>>>>> table and put them into the queue, when SB will take over. >>>>>> >>>>>> Can anyone suggest a method of doing this? What I imagine is some SQL >>>>>> Server process that waits for a row to be inserted into the local >>>>>> table >>>>>> and then moves it to the queue. It would then go back and wait for >>>>>> the >>>>>> next row, and so on. If this were in a sproc, I don't know how it >>>>>> would >>>>>> be activated, and the fact that it would run forever might be a >>>>>> problem >>>>>> too. >>>>> >>>>> To me it sounds that you have just described a Service Broker queue. >>>>> Why >>>>> save it to a table, and then queue it with Service Broker? Why not >>>>> just >>>>> send it? Then Service Broker will store it its internal tables, until >>>>> it >>>>> is delivered. >>>>> >>>>> >>>>> -- >>>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>>> >>>>> Links for SQL Server Books Online: >>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>>>> SQL 2000: >>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>>> >>>> >>>> >>> >> >> > > Hi Roger
Thanks, that clears things up. Cheers Charles Show quoteHide quote "Roger Wolter[MSFT]" <rwol***@online.microsoft.com> wrote in message news:A0A9B573-1F92-49D3-AF66-D44ED62D3E15@microsoft.com... > If you are sending a stream of messages from one database to another, the > best practice would be to open a conversation and use it forever. > Conversations are persistent objects so unless some kind of error causes > the conversation to terminate you can use the same one for months as long > as you don't specify a lifetime. If you receive a conversation error > message, just end the one you are using and open another one. Some people > have created pools of conversations to handle high message volumes but one > conversation should handle 10 messages a second with no problem. > > Opening a conversation and leaving it open doesn't violate the fire and > forget rule. > > -- > This 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 > > "Charles" <bl***@nowhere.com> wrote in message > news:uzefhip%23JHA.4560@TK2MSFTNGP05.phx.gbl... >> Hi Tom >> >> Each message is distinct and stands alone. They are all on the same >> topic, and relate to the same thing, as each is a real-time update of >> information previously sent, and they can come at a rate of 10 per >> second, forever. >> >> I suppose I am concerned that by striking up a new conversation 10 times >> per second it will affect throughput. >> >> At that rate of messaging, what would you say is the appropriate >> strategy? >> >> Charles >> >> >> "Tom Moreau" <tom@dont.spam.me.cips.ca> wrote in message >> news:Of8JGQo%23JHA.5092@TK2MSFTNGP03.phx.gbl... >>> It's possible to have a 1-message conversation, followed by END DIALOG. >>> You >>> keep the conversation open only as long as necessary. For example, if >>> you >>> called home one a day, each of those is a separate conversation and >>> there is >>> no need to keep the phone off the hook. >>> >>> -- >>> Tom >>> >>> ---------------------------------------------------- >>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS >>> SQL Server MVP >>> Toronto, ON Canada >>> https://mvp.support.microsoft.com/profile/Tom.Moreau >>> >>> >>> "Charles" <bl***@nowhere.com> wrote in message >>> news:ezhx2%23n%23JHA.1492@TK2MSFTNGP03.phx.gbl... >>> Hi Dan >>> >>> Thanks for the reply. So, I need a reply to allow a conversation to be >>> ended >>> at both ends, but what if the conversation never ends? In my scenario >>> there >>> will be a continuous stream of messages from the initiator to the >>> target, >>> forever; or at least until there is an exceptional situation, like the >>> database is taken off-line or it fails over or something like that. It >>> seems >>> to me that the conversation shouldn't end in normal operation. Or, is a >>> conversation a one-message-only dialog? I had thought that the stream of >>> messages would all be part of the same conversation, or have I got that >>> wrong? >>> >>> Charles >>> >>> >>> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message >>> news:%23Q4LnRk%23JHA.1252@TK2MSFTNGP04.phx.gbl... >>>>> Is a reply actually necessary? The books and blogs I have read all >>>>> show >>>>> the receiver sending a reply, but perhaps that is redundant if all >>>>> messages are guaranteed? Obviously, for my modified scheme to work, I >>>>> would need the reply, but then again if I go with your original >>>>> suggestion perhaps I don't need a reply, and that would make things >>>>> run >>>>> more quickly? >>>> >>>> You must never "fire and forget" with Service Broker in order to avoid >>>> leaking conversation handles. When the target ends the conversation, >>>> an >>>> EndDialog (or Error) message is send back to the initiator. The >>>> initiator >>>> needs to issue an END CONVERSATION in response to clean things up. >>>> >>>> A common pattern is to use an initiator queue activated proc for this >>>> purpose. The initiator queue activated proc just issues an END >>>> CONVERSATION in response to messages and optionally logs unexpected >>>> messages (e.g. Error) to an error table for troubleshooting and to >>>> facilitate monitoring. >>>> >>>> >>>> -- >>>> Hope this helps. >>>> >>>> Dan Guzman >>>> SQL Server MVP >>>> http://weblogs.sqlteam.com/dang/ >>>> >>>> "Charles" <bl***@nowhere.com> wrote in message >>>> news:u1lw8lJ%23JHA.4944@TK2MSFTNGP02.phx.gbl... >>>>> Hi Erland >>>>> >>>>> A thought occurs to me. I might quite like to keep the data locally, >>>>> in a >>>>> 'visible' form, until I know it has been transferred successfully to >>>>> the >>>>> remote table. I'm thinking that my existing sproc could continue to >>>>> write >>>>> the data to a local table, and also put it into the queue. Then, when >>>>> the >>>>> remote end has received the message and put the data into the remote >>>>> table, it sends a reply message containing the PK of the data it was >>>>> sent. When the reply gets back to the sender it can use the PK to >>>>> remove >>>>> the row from the local table. >>>>> >>>>> That might all seem unnecessarily complicated, and I don't know what >>>>> happens if the reply doesn't get back, if indeed that can ever happen? >>>>> I >>>>> imagine that the delivery of the reply is guaranteed just like the >>>>> outgoing message. >>>>> >>>>> Is a reply actually necessary? The books and blogs I have read all >>>>> show >>>>> the receiver sending a reply, but perhaps that is redundant if all >>>>> messages are guaranteed? Obviously, for my modified scheme to work, I >>>>> would need the reply, but then again if I go with your original >>>>> suggestion perhaps I don't need a reply, and that would make things >>>>> run >>>>> more quickly? >>>>> >>>>> Charles >>>>> >>>>> >>>>> "Erland Sommarskog" <esq***@sommarskog.se> wrote in message >>>>> news:Xns9C38EEB5E3C46Yazorman@127.0.0.1... >>>>>> Charles (bl***@nowhere.com) writes: >>>>>>> I have an application that puts data into a table in a remote >>>>>>> database >>>>>>> instance. The problem is that when the database or network is down >>>>>>> the >>>>>>> application has nowhere to put the data. To overcome this, I have >>>>>>> decided to save the data to a local instance and use Service Broker >>>>>>> to >>>>>>> move the data from the local table to the remote table. >>>>>>> >>>>>>> I have the mechanism and understanding now to create the SB objects >>>>>>> on >>>>>>> the >>>>>>> remote instance to receive the messages, but I'm not sure how to get >>>>>>> the >>>>>>> data into the local queue. >>>>>>> >>>>>>> What I am looking for is a timely method to remove rows from the >>>>>>> local >>>>>>> table and put them into the queue, when SB will take over. >>>>>>> >>>>>>> Can anyone suggest a method of doing this? What I imagine is some >>>>>>> SQL >>>>>>> Server process that waits for a row to be inserted into the local >>>>>>> table >>>>>>> and then moves it to the queue. It would then go back and wait for >>>>>>> the >>>>>>> next row, and so on. If this were in a sproc, I don't know how it >>>>>>> would >>>>>>> be activated, and the fact that it would run forever might be a >>>>>>> problem >>>>>>> too. >>>>>> >>>>>> To me it sounds that you have just described a Service Broker queue. >>>>>> Why >>>>>> save it to a table, and then queue it with Service Broker? Why not >>>>>> just >>>>>> send it? Then Service Broker will store it its internal tables, until >>>>>> it >>>>>> is delivered. >>>>>> >>>>>> >>>>>> -- >>>>>> Erland Sommarskog, SQL Server MVP, esq***@sommarskog.se >>>>>> >>>>>> Links for SQL Server Books Online: >>>>>> SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx >>>>>> SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx >>>>>> SQL 2000: >>>>>> http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx >>>>>> >>>>> >>>>> >>>> >>> >>> >> >> > Hi, Charles.
> Thanks for the reply. So, I need a reply to allow a conversation to be I assumed each of your messages was on a different conversation and is why I > ended at both ends, but what if the conversation never ends? In my > scenario there will be a continuous stream of messages from the initiator > to the target, forever; or at least until there is an exceptional > situation, like the database is taken off-line or it fails over or > something like that. It seems to me that the conversation shouldn't end in > normal operation. Or, is a conversation a one-message-only dialog? I had > thought that the stream of messages would all be part of the same > conversation, or have I got that wrong? cautioned about making sure the both sides ended the conversation. As Roger mentioned, no problem keeping a single conversation open for long periods for your message stream. You'll still need a means to handle the errors and start a new conversation when needed. Hi Dan
Yes, it does help. Many thanks. Charles Show quoteHide quote "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message news:eAQ4Cr3%23JHA.3320@TK2MSFTNGP04.phx.gbl... > Hi, Charles. > >> Thanks for the reply. So, I need a reply to allow a conversation to be >> ended at both ends, but what if the conversation never ends? In my >> scenario there will be a continuous stream of messages from the initiator >> to the target, forever; or at least until there is an exceptional >> situation, like the database is taken off-line or it fails over or >> something like that. It seems to me that the conversation shouldn't end >> in normal operation. Or, is a conversation a one-message-only dialog? I >> had thought that the stream of messages would all be part of the same >> conversation, or have I got that wrong? > > I assumed each of your messages was on a different conversation and is why > I cautioned about making sure the both sides ended the conversation. As > Roger mentioned, no problem keeping a single conversation open for long > periods for your message stream. You'll still need a means to handle the > errors and start a new conversation when needed. > > > -- > Hope this helps. > > Dan Guzman > SQL Server MVP > http://weblogs.sqlteam.com/dang/ >  
Other interesting topics
|
|||||||||||||||||||||||
