Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

messages not appearing in service broker queue Expand / Collapse
Author
Message
Posted Monday, May 14, 2012 5:25 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1299471
Posted Monday, May 14, 2012 5:26 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1299472
Posted Monday, May 14, 2012 5:31 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:32 AM
Points: 1,890, Visits: 3,472
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.
Post #1299475
Posted Monday, May 14, 2012 5:42 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Friday, August 8, 2014 3:25 AM
Points: 2,283, Visits: 781
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
Post #1299479
Posted Monday, May 14, 2012 5:44 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: 2 days ago @ 6:32 AM
Points: 1,890, Visits: 3,472
Have I ever seen that one before?
Post #1299482
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse