I have been building out an application and Service Broker has been working great but I have this one nagging problem.
There are some scenarios that involve users waiting for a response so I call a stored procedure that sends a message on a service and then waits for a responses on the conversation handle on which it sent the request.
When I call the stored procedure from a query window inside management studio it works perfectly.
When I call it from a .NET application it times out waiting for the response. If I set a long timeout I can see that the response was returned and is sitting in the queue but the waitfor receive just sits there until it times out.
as soon as it times out if I execute a receive it succeeds and receives the message.
This seems to be a bug in the conversation locking mechanism which only happens if called from .NET. If my understanding is accurate the conversation lock should block others but not the session that created the conversation.< xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
What is really baffling is why it works in some scenarios and not in others.
Jim
Wait for Recieve Fails to recieve message when called from .Net sqlClient
opop
Waitfor command will wait forever however when you create a command in .NET you spearately set the command timeout.
Rob Reiss
Hi, even if I Specify a timeout I get the same error:
"Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding."
WAITFOR (RECEIVE CONVERT(NVARCHAR(max), message_body) AS message
FROM ReceiverQueue), TIMEOUT -1 (or a very big value)usna91
Thanks,
Rushi
jonbruce_ddt
I tried to setup a repro of your scenario using simpler scripts. These work on my machine. Could you test it on your system
use tempdb
go
create procedure requestreply
as
declare @dh uniqueidentifier;
begin transaction;
begin dialog @dh
from service s1
to service 's2';
send on conversation @dh (N'Request');
commit;
begin transaction
waitfor (
receive conversation_handle, message_type_name,
convert(nvarchar(max), message_body)
from q1 where conversation_handle = @dh), timeout 10000;
if (@@rowcount = 0)
begin
rollback;
return;
end
end conversation @dh
commit;
go
create procedure replyrequest
as
declare @dh uniqueidentifier;
declare @mt sysname;
while (1=1)
begin
begin transaction;
waitfor (
receive top(1) @dh=conversation_handle, @mt=message_type_name
from q2), timeout 10000;
if (@@rowcount = 0)
begin
commit;
break;
end
if (@mt = 'DEFAULT')
send on conversation @dh (N'Reply');
else
end conversation @dh;
commit
end
go
create queue q1;
create queue q2 with activation (
status = on, procedure_name = replyrequest, max_queue_readers=1, execute as self);
go
create service s1 on queue q1;
create service s2 on queue q2 ([DEFAULT]);
go
And the ADO.NET program used was:
using System;
using System.Data.SqlClient;
class Program
{
static void Main(string[] args)
{
SqlConnection conn = new SqlConnection("Server=xxx; Database=tempdb; Integrated Security=SSPI;");
conn.Open();
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "requestreply";
cmd.CommandType = System.Data.CommandType.StoredProcedure;
cmd.CommandTimeout = 0;
using (SqlDataReader reader = cmd.ExecuteReader())
{
while (reader.Read())
{
Console.WriteLine("{0}: {1}, {2}", reader.GetGuid(0), reader.GetString(1), reader.GetString(2));
}
}
Console.WriteLine("Press any key to continue...");
Console.ReadLine();
}
}
Sushil Sharma
We had this issue on another very similar service scenario once before and changing from ExecuteNonQuery to ExecuteReader even though in that case we did not need a result set fixed the problem now the problem has popped up again.
Also we are now on the Sept. CTP and the other time I saw this we were on Beta 2.
[/code]
allez
jwf
Thanks,
Rushi
jmargey
I have the same problem:
If I run a WAIT FOR on SQL Management Studio it waits indefinetly
IF I run the same via SQL Client I get timeout error.
How can I fix it How to give "RECEIVE" right to the user (but I'm connecting as dbo....)
SQL:
WAITFOR
(RECEIVE CONVERT(NVARCHAR(max), message_body) AS message FROM ReceiverQueue)Thanks for helping
Mystagogue
Rushi
emilylin56
I actually figured this out last night! I still think it is possibly a bug but it has nothing to do with .NET client.
We run our .NET apps on connections with least privilege and rely on Ownership chaining for access to the base objects. So the user only has execute permissions on the stored procedure and the owner of both the stored procedures and objects in the database is dbo so the procedures all get access to underlying objects through ownership chaining.
The funny thing is that ownership chaining seems to work for sending on a service but not for receiving from a queue. Worse then that is that it does not throw an error.
So if you take your stored procedure and run it with a user that has execute permissions on the procedure but does not have receive permissions on the queue it will send, enter the waitfor and sit there till it times out.
giving the user receive permission on the queue makes it work fine.
Is this by design
Why does ownership chaining work for send but not receive
Maybe there is still a small gap in my understanding
Thanks for your attention to this issue, sorry to lead you down the wrong path initially but I think we are near a full understanding of the issue now.
Jim
threna
spyork
For now we have a simple workaround which is to just explicitly grant recieve rights on the queue.
Jim
Andreas Johansson
Thank you,
Rushi
AJ Software
In terms of transactions, When called from .NET we do not create any transaction. If you begin a transaction and call the stored procedure then the service never recieves the request because until you commit the outer transaction it is not available to be recieved.
In this failure scenario the message is recieved by the service and a response is sent. Selecting from the respons queue shows the response message in the queue but trying to recieve the message while the procedure is waiting does not receive the message. Once the procedure times out then I can recieve the message from the queue.
Jim