Microsoft KB Archive/264682

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

Article ID: 264682

Article Last Modified on 10/16/2003

-

APPLIES TO


 * Microsoft SQL Server 6.5 Standard Edition
 * Microsoft SQL Server 7.0 Standard Edition

-



This article was previously published under Q264682



BUG #: 17749 (SQLBUG_65)

BUG #: 33934 (SQLBUG_70)



SYMPTOMS
You can use the OLE Automation system stored procedure sp_OAMethod with a SQL INSERT...EXEC statement to insert the results of the OLE object method called. However, if the records consist of 255 or more characters for a table column, the insert operation fails with the following OLE Automation error message:

HRESULT: 0x8004271d

Source: ODSOLE Extended Procedure

Description: Error in srv_sendrow.

If 254 characters or less are returned, the insert operation is successful.



WORKAROUND
The OLE object method should separate the character data string into chunks of 254 characters or less before returning the results.



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



MORE INFORMATION
To reproduce the problem, run the following sample code: set nocount on go

create table #DestTbl ( Str_val varchar(255) ) go

DECLARE @object    int DECLARE @oResultSet int DECLARE @hr        int

EXEC @hr = sp_OACreate 'SQLDMO.SQLServer', @object OUT IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

-- -- Connecting to local server, supply a valid LoginID and Password

EXEC @hr = sp_OAMethod @object,&quot;Connect&quot;, NULL, &quot;.&quot;, &quot;LoginID&quot;,&quot;Password&quot; IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

print '- Insert record with 255 characters - FAIL --' EXEC @hr = sp_OAMethod @object, &quot;ExecuteWithResults&quot;, @oResultSet OUTPUT, @Command=&quot;select replicate('f',255)&quot; IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

insert into #DestTbl EXEC @hr = sp_OAMethod @oResultSet, &quot;GetColumnString&quot;, NULL, 1, 1 IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

print '- Insert record with 254 characters - GOOD --' EXEC @hr = sp_OAMethod @object, &quot;ExecuteWithResults&quot;, @oResultSet OUTPUT, @Command=&quot;select replicate('g',254)&quot; IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

insert into #DestTbl EXEC @hr = sp_OAMethod @oResultSet, &quot;GetColumnString&quot;, NULL, 1, 1 IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

print 'Only 1 row should be inserted:' select * from #DestTbl go drop table #DestTbl go For additional information, click the article number below to view the article in the Microsoft Knowledge Base:

278448 BUG: sp_OASetProperty Truncates Varchar Types to 255 Characters

Additional query words: SQLOLE

Keywords: kbbug kbcodesnippet kbpending KB264682

-

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

© Microsoft Corporation. All rights reserved.