An error occurred while receiving data: ''64(error not found)''

We are using Service Broker to synchronise two databases using async triggers (using a middle server to preform data mapping processing). We are re-using dialogs as we want to ensure order when sending messages which contain the data to be synced. A dialog is created between each intiator and target service (there are 3 of each) which is kept open indefinately (we are only ending conversation upon receiving an end conversation message or error message).

We seem to get it working for a period then after about 1 hour it seems to stop sending and we see the following error in SQL Profiler:

An error occurred while receiving data: '64(error not found)'

Any ideas what could be causing this? We do not see any errors or end conversations. It seems to happen at irregular points.

Error 64 is usually a red herring. It simply indicates that connections are being closed due to inactivity (no traffic for 90 seconds).|||

We find however that when we next try to send across to the other server the message does not get delivered, instead we only see this error.

As a last resort we have had to send one message on one dialog, closing the dialog after it has been sent, and we have no issues now. However as the messages are being sent over seperate dialog SB cannot guarantee that it will arrive in the same order as it happened in the original database.

Below is the activation SP which resides on the middle server where I think the problem lies, although all three servers contain activation SPs that are fairly similar:

Create Procedure [ctk].[ActivateMartiniSendQueue]
AS

begin
set nocount on

declare
@.ConversationHandle uniqueidentifier,
@.DialogHandle uniqueidentifier,
@.MessageType varchar(128),
@.MessageBody xml,
@.QueuingOrder bigint,
@.Counter int,
@.Error int

declare @.tableMessage table
(
queuing_order bigint,
conversation_handle uniqueidentifier,
message_type_name sysname,
message_body xml
)

declare cursorMessage cursor forward_only read_only
for select
queuing_order,
conversation_handle,
message_type_name,
message_body
from @.tableMessage
order by queuing_order asc

begin try
while (1 = 1)
begin
begin transaction

waitfor
(
receive
queuing_order,
conversation_handle,
convert(varchar(128),message_type_name),
convert(xml,message_body)
from MartiniSendQueue
into @.tableMessage
), timeout 2000

if @.@.rowcount = 0 begin
if @.@.trancount > 0 rollback transaction
break
end

open cursorMessage

while (1 = 1)
begin
fetch next from cursorMessage into
@.QueuingOrder,
@.ConversationHandle,
@.MessageType,
@.MessageBody

if @.@.fetch_status <> 0 begin
break
end

if @.MessageType = 'Martini/Customer/Create'

Begin --1

Set @.counter = 0
while (1 = 1)
begin --2

select @.DialogHandle = [conversation_handle] from
sys.conversation_endpoints as ce
left join sys.services as s on s.service_id = ce.service_id
left join sys.service_contracts as sc on sc.service_contract_id = ce.service_contract_id
where is_initiator = 1 and s.name = 'MartiniSenderService' and far_service = 'MartiniReceptionService'

if @.DialogHandle is null

begin --3
begin dialog conversation @.DialogHandle
from service [MartiniSenderService]
to service 'MartiniReceptionService'
on contract MartiniContract
with encryption = off
end;--3e

set @.MessageType = 'Martini/Customer/Create';

send on conversation @.DialogHandle
message type @.MessageType (@.MessageBody)

set @.error = @.@.error
if @.error = 0 break
set @.counter = @.counter + 1

if @.counter > 10
begin --4
raiserror ('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @.error) with log
break
End --4e

End conversation @.DialogHandle
set @.DialogHandle = null

End --2e

End --1e

else if @.MessageType = 'Martini/Address/Create'

Begin --1
Set @.counter = 0
while (1 = 1)

begin --2

select @.DialogHandle = [conversation_handle] from
sys.conversation_endpoints as ce
left join sys.services as s on s.service_id = ce.service_id
left join sys.service_contracts as sc on sc.service_contract_id = ce.service_contract_id
where is_initiator = 1 and s.name = 'MartiniSenderService' and far_service = 'MartiniReceptionService'

if @.DialogHandle is null
begin --3
begin dialog conversation @.DialogHandle
from service [MartiniSenderService]
to service 'MartiniReceptionService'
on contract MartiniContract
with encryption = off
end; --3e

set @.MessageType = 'Martini/Address/Create';

send on conversation @.DialogHandle
message type @.MessageType (@.MessageBody)

set @.error = @.@.error
if @.error = 0 break
set @.counter = @.counter + 1

if @.counter > 10
begin --4
raiserror ('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @.error) with log
break
end --4e

end conversation @.DialogHandle
set @.DialogHandle = null

End --2e

End --1e

else if @.MessageType = 'Martini/Address/Update'

Begin

Set @.counter = 0
while (1 = 1)

begin

select @.DialogHandle = [conversation_handle] from
sys.conversation_endpoints as ce
left join sys.services as s on s.service_id = ce.service_id
left join sys.service_contracts as sc on sc.service_contract_id = ce.service_contract_id
where is_initiator = 1 and s.name = 'MartiniSenderService' and far_service = 'MartiniReceptionService'
if @.DialogHandle is null

begin
begin dialog conversation @.DialogHandle
from service [MartiniSenderService]
to service 'MartiniReceptionService'
on contract MartiniContract
with encryption = off

end;

set @.MessageType = 'Martini/Address/Update';

send on conversation @.DialogHandle
message type @.MessageType (@.MessageBody)

set @.error = @.@.error
if @.error = 0 break
set @.counter = @.counter + 1

if @.counter > 10
begin
raiserror ('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @.error) with log
break
end

end conversation @.DialogHandle
set @.DialogHandle = null

End

End

else if @.MessageType = 'Martini/Payment/Create'

Begin --1

Set @.counter = 0
while (1 = 1)

begin --2

select @.DialogHandle = [conversation_handle] from
sys.conversation_endpoints as ce
left join sys.services as s on s.service_id = ce.service_id
left join sys.service_contracts as sc on sc.service_contract_id = ce.service_contract_id
where is_initiator = 1 and s.name = 'MartiniSenderService' and far_service = 'MartiniReceptionService'

if @.DialogHandle is null
begin
begin dialog conversation @.DialogHandle
from service [MartiniSenderService]
to service 'MartiniReceptionService'
on contract MartiniContract
with encryption = off
end;

set @.MessageType = 'Martini/Payment/Create';

send on conversation @.DialogHandle
message type @.MessageType (@.MessageBody)

set @.error = @.@.error
if @.error = 0 break
set @.counter = @.counter + 1

if @.counter > 10
begin
raiserror ('Failed to SEND on a conversation for more than 10 times. Error %i.', 16, 1, @.error) with log
break
end

end conversation @.DialogHandle
set @.DialogHandle = null

End --2e

End --1e

if @.MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog' begin
end conversation @.DialogHandle
end

-- Error
if @.MessageType = 'http://schemas.microsoft.com/SQL/ServiceBroker/Error' begin
end conversation @.DialogHandle
end
end

close cursorMessage
delete from @.tableMessage

if @.@.trancount > 0 commit transaction
end

deallocate cursorMessage
end try
begin catch
if @.@.trancount > 0 rollback transaction
end catch
end
go

Any help would be much appreciated.

Regards

David