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
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.
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.