Can SQL Service Broker persist requests?

I have a scenario where we want something like a message bus. We can have multiple clients sending in events and would like a way to

i) persist those events

ii) Send out the notifications to the subcribers (this would be some C# services)

What is the best way to do this? I know that we can build something from scratch using WCF Publish-Subscribe. But i was interested in knowing if I can leverage SQL Service Broker to do the work.

When you create the Queue, you can set the RETENTION of the queue to ON so that messages are not removed from the queue once they have been processed.|||Thanks Tim! I'll give that a try.|||Great. Let me know if it solves your problem.
Tim|||

Tim,

The Retiontion=On does solve one part of the problem. However, I am trying to achieve a Publish-Subscribe mechanism. So far all the examples I have come across for Service Broker seem to be more of a 'Pull' mechanism rather than a 'Push'. Is it possible to implement a Push type messaging with Service Broker? How do I get started?

Thanks,

Meera

|||Have a look at https://blogs.msdn.com/remusrusanu/archive/2005/12/12/502942.aspx|||

Thanks Remus! I took a quick look at it. I need to tweak it a bit since i want my C# applications((instead of stored procs) to process the messages.

|||

Remus,

I've been looking at sample code, but cannot figure out how I can pass on the notifications to my C# client applications. Referrign to your publish-subscribe code, what I have is a case where once an article is published, my CLR code should get executed, instead of the stored procedure. Also, each of the subscribers will be CLR apps.

Can someone helpl me write this query to create a crosstab(piv

Hi Rob,
Originally I was sending the data and using a function in VB.Net to create
the pivot table, but I'm trying to have the work done on the SQL Server
instead of transferring all that data to the ASP.Net page. I think at the
moment that it's duplicating some of the work in the query but was wondering
if someone knew of a better way to write the query.
David
"Rob Farley" wrote:

> Presumably you're going to display your results somewhere, such as in
> ReportingServices, or a web page, etc... So why not add them up there inst
ead
> if you're worried?
> Personally, I'm not too keen on the PivotTable concept of SQL. I would
> rather return all the data, and then use something at the presentation lay
er
> to turn it into a PivotTable.
>Just to be sure, could you lay out the output that you are looking for'
"David Reynolds" wrote:
> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David
> "Rob Farley" wrote:
>|||There's no pretty way of returning a PivotTable directly from SQL. You can d
o
it through a large amount of temporary table population or creating an ugly
piece of 'dynamic' SQL. But honestly, it's much easier to do it once you've
got the data away from SQL.
If you group by all your columns and rows, so that you get only one record
back for each cell of your PivotTable, then you can easily handle that in
VB.Net. If you're worried about the amount of data passed back, you could
group by IDs instead of column/row names, and pass back separate datasets
which translate the IDs into the more human-readable form.
If you're really determined to create a stored procedure that will do it for
you, then I'm sure we can come up with something, but please, pick the
'simple' solution, which is to find a control that will display the
PivotTable for you.
Rob
"David Reynolds" wrote:

> Hi Rob,
> Originally I was sending the data and using a function in VB.Net to create
> the pivot table, but I'm trying to have the work done on the SQL Server
> instead of transferring all that data to the ASP.Net page. I think at the
> moment that it's duplicating some of the work in the query but was wonderi
ng
> if someone knew of a better way to write the query.
> David