April 20, 2010 at 2:32 pm
I'm trying to use the waitfor\receive statements in a dynamic sql statement within a stored procedure to update a logging table and update a table within the database using the Audit_Logout with the Service Broker. I need to be able to specifiy a different Queue in the waitfor statement that is dynamically generated depending which database the procedure is being run against by prepending the database name plus an underscore in front of the "LoggingQueue" name. In the below procedure where the waitfor statement is remarked out, when unremarked the procedure works fine but I have to hadrcode the queue name for each different queue on each database. When trying to do the same thing using dynamic sql the waitfor\receive does not retrieve the message from the queue and update the logging table. Also, when stepping through the code I get an error at the "exec sp_executesql @cmd" statement that the @message_type_name variable must be declared which it clearly has. Is it possible to do this with the waitfor\receive statements being used dynamically? Any help is greatly appreciated.
create procedure EventDisconnect with execute as 'dbo'
as
set nocount on;
declare
@message_bodyxml,
@message_type_namenvarchar(256),
@dialoguniqueidentifier;
while (1 = 1)
begin
declare @dbnamenvarchar(60)
declare @cmdnvarchar(256)
set @dbname = (select db_name() +'_LogQueue')
set @cmd = 'waitfor (receive top(1) @message_type_name=message_type_name, @message_body=message_body, @dialog = conversation_handle from '+@dbname+'), timeout 2000'
begin transaction;
exec sp_executesql @cmd
--waitfor (receive top(1) @message_type_name=message_type_name, @message_body=message_body, @dialog = conversation_handle from DatabaseName_LogQueue), timeout 2000
if (@@rowcount = 0)
begin
rollback transaction ;
break ;
end ;
if (@message_type_name = 'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog')
begin
end conversation @dialog ;
end ;
else
begin
insert into Logging (
EventTime,
EventType,
LoginName,
UserName,
HostName,
ServerName,
NTUserName,
NTDomainName,
Success,
FullLog )
values (
cast(cast(@message_body.query('/EVENT_INSTANCE/PostTime/text()') as varchar(64)) as datetime),
cast(@message_body.query('/EVENT_INSTANCE/EventType/text()') as varchar(100)),
cast(@message_body.query('/EVENT_INSTANCE/LoginName/text()') as varchar(100)),
cast(@message_body.query('/EVENT_INSTANCE/UserName/text()') as varchar(100)),
cast(@message_body.query('/EVENT_INSTANCE/HostName/text()') as varchar(100)),
cast(@message_body.query('/EVENT_INSTANCE/ServerName/text()') as varchar(100)),
cast(@message_body.query('/EVENT_INSTANCE/NTUserName/text()') as varchar(100)),
cast(@message_body.query('/EVENT_INSTANCE/NTDomainName/text()') as varchar(100)),
cast(cast(@message_body.query('/EVENT_INSTANCE/Success/text()') as varchar(64)) as integer), @message_body)
declare @connIDvarchar(32);
declare @SPIDint;
set @connID = cast(@message_body.query('/EVENT_INSTANCE/SPID/text()') as varchar(32));
set @SPID = convert(int,@connID);
if exists(select 1 from [dbo].[vci_User_Module] where [Connection_Id] = @SPID)
begin
update [dbo].[User_Module] set [Connection_Id] = null,
[Connect_Time] = null,
[Computer_Name] = null
where [Connection_Id] = @SPID
end
end
commit transaction
end
go
declare @dbnamenvarchar(60)
declare @cmdnvarchar(255)
set @dbname = (select db_name() +'_LogQueue')
set @cmd = '
alter queue '+@dbname+' with activation (status = on, procedure_name = Loggingproc,
max_queue_readers = 1, execute as self)'
exec sp_executesql @cmd
go
June 23, 2010 at 3:41 pm
You have to specify the parameters, their types and the output clause to your dynamic sql, like this:
declare @dbnamenvarchar(60);
declare @cmd nvarchar(max);
declare @message_type_name nvarchar(256);
declare @message_body varbinary(max);
declare @dialog uniqueidentifier;
set @dbname = (select db_name() +'_LogQueue');
set @cmd = '
waitfor (
receive top(1)
@mtn=message_type_name,
@mb=message_body,
@d = conversation_handle
from '+quotename(@dbname)+'
), timeout 2000';
exec sp_executesql
@cmd,
N'@mtn nvarchar(256) output, @mb varbinary(max) output, @d uniqueidentifier output',
@message_type_name output,
@message_body output,
@dialog output;
June 23, 2010 at 3:52 pm
And you'll probably want to include the line "set @nRows = @@rowcount;" into the dynamic sql too. Just add another output parameter ', @nRows int output' in the parameter list and add a local variable to receive the value to the list of parameters to sp_executesql and you'll retrieve the number of rows received into that variable after execution of the dynamic sql.
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply