Submit My Question - Recent Questions - Recent Answer
Msdn Tech >> SQL Server >> SQL Service Broker

Transmission Queue problem

" noWrap width="1%" bgColor=#b71c0c>1. Yosi 29 Temmuz 12:19se?enekleri g?ster
G?nderen:"Yosi" <yus...@bizitek.com> -Bu yazar?n yazd??? mesajlar? bul
Tarih:Fri, 29 Jul 2005 02:19:00 -0700
Konu:Service Broker
Cevapla |Yazara Cevap Ver |?let |Yazd?r |Sadece Mesaj |Asl?n? g?ster |Kald?r |K?tüye Kullan?m? Bildir

I'm trying the service broker features of SQL Server 2005. I created a
queue, a writer service and a reader service. I'm inserting a message
to the queue by running the following command

declare @dh uniqueidentifier;
begin transaction;
begin dialog @dh
from service writer2
to service 'reader'
with encryption = off;
send on conversation @dh ('My first message');
end conversation @dh;
commit transaction;

After running the command, I query the queue by this query

select * from spool

I can not see the new message in the queue. Instead I see the message
in sys.transmission_queue. The "transmission_status" of the message is
"One or more messages could not be delivered to the local service
targeted by this dialog."

I also checked the sys.databases table and my database is service
broker enabled (is_broker_enabled=1)

In the event viewer there are errors with the following content

"An error occurred in the service broker message dispatcher, Error:
15404 State: 19."

What may be the problem ?

[6169 byte] By [Yosi345] at [2008-1-19]
«« Specifying todays date in Subscription Parameters
»» Hidden XML Tags in Word document
# 1
What account is SQL Server service running as?
It seems that the account that SQL Server service was installed as does not have the rights to query the domain's active directory. Typically this happens when installed as LOCAL SERVICE.

HTH,
~ Remus

RemusRusanu at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 2
The other common cause for this is running on a machine that is normally connected to a domain but currently is not connected - for example a laptop. SQL Server security is trying to authenticate the user and when it attempts to get the list of Windows groups from the domain controller, this fails. You can sometimes get around this by running as a SQL Server user but you will need to recreate the services as the SQL user.
Roger_MS at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 3

I was running on local service. I changed it to an administrator account but it didn't help. What's more, I have another queue, and I can write and read from this queue without any problem

Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 4
This is not my case. I'm working on a laptop but I'm connected to the network.
Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 5
In the database where the 'from' service is, can you run this statement:

EXECUTE AS USER = '<owner of from_service>';

E.g. if the 'from' service is owned by dbo (owner id in sys.services is 1) , then EXECUTE AS USER = 'dbo';

I expect this statement to return the same error, 15404, with more info in the error message.

Thanks,
~ Remus

RemusRusanu at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 6
Hi,

I've tried the "execute as" statement but it didn't return any error. The pricipal_id in sys.services table of the services are 1.

Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 7
Yosi345 wrote:

I was running on local service. I changed it to an administrator account but it didn't help. What's more, I have another queue, and I can write and read from this queue without any problem


Yosi
This is weird, a couple of things:
* What CTP are you running April or June?
* This service that doesn't work - is it in the same database as the initiator, if not is it in another db on the same machine or it it in another instance of SQL Server?
* And your laptop is part of a domain and you are logged in to the domain when this soesn't work?
* You say that you have another service/queue working - can you please email me the scripts you use to create all the objects involved here, both message types, contratcs, queues, services, begin conversation and send.
Can you please send me these for both the services that work as well for the services that doesn't work.
My email is nielsb at develop dot com
nielsb at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 8

* I'm running April CTP
* All the services and queues are in the same database.
* The laptop is part of the domain, and I'm logged into the domain.

GO
CREATE QUEUE [dbo].[spool] WITH STATUS = OFF , RETENTION = OFF ON [PRIMARY]
GO
CREATE QUEUE [dbo].[spool2] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY]
GO
CREATE SERVICE [reader] AUTHORIZATION [dbo] ON QUEUE [dbo].[spool] ([DEFAULT])
GO
CREATE SERVICE [writer] AUTHORIZATION [dbo] ON QUEUE [dbo].[spool] ([DEFAULT])
GO
CREATE SERVICE [reader3] AUTHORIZATION [dbo] ON QUEUE [dbo].[spool2] ([DEFAULT])
GO
CREATE SERVICE [writer3] AUTHORIZATION [dbo] ON QUEUE [dbo].[spool2] ([DEFAULT])

declare @dh uniqueidentifier;

begin transaction;

begin dialog @dh

from service writer

to service 'reader'

with encryption = off;

send on conversation @dh ('My first message');

commit transaction;
Queue "spool" does not work. Queue spool2 works

Thank you.

Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 9

You create the queue 'spool' disabled (STATUS = OFF).

A disabled quue cannot accept any message, therefore the messages are delyed in the transmission_queue. Once you enbale back the queue, the messages will de delivered.

RemusRusanu at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 10
Yosi345 wrote:

* I'm running April CTP
* All the services and queues are in the same database.
* The laptop is part of the domain, and I'm logged into the domain.

GO
CREATE QUEUE [dbo].[spool] WITH STATUS = OFF , RETENTION = OFF ON [PRIMARY]
GO
CREATE QUEUE [dbo].[spool2] WITH STATUS = ON , RETENTION = OFF ON [PRIMARY]
GO


Well, as Remus points out - if the script is accurate as per above then the spool queue has a status which is off. Change it to ON and hopefully things will start working. Let us know.
Niels
nielsb at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 11
It worked.
I didn't notice that the status was off.

Thank you for your help...

Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 12

Hi,

Now I have exactly this problem. My laptop is normally connected to a domain. When I work at home, not connected to the domain the service broker does not work. In sys.transmission_queue table the error is as follows:

An exception occurred while enqueueing a message in the target queue. Error: 15404, State: 19. Could not obtain information about Windows NT group/user 'BIZITEK\YUSUFK', error code 0x54b.

How can I solve this problem ?

Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 13

Change the user that owns the dialog to a SQL login instead of a windows login.

The most likely case is that the dialog is owned by 'dbo'. In this case, dbo is mapped to 'BIZITEK\YUSUFK' because this is the user that originally created the database. The solution is to change the 'dbo' to map to 'sa' for instance:

ALTER AUTHORIZATION ON DATABASE::[databasenamehere] TO [sa];

HTH,
~ Remus

RemusRusanu at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
# 14

I ran

sp_changedbowner 'sa'

, and it worked

Yosi345 at 2007-9-9 > top of Msdn Tech,SQL Server,SQL Service Broker...
SQL Server
SQL Service Broker

SQL Server Hot Topic

SQL Server New Topic

SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified