BUG: You Cannot Set the Microsoft Message Queuing Body Property by Using the Sp_OASetProperty Stored Procedure (309002)

WORKAROUND

1. In a Transact-SQL context, create a DTS package (that is stored in SQL Server) that sends an Message Queuing message.
2. Create the following stored procedure that sets the Body property of the Message Queuing message, and that runs the package:

     
   CREATE PROCEDURE send_msmq_message
   @queuename varchar(30),
   @messagetext varchar(50)
   
   AS
   
   SET NOCOUNT ON
   
   DECLARE @packagename varchar(30)
   DECLARE @flag int
   SELECT @packagename = 'MSMQ_send'  --DTS package name goes here
   SELECT @flag = 256 --"integrated security"
   
   --object handles, misc variables
   DECLARE @debug_mode bit  --0=suppress debug msgs, 1=show msgs
   DECLARE @eventdesc char(30)
   DECLARE @resultcode int
   DECLARE @package_handle int
   DECLARE @task_handle int
   DECLARE @property_handle int
   DECLARE @customtask_handle int
   DECLARE @message_handle int
   DECLARE @verified_property varchar(255)
   
   DECLARE @steps_count int
   DECLARE @steps_handle int
   DECLARE @step_handle int
   DECLARE @step_result int
   DECLARE @result_sum int
   
   DECLARE @error_code int
   DECLARE @error_source varchar(8000)
   DECLARE @error_description varchar(8000)
   DECLARE @error_helpfile varchar(8000)
   DECLARE @error_helpcontext int
   DECLARE @error_interface_id varchar(8000)
   
   SELECT @debug_mode = 0
   
   --*************************************
   --establish a Package object
   --*************************************
   EXEC @resultcode = sp_OACreate 'DTS.Package2', @package_handle OUT
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'package object built.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle
    
   	END
   
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @package_handle
   	GOTO Destructor
   
   	END
   --*************************************
   --load the package definition from the server
   --*************************************
   
   EXEC @resultcode = sp_OAMethod @package_handle, 'LoadFromSqlServer', NULL, @ServerName=@@SERVERNAME, @PackageName=@packagename, @Flags=@flag
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'load package from SQL Server.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle
   	 
   	END
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @package_handle
   	GOTO Destructor
   
   	END
   
   --*************************************
   --isolate the first task in the job ("Main Task", a Message Queue Task)
   --*************************************
   
   EXEC @resultcode = sp_OAGetProperty @package_handle, 'Tasks(1)', @task_handle OUTPUT
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'isolating the individual task.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle, task_handle = @task_handle
   	END
   
   IF @resultcode <> 0
   		BEGIN
   		EXEC sp_OAGetErrorInfo @package_handle
   		GOTO Destructor
   
   		END
   
   IF @debug_mode = 1 
           BEGIN
   	EXEC @resultcode = sp_OAGetProperty @task_handle, 'Description', @verified_property OUT
   	SELECT 'result' = @resultcode, 'task desc' = @verified_property
   	END
   --*************************************
   --find the QueuePath property, and set it to the given queue name
   --*************************************
   
   EXEC @resultcode = sp_OAGetProperty @task_handle, 'Properties.Item("QueuePath")', @property_handle OUTPUT
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'isolating QueuePath property.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle, task_handle = @task_handle, property_handle = @property_handle
   	 
   	END
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @task_handle
   	GOTO Destructor
   
   	END
   
   EXEC @resultcode = sp_OASetProperty @property_handle, 'Value', @queuename
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'setting the QueuePath value.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle, task_handle = @task_handle, property_handle = @property_handle, 'queue name' = @queuename
   	END	
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @property_handle
   	GOTO Destructor
   
   	END
   IF @debug_mode = 1 
   	BEGIN
   	EXEC @resultcode = sp_OAGetProperty @property_handle, 'Value', @verified_property OUT
   	SELECT 'result' = @resultcode, 'verified' = @verified_property
   	END
   
   --*************************************
   --find the task's CustomTask property (this object holds the message itself)
   --*************************************
   
   EXEC @resultcode = sp_OAGetProperty @task_handle, 'CustomTask', @customtask_handle OUTPUT
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'isolating CustomTask property.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle, task_handle = @task_handle, customtask_handle = @customtask_handle
   	 
   	END
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @task_handle
   	GOTO Destructor
   
   	END
   
   --*************************************
   --find the Message object (1st item in the Messages collection), and set its MessageString to the given text
   --*************************************
   
   EXEC @resultcode = sp_OAGetProperty @customtask_handle, 'Messages(1)', @message_handle OUTPUT
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'isolating the message.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle, task_handle = @task_handle, customtask_handle = @customtask_handle, message_handle = @message_handle
   	 
   	END
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @package_handle
   	GOTO Destructor
   
   	END
   
   EXEC @resultcode = sp_OASetProperty @message_handle, 'MessageString', @messagetext
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'setting the MessageString.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle, task_handle = @task_handle, customtask_handle = @customtask_handle, message_handle = @message_handle, 'text' = @messagetext
   	END
   	
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @message_handle
   	GOTO Destructor
   
   	END
   
   IF @debug_mode = 1 
   	BEGIN
   	EXEC @resultcode = sp_OAGetProperty @message_handle, 'MessageString', @verified_property OUT
   	SELECT 'result' = @resultcode, 'verified' = @verified_property
   	END
   
   --*************************************
   --run the (now-customized) package
   --*************************************
   EXEC @resultcode = sp_OAMethod @package_handle, 'Execute'
   IF @debug_mode = 1 
   	BEGIN
   	SELECT @eventdesc = 'execute package.'
   	SELECT @eventdesc, result = @resultcode, package_handle = @package_handle
   	 
   	END
   
   IF @resultcode <> 0
   	BEGIN
   	EXEC sp_OAGetErrorInfo @package_handle
   	GOTO Destructor
   
   	END
   --*************************************
   --look for step-level errors
   --*************************************
   
   EXEC @resultcode = sp_OAGetProperty @package_handle, 'Steps', @steps_handle OUTPUT
   IF @resultcode <> 0
   	BEGIN
   	print 'Unable to get steps'
           EXEC sp_OAGetErrorInfo @steps_handle
   	GOTO Destructor
   
   	END
   
   EXEC @resultcode = sp_OAGetProperty @steps_handle, 'Count', @steps_count OUTPUT
   IF @resultcode <> 0
   	BEGIN
   	print 'Unable to get number of steps'
           EXEC sp_OAGetErrorInfo @steps_handle
   	GOTO Destructor
   
   	END
   IF @debug_mode = 1
   	SELECT '#-steps: ' = @steps_count
   
   SELECT @result_sum = 0 --initialize
   WHILE @steps_count > 0 
   	BEGIN 
   	EXEC @resultcode = sp_OAGetProperty @steps_handle, 'Item', @step_handle OUTPUT, @steps_count
   	IF @resultcode <> 0
   		BEGIN
   		print 'Unable to get step'
                   EXEC sp_OAGetErrorInfo @steps_handle
   		GOTO Destructor
   
   		END
   
   	EXEC @resultcode = sp_OAGetProperty @step_handle, 'ExecutionResult', @step_result OUTPUT
   	IF @resultcode <> 0
   		BEGIN
   		print 'Unable to get ExecutionResult'
                   EXEC sp_OAGetErrorInfo @step_handle
   		GOTO Destructor
   
   		END
   	IF @debug_mode = 1
   		SELECT 'Step Handle' = @step_handle, 'Step #' = @steps_count, 'Step Result' = @step_result
   	IF @step_result <> 0
   		BEGIN
   		SELECT @resultcode = 0 --initialize
   		EXEC @resultcode = sp_OAMethod @step_handle, 'GetExecutionErrorInfo', @error_code OUT, @error_source OUT, @error_description OUT, @error_helpfile OUT, @error_helpcontext OUT, @error_interface_id OUT
   		SELECT 'GetExecutionErrorInfo: result code' = @resultcode, 'Error Code' = @error_code, 'Error Source' = @error_source, 'Error Desc' = @error_description
                   EXEC sp_OAGetErrorInfo @step_handle
   		END
   
   	SELECT @steps_count = @steps_count - 1
   	SELECT @result_sum = @result_sum + @step_result
   	END
   
   IF @result_sum > 0
   	print 'Package had ' + CAST(@result_sum as varchar) + ' failed step(s)'
   ELSE 
   	print 'Package Succeeded'
   
   --*************************************
   --clean up
   --*************************************
   Destructor:
   
   EXEC @resultcode = sp_OADestroy @package_handle
   EXEC @resultcode = sp_OADestroy @task_handle
   EXEC @resultcode = sp_OADestroy @property_handle
   EXEC @resultcode = sp_OADestroy @customtask_handle
   EXEC @resultcode = sp_OADestroy @message_handle
   
   SET NOCOUNT OFF
   GO
   					

3. Run the stored procedure in Query Analyzer by supplying the queue name and the message body:
   

   EXEC send_msmq_message '.\Queue1', 'This is a test'

   
   Alternately, you can create a VBScript file that instantiates a MSMQ.MSMQMessage object and that sets the Body property directly.
   
   For example, you can create a file named Msmq.vbs by using the following code:
   

   if wscript.arguments.count < 1 then
     wscript.echo "Pass queue name as a command line param. Ex: "
     wscript.echo ""
     wscript.echo "  cscript msmq.vbs .\Queue1"
     wscript.quit(1)
   end if
   
   wscript.echo "Sending message to queue: " & wscript.arguments(0)
   set qinfo = createobject ("MSMQ.MSMQQueueInfo")
   qinfo.Pathname = wscript.arguments(0) '".\Queue1"
   set q = qinfo.open (2, 0)
   set msg = createobject ("MSMQ.MSMQMessage")
   msg.Priority = 1
   msg.label = "message label..."
   msg.body = "sent from MSMQMessage"
   msg.send q
   						

   In this example, the Message Queuing queue name is:

      .\Queue1
   						

   You can run this code by typing the following command from a command shell window:

   cscript msmq.vbs .\Queue1