Clever Geek Handbook

Overcome 8k limitation for sql server stored procedure parameter

I recently solved an issue that a VB6 application had when storing blobs in sql server using ADO 2.8 and AppendChunck method for Recordset.Field. If the data was too large, I would get either an "Invalid Handle" in the for loop, or "Not enough system storage to complete this operation." I fixed the problem by sending chunks to the stored procedure and using "updatetext". However, with this I can now only send 8k characters over the 8k limit. Does anyone know of a good workaround? Below is my sproc.

@chunck binary(8000),  
@result_id int

as

declare @pointer binary(16),  
        @imagelength int,  
        @datalength int  


--get the pointer and length to the image column    
select @pointer = textptr(result_image),  
       @imagelength = datalength(result_image)  
from dbo.dash_result  
where result_id = @result_id

if @pointer is null

update dbo.dash_result  
set result_image = @chunck  
where result_id = @result_id  

else

begin

  --append the chunck of data to the end  
  set @datalength = datalength(@chunck)  
  updatetext dbo.dash_result.result_image @pointer @imagelength 0 @chunck

end
+1


source to share


2 answers


For SQL Server 2000, declare the parameter as text. Please note that this must be a parameter, not a local var, only parameters can be textual and not local.



0


source


If you are using SQL Server 2005, you can use the varbinary (MAX) or varchar (MAX) data type, depending on the type of data you are storing. I believe they can hold up to 2 gigabytes of data.



+2


source






More articles:


All Articles