SQL Server Error 'There are no rows in the current fetch buffer'

September 30th, 2011 - 07:11 am ET by ThomasD | Report spam
SQL Server Error 'There are no rows in the current fetch buffer'

We are storing/updateting binary data (~125kb) from a file into column
of type IMAGE in a SQL Server table
using C++ / OLE-DB with an Accessor like
CCommand<CAccessor<MyAccessor>> / ISequentialStream

Since many of years this works fine, but now we have a constellation
on one server where we get an error.
On this server some special data files produce this error by storing
it, many other not.
We took this data files to an other server, but there it works fine.
The problem seems to be on that server.

Here is the output of Profiler which shows that error. Curious are the
2 lines with the same aktion
SP:StmtStarting UPDATE [AttribImage] SET [value]=@Param000004
SP:StmtStarting UPDATE [AttribImage] SET [value]=@Param000004
Exception Error: 16931, Severity: 16, State: 2
User Error Message There are no rows in the current fetch buffer.

On the server where the test succeeded, there was only one update
statement (SP:StmtStarting) in Profiler output
SP:StmtStarting UPDATE [AttribImage] SET [value]=@Param000004
SP:StmtCompleted UPDATE [AttribImage] SET [value]=@Param000004


??? ANY IDEA ???

Server data:

Microsoft Windows XP Professional Service Pack 3 (Version 5.1.2600) 4
Processor Intel
DBMS:'9.00.4273.00 (SP3, Standard Edition (64-bit),
Latin1_General_CI_AS)'


Full Profiler output (error condition)
RPC:Starting declare @p1 int set @p1=NULL declare @p3 int set
@p3"9378 declare @p4 int set @p4)4916 declare @p5 int set
@p5=NULL
exec sp_cursoropen @p1 output,N'SELECT value FROM
AttribImage WHERE oid = 0x18477E AND aid = 230',@p3 output,@p4
output,@p5 output
select @p1, @p3, @p4, @p5
SP:StmtStarting SELECT value FROM AttribImage WHERE oid = 0x18477E
AND aid = 230
SP:StmtCompleted SELECT value FROM AttribImage WHERE oid = 0x18477E
AND aid = 230
RPC:Completed declare @p1 int set @p10150003 declare @p3 int
set @p3=2 declare @p4 int set @p4=4 declare @p5 int set @p5=-1
exec sp_cursoropen @p1 output,N'SELECT value FROM
AttribImage WHERE oid = 0x18477E AND aid = 230',@p3 output,@p4
output,@p5 output
select @p1, @p3, @p4, @p5
RPC Output Parameter 180150003
RPC Output Parameter 2
RPC Output Parameter 4
RPC Output Parameter -1
RPC:Starting exec sp_cursoroption 180150003,1,0
RPC:Completed exec sp_cursoroption 180150003,1,0
RPC:Starting exec sp_cursorfetch 180150003,32,1,1
SP:StmtStarting FETCH API_CURSOR0000000000014854
SP:StmtCompleted FETCH API_CURSOR0000000000014854
RPC:Completed exec sp_cursorfetch 180150003,32,1,1
RPC:Starting exec sp_cursoroption 180150003,3,1
RPC:Completed exec sp_cursoroption 180150003,3,1
RPC:Starting exec sp_cursor 180150003,40,1
SP:StmtStarting FETCH API_CURSOR0000000000014854
RPC:Completed exec sp_cursor 180150003,40,1
RPC:Starting exec sp_cursor
180150003,33,1,N'AttribImage',@value=0x62694C5A00740200789CECBB095C1357BFFF7FCE4C12C266084B10151340C0958828EE061070A
(124kb of data)
SP:StmtStarting UPDATE [AttribImage] SET [value]=@Param000004
SP:StmtStarting UPDATE [AttribImage] SET [value]=@Param000004
Exception Error: 16931, Severity: 16, State: 2
User Error Message There are no rows in the current fetch buffer.
User Error Message The statement has been terminated.
RPC:Completed exec sp_cursor
180150003,33,1,N'AttribImage',@value=0x62694C5A00740200789CECBB095C1357BFFF7FCE4C12C266084B10151365249673C67A2E8C7F4F...
(124kb of data)
RPC:Starting exec sp_cursorclose 180150003
RPC:Completed exec sp_cursorclose 180150003
email Follow the discussionReplies 3 repliesReplies Make a reply

Replies

#1 Erland Sommarskog
September 30th, 2011 - 05:31 pm ET | Report spam
ThomasD () writes:
SQL Server Error 'There are no rows in the current fetch buffer'

We are storing/updateting binary data (~125kb) from a file into column
of type IMAGE in a SQL Server table
using C++ / OLE-DB with an Accessor like
CCommand<CAccessor<MyAccessor>> / ISequentialStream

Since many of years this works fine, but now we have a constellation
on one server where we get an error.
On this server some special data files produce this error by storing
it, many other not.
We took this data files to an other server, but there it works fine.



That certainly seems like a difficult case.

To start somewhere, the image data type is deprecated, and you should
use varbinary(MAX), but you probably already know that. And it may not
help in this particular problem.

The SP:StmtStarting events certainly looks funny. But what events did you
include? Did you include all SP events like SP:Recompile, SQL:StmtRecompile
and the Cache events? I think I've seen multiple StmtStarting events
in conjunction with recompiles.

And maybe the recompile has something to do with error message, which I
would interpret as if the cursor have been exhausted. There are a couple
of cursor events; could be an idea to include those in the trace as well.

Here is another thing that looks spooky:

RPC:Starting exec sp_cursor 180150003,40,1
SP:StmtStarting FETCH API_CURSOR0000000000014854
RPC:Completed exec sp_cursor 180150003,40,1

Here is an SP:StmtCompleted missing as well. How does this look in the good
server?

As you realise, I don't have much clue of what is going on. But still,
what happens if you try a different cursor type, for instance a static
cursor?


Erland Sommarskog, SQL Server MVP,

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sql...14207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sql...95970.aspx

Similar topics