Microsoft KB Archive/309002

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

Article ID: 309002

Article Last Modified on 10/3/2003

-

APPLIES TO


 * Microsoft SQL Server 2000 Standard Edition

-



This article was previously published under Q309002



BUG #: 355102 (SHILOH_BUGS)



SYMPTOMS
When you instantiate a Microsoft Message Queuing object by SQL Server and the sp_OA automation stored procedures, you cannot assign the Body parameter a value.

Additionally, you receive the following error message:

NOTE: The script in the &quot;Steps to Reproduce the Problem&quot; section in this article generates this error message.

Operation           hr         Source      Description -- --- --- Set MSMQMessage.Body 0x80070057 MSMQMessage The parameter is incorrect.

(1 row(s) affected)



CAUSE
This problem occurs because the Message Queuing object rejects the parameter because it is not the correct type. The body property of the MSMQMessage object expects a data type of VT_BSTR, but SQL Server does not support passing this data type as a parameter.



WORKAROUND
To work around this problem, follow these steps:  In a Transact-SQL context, create a DTS package (that is stored in SQL Server) that sends an Message Queuing message.  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 --&quot;integrated security&quot;

--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 (&quot;Main Task&quot;, 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(&quot;QueuePath&quot;)', @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   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 &quot;Pass queue name as a command line param. Ex: &quot; wscript.echo &quot;&quot; wscript.echo &quot; cscript msmq.vbs .\Queue1&quot; wscript.quit(1) end if

wscript.echo &quot;Sending message to queue: &quot; & wscript.arguments(0) set qinfo = createobject (&quot;MSMQ.MSMQQueueInfo&quot;) qinfo.Pathname = wscript.arguments(0) '&quot;.\Queue1&quot; set q = qinfo.open (2, 0) set msg = createobject (&quot;MSMQ.MSMQMessage&quot;) msg.Priority = 1 msg.label = &quot;message label...&quot; msg.body = &quot;sent from MSMQMessage&quot; 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 



STATUS
Microsoft has confirmed that this is a problem in the Microsoft products that are listed at the beginning of this article.

<div class="moreinformation_section">

Steps to Reproduce the Problem
To demonstrate this problem, use the following script. This script creates a message queue and tries to set the property by using Transact-SQL: DECLARE @hr int DECLARE @MSMQQueueInfo int DECLARE @MSMQQueue int DECLARE @MSMQMessage int DECLARE @src varchar(255) DECLARE @desc varchar(255)

-- Create the MSMQQueueInfo object. EXEC @hr = sp_OACreate 'MSMQ.MSMQQueueInfo', @MSMQQueueInfo OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQQueueInfo, @src OUT, @desc OUT SELECT 'CreateObject MSMQQueueInfo' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END

-- Set MSMQQueueInfo.PathName to the name of the queue. EXEC @hr = sp_OASetProperty @MSMQQueueInfo, 'PathName', &quot;.\Queue1&quot; IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQQueueInfo, @src OUT, @desc OUT SELECT 'Set MSMQQueueInfo.PathName' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END

-- Open the queue to get ref to MSMQQueue. EXEC @hr = sp_OAMethod @MSMQQueueInfo, 'Open', @MSMQQueue OUTPUT, 2, 0 IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQQueueInfo, @src OUT, @desc OUT SELECT 'MSMQQueueInfo.Open' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END

EXEC @hr = sp_OACreate 'MSMQ.MSMQMessage', @MSMQMessage OUT IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQMessage, @src OUT, @desc OUT SELECT 'CreateObject MSMQMessage' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END

EXEC @hr = sp_OASetProperty @MSMQMessage, 'Priority', 1 IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQMessage, @src OUT, @desc OUT SELECT 'Set MSMQMessage.Priority' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END

EXEC @hr = sp_OASetProperty @MSMQMessage, 'Label', 'Sent from SQL server' IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQMessage, @src OUT, @desc OUT SELECT 'Set MSMQMessage.Label' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END

-- -- This block of code attempts to set the Body property -- DECLARE @body varchar (30) --DECLARE @body char(30) --DECLARE @body nvarchar(30) --DECLARE @body sql_variant SELECT @body = 'This is the message body' EXEC @hr = sp_OASetProperty @MSMQMessage, 'Body', @body IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQMessage, @src OUT, @desc OUT SELECT 'Set MSMQMessage.Body' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END -- -- --

EXEC @hr = sp_OAMethod @MSMQMessage,'Send', NULL, @MSMQQueue IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQMessage, @src OUT, @desc OUT SELECT 'MSMQMessage.Send' AS Operation, hr=convert(varbinary(4),@hr), Source=@src, Description=@desc GOTO Destructor

END

-- Destroy the object. Destructor:

EXEC @hr = sp_OADestroy @MSMQQueueInfo IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQQueueInfo, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END

EXEC @hr = sp_OADestroy @MSMQQueue IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQQueue, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END

EXEC @hr = sp_OADestroy @MSMQMessage IF @hr <> 0 BEGIN EXEC sp_OAGetErrorInfo @MSMQMessage, @src OUT, @desc OUT SELECT hr=convert(varbinary(4),@hr), Source=@src, Description=@desc RETURN END

Keywords: kbbug kbpending KB309002

-

[mailto:TECHNET@MICROSOFT.COM Send feedback to Microsoft]

© Microsoft Corporation. All rights reserved.