Databases
StackOverflow2013
Postsdbo
POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
    primarykey
    data
    text
    <p>I am learning how to use the Service Broker of SQL Server 2008 R2. When following the tutorial <a href="http://msdn.microsoft.com/en-us/library/bb839495%28v=sql.105%29" rel="noreferrer">Completing a Conversation in a Single Database</a>. Following the <a href="http://msdn.microsoft.com/en-us/library/bb839488%28v=sql.105%29" rel="noreferrer">Lesson 1</a>, I have successfully created the message types, contract, the queues and services. Following the <a href="http://msdn.microsoft.com/en-us/library/bb839499%28v=sql.105%29" rel="noreferrer">Lesson 2</a>, I have probably sent the message. However, when trying to receive the message, I get the NULL for the <code>ReceivedRequestMsg</code> instead of the sent content.</p> <p>When looking at the <code>sys.transmission_queue</code>, the <code>transmission_status</code> for the message says:</p> <blockquote> <p>An exception occurred while enqueueing a message in the target queue. Error: 15517, State: 1. Cannot execute as the database principal because the principal "dbo" does not exist, this type of principal cannot be impersonated, or you do not have permission.</p> </blockquote> <p>I have installed SQL Server using the Windows login like <code>Mycomp\Petr</code>. I am using that login also for the lessons.</p> <p>Can you guess what is the problem? What should I check and or set to make it working?</p> <p><strong>Edited 2012/07/16:</strong> For helping to reproduce the problem, here is what I did. Can you reproduce the error if you follow the next steps? </p> <p>Firstly, I am using Windows 7 Enterprise SP1, and Microsoft SQL Server 2008 R2, Developer Edition, 64-bit (ver. 10.50.2500.0, Root Directory located at C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL_PRIKRYL05\MSSQL). </p> <ol> <li><p>Following the tutorial advice, I have downloaded the AdventureWorks2008R2_Data.mdf sample database, and copied it into C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQL_PRIKRYL05\MSSQL\DATA\AdventureWorks2008R2_Data.mdf</p></li> <li><p>The SQL Server Management Studio had to be launched "As Administrator" to be able to attach the data later. Then I connected the SQL Server.</p></li> <li><p>Right click on Databases, context menu Attach..., button Add..., pointed to AdventureWorks2008R2_Data.mdf + OK. Then selected the AdventureWorks2008R2_Log.ldf from the grid below (reported as Not found) and pressed the Remove... button. After pressing OK, the database was attached and the AdventureWorks2008R2_log.LDF was created automatically.</p></li> <li><p>The following queries were used for looking at "Service Broker enabled/disabled", and for enabling (the Service Broker was enabled successfully for the database):</p></li> </ol> <hr> <pre><code>USE master; GO SELECT name, is_broker_enabled FROM sys.databases; GO ALTER DATABASE AdventureWorks2008R2 SET ENABLE_BROKER WITH ROLLBACK IMMEDIATE; GO SELECT name, is_broker_enabled FROM sys.databases; GO </code></pre> <ul> <li>Then, following the tutorial, the queries below were executed to create the message types, the contract, the queues, and the services:</li> </ul> <hr> <pre><code>USE AdventureWorks2008R2; GO CREATE MESSAGE TYPE [//AWDB/1DBSample/RequestMessage] VALIDATION = WELL_FORMED_XML; CREATE MESSAGE TYPE [//AWDB/1DBSample/ReplyMessage] VALIDATION = WELL_FORMED_XML; GO CREATE CONTRACT [//AWDB/1DBSample/SampleContract] ([//AWDB/1DBSample/RequestMessage] SENT BY INITIATOR, [//AWDB/1DBSample/ReplyMessage] SENT BY TARGET ); GO CREATE QUEUE TargetQueue1DB; CREATE SERVICE [//AWDB/1DBSample/TargetService] ON QUEUE TargetQueue1DB ([//AWDB/1DBSample/SampleContract]); GO CREATE QUEUE InitiatorQueue1DB; CREATE SERVICE [//AWDB/1DBSample/InitiatorService] ON QUEUE InitiatorQueue1DB; GO </code></pre> <p>So far, so good. </p> <ul> <li>Then the following queries are used to look at the queues (now empty when used):</li> </ul> <hr> <pre><code>USE AdventureWorks2008R2; GO SELECT * FROM InitiatorQueue1DB WITH (NOLOCK); SELECT * FROM TargetQueue1DB WITH (NOLOCK); SELECT * FROM sys.transmission_queue; GO </code></pre> <ul> <li>The problem manifests when the message is sent:</li> </ul> <hr> <pre><code>BEGIN TRANSACTION; BEGIN DIALOG @InitDlgHandle FROM SERVICE [//AWDB/1DBSample/InitiatorService] TO SERVICE N'//AWDB/1DBSample/TargetService' ON CONTRACT [//AWDB/1DBSample/SampleContract] WITH ENCRYPTION = OFF; SELECT @RequestMsg = N'&lt;RequestMsg&gt;Message for Target service.&lt;/RequestMsg&gt;'; SEND ON CONVERSATION @InitDlgHandle MESSAGE TYPE [//AWDB/1DBSample/RequestMessage] (@RequestMsg); SELECT @RequestMsg AS SentRequestMsg; COMMIT TRANSACTION; GO </code></pre> <p>When looking at the queues, the <code>Initiator...</code> and the <code>Target...</code> queues are empty, and the sent message can be found in <code>sys.transmission_queue</code> with the above mentioned error reported via the <code>transmission_status</code>.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USpepr
    1. USpepr
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
      1. USpepr
      1. VTBountyStart
    2. VO
      singulars
      1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
      1. This table or related slice is empty.
      1. VTUpMod
    1. COThanks for making corrections ;)
      singulars
      1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
      1. USpepr
    2. CODid you check **[here](http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/)** ? I am using sa user to execute the **[sample script](http://blog.sqlauthority.com/2009/09/21/sql-server-intorduction-to-service-broker-and-sample-script/)**
      singulars
      1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
      1. This table or related slice is empty.
    3. CO@RGI: I did try similar starters and it worked. The problem is that I need to learn more and set it for the database that was not created by myself. The reason for the question is not that I do not understand the basics of the Service Broker. The reason is that I observed the error and I want to learn how to get rid of it.
      singulars
      1. POSQL Server 2008 Service Broker tutorial -- cannot receive the message (exception in transmission_status)
      1. USpepr
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload