May 14, 2012 at 5:25 am
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
May 14, 2012 at 5:26 am
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
May 14, 2012 at 5:31 am
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.
May 14, 2012 at 5:42 am
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
May 14, 2012 at 5:44 am
Have I ever seen that one before? 😀
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply