Need distributed service broker sample

I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.

thx!

Christopher Yager wrote:

I'm working with the April CTP of SQL Server and I'm trying to create a proof of concept using service broker. I'm struggling with the "abc's" of it. If anyone has or can point me to a distributed "Hello, World" for service broker between SQL Server 2005 and SQL Express instances it would save me some time and trouble.

I have uploaded a zip file containing two SQL projects with scripts for sending messages between two SQL Server 2005 instances. You probably have to create your own certificates, but at least the code is there how to catalogue them etc. You can get the file from here.
Niels

|||Thanks - I've downloaded the code and I'm about to jump back in. (I've spent a few days with failure and I'm ready for some success!)|||

I don't think this is Service Broker related but I can't create a master key in the master db of "instance 1" (using the sample code example). I get the error:

Msg 15466, Level 16, State 1, Line 1
An error occurred during decryption.

when executing the statement:

create master key encryption by password = 'Hello1234'

I'm logged in to the computer as domain administrator and using windows security to connect to SQL Server. I was having the same trouble before posting to this forum in the first place. I don't know what I did but I was able to create a key before. I've since deleted, re-created, and deleted. Now I can't seem to create it again.

any thoughts?

thx.

|||I found the error of my ways. I changed the logon account for the SQL Server service. That invalidated the Service Master Key apparently. After re-generating the Service Master Key (using the FORCE option) I was able to successfully create a master key.

Oh - the things we learn!|||OK - I've run through the sample. I am much more enlightened but still frustrated. I have everything set up - certificates created - routes set up, bindings created - but messages are not moving from instance1 to instance2. Messages are staying in the sys.transmission_queue on instance1.

I appreciate the assistance thus far.

My configuration is as follows:

VPC1 - running Win2K3(Active Directory), SQL2005 April CTP, VS2005, Team Foundation
VPC2 - running XP(domain member), SQL2005 Express April CTP, VS2005 Team Suite

Both VPC's running on host Win2K3 system. Networking and sharing between the systems is working fine. I'm able to connect from SQL MGMT Studio to the SQL Express instance without trouble.|||OK - sorry for not answering straight away; I'm out of the office this week, and my internet connection is not the best.
A couple of things:
1. In sys,transmission_queue, you should have a column saying why the message is not sent (I don't have a SSB machine up and running at the moment, so I don't remember the name of the column). See what the reason is.
2. Are endpoints enabled on both macines?
3. Is the database on Instance2 SSB enabled?
4. have you created a route on Instance2 for the service on Instance1?
5. Run SQL profiler on Instance2 and see if there are anything happening on that machine when you send a message from Instance1.
Niels
|||Thanks for all your help.

I saw a message in the event log about connect permission on the endpoint.

I granted connect on the service broker endpoint on the initiator (and target although that one was already done) to the remcert user and the messages just started flowing!

Great technology and very timely for my organization.

Thanks again.

For anyone else struggling - here are a few other tips:
The syntax has changed for the certificate related objects -DECRYPTION_PASSWORD change to DECRYPTION BY PASSWORD;
ENCRYPTION_PASSWORD change to ENCRYPTION BY PASSWORD;
PRIVATE_KEY change to PRIVATE KEY (no underscore);
there are a few others but I don't remember where I found the list I used.

If you change the login account for the SQL Server service, you'll need to regenerate the Service Master Key. All other keys and certs are based on this so be aware before changing the login for any of your services.|||You can actually save the service master key before changing the SQL Server service login account, using BACKUP SERVICE MASTER KEY..., and then restore it under the new login account, using RESTORE SERVICE MASTER KEY...|||Ni Niels,

the link u gave for the zip file download does not work anymore. if u can provide me the zip file with code that would definitely help me getting started with service broker.

Thanks in advance.|||

Kapil Aggarwal wrote:

the link u gave for the zip file download does not work anymore. if u can provide me the zip file with code that would definitely help me getting started with service broker.

Try this link, those scripts "should" have correct syntax etc. For updates etc, check my blog.
Niels|||If you do setup the broker endpoint successfully, it is time to take the BrokerChallenge:

http://blogs.msdn.com/rushidesai/archive/2005/06/15/429649.aspx

Thank you for participating,
Rushi|||Niels,

The two link
http://staff.develop.om/nielsb/code/routing2.zip
http://staff.develop.com/nielsb/code/routing-aprilctp.zip

still do not work (a connection with the server could not be established). I do not know if the server is down, or is the document no longer for pulic domain?

Appreciate your reply

Nebie connection problems with SQL Server 2005 Express and VS 2005

I downloaded SQL Server 2005 Express and some sample databases (incl. Adventureworks_data.mdf), but when I try to Add Connection in VS 2005 to point to this db and use the Test Connection button, it gives the error:

Microsoft Visual Studio

An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified)

I've tried setting the data source as either "Microsoft SQL Server Database File" or "Microsoft SQL Server", but get the same error.

Any tips on verifying SQL Srv Express is installed correctly? Any ideas on how to solve this problem?

Thanks in advance.

What happens if you set the source=(local)?

Navigation on Matrix Subtotal

Here's a sample matrix:

Men Women Total

Full Professor 36 12 48

Assoc. Professor 16 9 25

Assistant Professor 11 14 25

Total 63 35 98

Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).

Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?

Thanks in advance!

Daniel,

If your total link you should be passing the SubTtotal report field value - ReportItems!MySubtotal.value and not the Fields!MyMatrix.value.

I hope this helps.

Carl

|||

Thanks for your reply, Carl.

The problem is that the matrix only has one field on which to create the navigation... the data field. The total field is generated automatically, so I don't see how it's possible to creating a unique link for the total itself. If there is a way, please tell me how.

Thanks,
Dan

|||

Yes,

you can just past the "ReportItems!MyField.value" if you click on the "36" it passes "36" if you click on the "58" it passes "58"

Ham

|||

Thanks for your assistance.

But... I'm not sure we're connecting here. In Layout View, the matrix has one data field. I right click on that field and choose Properties. Then I click on the Navigation tab and in the "Jump to report" menu, I choose the name of my sub report. Then I click on Parameters and add the two parameters, =Fields!Gender.Value and =Fields!JobEEO.Value.

Now, when I Preview my report, every number is clickable, including the subtotals. It's just that clicking on the subtotals doesn't bring the correct result, as described in my first post.

I'm trying desperately to explain this so that you can see it, but I have a feeling I'm not doing a very good job...

|||

dj,

Go to where you add the two parameters in the navigation, In the parameter value, select expression, in your expression, you can type " ReportItems! " your intellisense will then allow your to select the Report Field you want to pass.

Ham

|||

The report field values are meaningless as parameters, though. What I really am saying when I click on the 63 is: "Show me a list of all Full Professors, Associate Professors, and Assistant Professors who are Male."

So the parameters I need to pass are

1) JobEEO = Full Professor, Associate Professor, Assistant Professor

2) Gender = Male

|||

Hi,

I think you need to use the InScope function to determine which parameter values to pass.

This link may help:

http://msdn2.microsoft.com/en-us/library/aa255807(sql.80).aspx

Ian.

|||

I have finally figured this out and I though I would pass it on to anyone who needs it in the future.

The best way to deal with this situation is

1) On the sub-report, for a multi-valued parameter, make every option the default. So in my example, the default for the Job EEO would be Full Professors, Associate Professors, and Assistant Professors and the default value for Gender would be both male and female.

2) On the first report, only pass the parameter if its value is in scope. This is accomplished by editing the Omit property of the parameter you are passing... setting it to something like: =Not(InScope("matrix1_Gender"))

This means that if there is no distinct value for the gender, the parameter will not be passed. The subreport will then show data for both genders because that is the subreport's default.

If making every option in a subreport's parameter the default doesnt' work for your needs, there is another way. On the parameter that's being passed out of the first report, you can edit the expression to something like this:

=Iif(InScope("matrix1_Gender"), Fields!Gender.Value, Split("Male,Female", ","))

This, translated into English, says, "If you clicked on a field related to a specific gender, then send that gender to the subreport. Otherwise, send both Male and Female to the subreport."

Navigation on Matrix Subtotal

Here's a sample matrix:

Men Women Total

Full Professor 36 12 48

Assoc. Professor 16 9 25

Assistant Professor 11 14 25

Total 63 35 98

Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).

Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?

Thanks in advance!

Daniel,

If your total link you should be passing the SubTtotal report field value - ReportItems!MySubtotal.value and not the Fields!MyMatrix.value.

I hope this helps.

Carl

|||

Thanks for your reply, Carl.

The problem is that the matrix only has one field on which to create the navigation... the data field. The total field is generated automatically, so I don't see how it's possible to creating a unique link for the total itself. If there is a way, please tell me how.

Thanks,
Dan

|||

Yes,

you can just past the "ReportItems!MyField.value" if you click on the "36" it passes "36" if you click on the "58" it passes "58"

Ham

|||

Thanks for your assistance.

But... I'm not sure we're connecting here. In Layout View, the matrix has one data field. I right click on that field and choose Properties. Then I click on the Navigation tab and in the "Jump to report" menu, I choose the name of my sub report. Then I click on Parameters and add the two parameters, =Fields!Gender.Value and =Fields!JobEEO.Value.

Now, when I Preview my report, every number is clickable, including the subtotals. It's just that clicking on the subtotals doesn't bring the correct result, as described in my first post.

I'm trying desperately to explain this so that you can see it, but I have a feeling I'm not doing a very good job...

|||

dj,

Go to where you add the two parameters in the navigation, In the parameter value, select expression, in your expression, you can type " ReportItems! " your intellisense will then allow your to select the Report Field you want to pass.

Ham

|||

The report field values are meaningless as parameters, though. What I really am saying when I click on the 63 is: "Show me a list of all Full Professors, Associate Professors, and Assistant Professors who are Male."

So the parameters I need to pass are

1) JobEEO = Full Professor, Associate Professor, Assistant Professor

2) Gender = Male

|||

Hi,

I think you need to use the InScope function to determine which parameter values to pass.

This link may help:

http://msdn2.microsoft.com/en-us/library/aa255807(sql.80).aspx

Ian.

|||

I have finally figured this out and I though I would pass it on to anyone who needs it in the future.

The best way to deal with this situation is

1) On the sub-report, for a multi-valued parameter, make every option the default. So in my example, the default for the Job EEO would be Full Professors, Associate Professors, and Assistant Professors and the default value for Gender would be both male and female.

2) On the first report, only pass the parameter if its value is in scope. This is accomplished by editing the Omit property of the parameter you are passing... setting it to something like: =Not(InScope("matrix1_Gender"))

This means that if there is no distinct value for the gender, the parameter will not be passed. The subreport will then show data for both genders because that is the subreport's default.

If making every option in a subreport's parameter the default doesnt' work for your needs, there is another way. On the parameter that's being passed out of the first report, you can edit the expression to something like this:

=Iif(InScope("matrix1_Gender"), Fields!Gender.Value, Split("Male,Female", ","))

This, translated into English, says, "If you clicked on a field related to a specific gender, then send that gender to the subreport. Otherwise, send both Male and Female to the subreport."

Navigation on Matrix Subtotal

Here's a sample matrix:

Men Women Total

Full Professor 36 12 48

Assoc. Professor 16 9 25

Assistant Professor 11 14 25

Total 63 35 98

Now, it's easy enough to make the values clickable so that somebody can drill down to a report that shows detail about the people. I have also discovered how to turn off clickability on the totals. However, what I really want is for the totals to be clickable so that, for example, if I click on the 63, I see a report that shows all men. Likewise, If I click on the 48, I want to see a report that shows all Full Professors. What currently happens when the totals are clickable is that if I click on the 63, I get all men who are full professors (36 records instead of 63). If I click on the 48, I get all Full Professors who are men. (36 records instead of 48).

Is there any way to send different parameters (or even no parameters) to the secondary report if the subtotals are clicked instead of the regular results?

Thanks in advance!

Daniel,

If your total link you should be passing the SubTtotal report field value - ReportItems!MySubtotal.value and not the Fields!MyMatrix.value.

I hope this helps.

Carl

|||

Thanks for your reply, Carl.

The problem is that the matrix only has one field on which to create the navigation... the data field. The total field is generated automatically, so I don't see how it's possible to creating a unique link for the total itself. If there is a way, please tell me how.

Thanks,
Dan

|||

Yes,

you can just past the "ReportItems!MyField.value" if you click on the "36" it passes "36" if you click on the "58" it passes "58"

Ham

|||

Thanks for your assistance.

But... I'm not sure we're connecting here. In Layout View, the matrix has one data field. I right click on that field and choose Properties. Then I click on the Navigation tab and in the "Jump to report" menu, I choose the name of my sub report. Then I click on Parameters and add the two parameters, =Fields!Gender.Value and =Fields!JobEEO.Value.

Now, when I Preview my report, every number is clickable, including the subtotals. It's just that clicking on the subtotals doesn't bring the correct result, as described in my first post.

I'm trying desperately to explain this so that you can see it, but I have a feeling I'm not doing a very good job...

|||

dj,

Go to where you add the two parameters in the navigation, In the parameter value, select expression, in your expression, you can type " ReportItems! " your intellisense will then allow your to select the Report Field you want to pass.

Ham

|||

The report field values are meaningless as parameters, though. What I really am saying when I click on the 63 is: "Show me a list of all Full Professors, Associate Professors, and Assistant Professors who are Male."

So the parameters I need to pass are

1) JobEEO = Full Professor, Associate Professor, Assistant Professor

2) Gender = Male

|||

Hi,

I think you need to use the InScope function to determine which parameter values to pass.

This link may help:

http://msdn2.microsoft.com/en-us/library/aa255807(sql.80).aspx

Ian.

|||

I have finally figured this out and I though I would pass it on to anyone who needs it in the future.

The best way to deal with this situation is

1) On the sub-report, for a multi-valued parameter, make every option the default. So in my example, the default for the Job EEO would be Full Professors, Associate Professors, and Assistant Professors and the default value for Gender would be both male and female.

2) On the first report, only pass the parameter if its value is in scope. This is accomplished by editing the Omit property of the parameter you are passing... setting it to something like: =Not(InScope("matrix1_Gender"))

This means that if there is no distinct value for the gender, the parameter will not be passed. The subreport will then show data for both genders because that is the subreport's default.

If making every option in a subreport's parameter the default doesnt' work for your needs, there is another way. On the parameter that's being passed out of the first report, you can edit the expression to something like this:

=Iif(InScope("matrix1_Gender"), Fields!Gender.Value, Split("Male,Female", ","))

This, translated into English, says, "If you clicked on a field related to a specific gender, then send that gender to the subreport. Otherwise, send both Male and Female to the subreport."

|||Thanks !
It works !