Microsoft KB Archive/278448

= BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters =

Article ID: 278448

Article Last Modified on 10/16/2002

-

APPLIES TO


 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q278448



BUG #: 54472 (SQLBUG_70)



SYMPTOMS
If you use the sp_OAsetproperty property to pass varchar strings that are larger than 255 characters to a Microsoft Visual Basic COM DLL that accepts strings, the Odsole70.dll file truncates the string to 255 characters.



CAUSE
All sp_OA* extended stored procedures still use the old version of the srv_paraminfo function instead of the new srv_paraminfo function. Use of the old version of the srv_paraminfo function results in the truncation of the parameters (both in and out) to a maximum of 255 characters without any notification. One example is the sp_OAsetproperty in the Odsole70.dll file.



STATUS
Microsoft has confirmed this to be a problem in SQL Server 7.0.



Steps to Reproduce Behavior
 Create a new project on the C: drive and name the project &quot;SQLproj.vbp&quot;.  To add a class module with the name &quot;SQLrepro&quot; copy and paste the code that follows. Then, save this class to file with the name &quot;SQLrepro.cls&quot;. Option Explicit Public PropMessage As String

Public Property Let TestMessage(strTest As String) PropMessage = strTest Debug.Print PropMessage End Property Public Property Get TestMessage As String TestMessage = PropMessage End Property  To build the Visual Basic COM DLL file SQLproj.dll, from the File menu, click Make SQLproj.dll. Use Regsvr32.exe to register the SQLproj.dll file by typing the following at the command prompt:

regsvr32.exe C:\SQLproj.dll

  Copy and then run the following Transact-SQL script in the Microsoft SQL Server 7.0 Query Analyzer: -- Create stored procedure first CREATE PROCEDURE sp_Test_Dll AS DECLARE @object int DECLARE @hr int DECLARE @property varchar(3000) DECLARE @return varchar(1000)

-- Create an object. EXEC @hr = sp_OACreate 'SQLproj.SQLrepro', @object OUT

-- Call a method. EXEC @hr = sp_OASetProperty @object, &quot;TestMessage&quot;, '**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********0000000000**********123456789' EXEC @hr = sp_OAGetProperty @object, &quot;TestMessage&quot;, @property OUT -- It should only print first 255 characters. &quot;6789&quot; is truncated in this example Print @property

-- Destroy the object. EXEC @hr = sp_OADestroy @object Go -- execute stored procedure here sp_Test_Dll -- The truncated result has only 255 characters displayed. Number 6789 was truncated </li></ol>

For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

264682 BUG: INSERT .. EXEC sp_OAMethod Fails if 255 or More Characters are Returned

Additional query words: sp_OAsetproperty srv_paraminfo 255 string truncation ole automation

Keywords: kbbug kbpending KB278448

-

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

© Microsoft Corporation. All rights reserved.