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


The information in this article applies to:

  • Microsoft SQL Server 6.5
  • Microsoft SQL Server 7.0
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,"Connect", NULL, ".", "LoginID","Password"
IF @hr <> 0 EXEC sp_OAGetErrorInfo @object

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

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

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

insert into #DestTbl EXEC @hr = sp_OAMethod @oResultSet, "GetColumnString", 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

Modification Type:MajorLast Reviewed:10/16/2003
Keywords:kbBug kbCodeSnippet kbpending KB264682
©2003 Microsoft Corporation. All rights reserved.