SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


messages not appearing in service broker queue


messages not appearing in service broker queue

Author
Message
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3085 Visits: 860
Hi guys

looking for the obvious think that i've overlooked here

i'm using service broker to parellelize a process where we import CSV files into a database.

i have a procedure that looks at a table with a list of files to process and then starts a conversation and puts a message on the queue for each file.

the problem is that even though my proc is sending the messages, nothing is appearing in either queue

I've enabled service broker and set trustworthy on - what am i missing ?

here is my sample code



if not exists(select top 1 id from dbo.ProcessFile)
begin
--drop out if there are no files to process
return
end


--set up the conversation
DECLARE @handle uniqueidentifier
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE FileProcessService
TO SERVICE 'FileProcessService_receive'
ON CONTRACT FileContract;

--start sending messages
declare @id bigint
declare @folder varchar(255)
declare @filename varchar(255)
declare @message xml

--loop through the records that are unprocessed
declare curs1 cursor FAST_FORWARD for
select ID,folder,filename from dbo.ProcessFile where status=0
open curs1
fetch next from curs1 into @id,@folder,@filename
while @@FETCH_STATUS=0
begin
--set the message
set @message='<message><file>'
--+@folder+'\'
--+@filename
+'X</file></message>'
print convert(varchar(max),@message)
--Sends a message
;SEND ON CONVERSATION @handle
MESSAGE TYPE ImportFile
(@message)
--mark it as sent to queue
update dbo.ProcessFile set status=1 where ID=@id
fetch next from curs1 into @id,@folder,@filename
end
close curs1
deallocate curs1
--And ends the conversation
END CONVERSATION @handle WITH CLEANUP;



here is the table it reads from (and some sample data)


CREATE TABLE [dbo].[ProcessFile](
[id] [bigint] IDENTITY(1,1) NOT NULL,
[folder] [varchar](255) NOT NULL,
[filename] [varchar](255) NOT NULL,
[status] [int] NOT NULL,
PRIMARY KEY CLUSTERED
(
[id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
insert into Processfile (folder,filename,status) select 'c:\files','text.csv',0)



MVDBA
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3085 Visits: 860
oh - and i also forgot to say i switched the message type to VALIDATION=NONE just in case it was badly formed XML, didn't fix the problem

MVDBA
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2453 Visits: 3575
Any messages that Service Broker is unable to deliver can be found is sys.transmission_queue. There is a column called transmission_status that will provide more details about why the message hasn't been delivered.
MVDBA
MVDBA
Hall of Fame
Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)Hall of Fame (3.1K reputation)

Group: General Forum Members
Points: 3085 Visits: 860
brillian - thanks -

"The session keys for this conversation could not be created or accessed. The database master key is required for this operation."

OOPS - forgot to create the database master key

MVDBA
Nils Gustav Stråbø
Nils Gustav Stråbø
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2453 Visits: 3575
Have I ever seen that one before? :-D
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search