Message in Transmission Queue

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

HB - 02 Jan 2006 04:32 GMT

Environment: SQL 2005 9.0 RTM and Visual Studio 2005. Both are installed on
the same machine.

I am sending a message from a C# application to a Service Broker Queue. It
ends up in transamission queue with the following message in
transmission_status column.
"The target service name could not be found. Ensure that the service name is
specified correctly and/or routing information has been supplied."

I was able to do the above successfully with SQL Server June 2005 CTP and
Visual Studio 2005 build 2.0.50215 (CTP). Both the CTPs are installed on
the same machine. I still have this environment available and able to send
the message from C# to Service Broker queue even now.

On the computer running RTM software, I have verified that the target
service for Service Broker in question does exist using "select * from
sys.services"

I never created any routes on either computers. I am using Integrated
Security=SSPI for SQL connection from the C# app.

In order to dig a bit deeper, I set up the above experiment from SQL
Management Studio using the RTM versions. I am able to successfully put a
message on Service Broker queue of interest from Mgmt Studio.

With respect to my problem, what should I do to fix it?

Also, if I am sending the exact message from a C# on a remote machine to SQL
Server 2005 running on a different machine, what additional steps would be
necessary to achieve the above results?

Thanks in advance.

Reply to this Message

Roger Wolter[MSFT] - 02 Jan 2006 07:08 GMT

There are several possibilities. The most likely is that your program is
not using the same service name as the service. Remember that service names
use binary collation so they must match exactly. If it does in fact work
from management studio, compare the service name character by character with
your C# program. If that doesn't help, try using profiler with all the
service broker traces turned on to see if there's a background error you're
missing.

Signature

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

> Environment: SQL 2005 9.0 RTM and Visual Studio 2005. Both are installed
> on
[quoted text clipped - 31 lines]
>
> Thanks in advance.

Reply to this Message

HB - 02 Jan 2006 18:21 GMT

Roger,

Thanks for your reply. I did what you said. I ran the profiler and I also
checked the Service name. The results are still the same.

To go further with testing, I added a command to the C# app to retrieve Srvc
Brkr queue associated with the Service, and am able to do that successfully.
So, if the Service name I am using to talk to Service Broker TRULY does not
exist, then I should NOT be able to get the service_queue associated with
it; but I do get it.

I know I am missing something or not doing something right but not sure how
to resolve it.

Help appreciated.

> There are several possibilities. The most likely is that your program is
> not using the same service name as the service. Remember that service names
[quoted text clipped - 39 lines]
> >
> > Thanks in advance.

Reply to this Message

Niels Berglund - 02 Jan 2006 18:25 GMT

> I know I am missing something or not doing something right but not
> sure how to resolve it.

Can you please post the code (both the C# code as well as the DDL for the
broker objects).

Niels

Signature

**************************************************
* Niels Berglund
* http://staff.develop.com/nielsb
* nielsb@no-spam.develop.com
* "A First Look at SQL Server 2005 for Developers"
* http://www.awprofessional.com/title/0321180593
**************************************************

Reply to this Message

Roger Wolter[MSFT] - 02 Jan 2006 18:32 GMT

The point of using profiler was not to fix the problem but to get more
detail on the error. What did profiler say about the error? Are you
specifying a Broker Identifier in the Begin Dialog command?

Signature

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

> Roger,
>
[quoted text clipped - 75 lines]
>> >
>> > Thanks in advance.

Reply to this Message

Remus Rusanu [MSFT] - 02 Jan 2006 20:19 GMT

Check if you still have the autocreated local route in the database. W/o
this route, local services will not be found. This route is created
automatically when a database is created, but is an user route (not system)
and therefore it can be dropped. If someone drops this route, the behavior
would be like the one you describe.

Compare the output of SELECT * FROM SYS.ROUTES on the database where things
work with the one where things don't work.

Signature

This posting is provided "AS IS" with no warranties, and confers no rights.

HTH,
~ Remus Rusanu

SQL Service Broker
http://msdn2.microsoft.com/en-us/library/ms166043(en-US,SQL.90).aspx

> Roger,
>
[quoted text clipped - 75 lines]
>> >
>> > Thanks in advance.

Reply to this Message

HB - 03 Jan 2006 06:02 GMT

Roger, Niels, and Remus,

Thanks for your help.

I basically had to recreate the Srvc Brkr plumbing in SQL 2005 RTM. Now
things are working.
I copied the mdf and ldf files from SQL 2005 June CTP and attached them in
SQL 2005 RTM.

Remus, yes AutoCreatedLocal does exist. Roger, yes I understand profiler's
purpose was in problem diagnosis.

I still have one unanswered question:

If I am sending the exact message from a C# on a remote machine to SQL
Server 2005 running on a different machine, what additional steps would be
necessary in order to send the message to SQL Srvc Brkr queue?

Thanks again for all your help.

> Check if you still have the autocreated local route in the database. W/o
> this route, local services will not be found. This route is created
[quoted text clipped - 84 lines]
> >> >
> >> > Thanks in advance.

Reply to this Message