sqlTransaction Complete Error-dataype was image now varbinary(max)

#31

I should add, I have modified the datatype of the parameter in C# to be
varBinary:

SqlParameter paramData = cm.Parameters.Add("@data",
System.Data.SqlDbType.VarBinary);

And the isolation level of the transaction is ReadCommitted.

Also, I tried running the stored procedure directly in the Enterprise
manager and I did not get any errors with @append set to either 1 or 0.

--
Regards,

Phillip Johnson (MCSD For .NET)
PJ Software Development
www.pjsoftwaredevelopment.com

"Phil Johnson" wrote:

Quote:

Hi,

This is being called in a C# loop within an ado.net transaction from C# 1.1
code. It is used to write large file data to a SQL Server database in
chunks, rather than in one go.

I had the stored procedure below, which worked until the image datatype was
changed to varbinary(max).

Now I get the following error:

This SqlTransaction has completed; it is no longer usable

The stored procedure is here (NOTE this is the version that works with the
dataype set to image. If I change the datatype to varbinary(max), and
obviously change the field in the table as well, the code fails with the
error given above).

ALTER PROCEDURE [dbo].[FileUploadData_Upd]
@FileID Uniqueidentifier,
--@data varbinary(max),
@data image,
@append bit
AS

SET NOCOUNT ON
IF @append = 0
UPDATE dbo.FileUpload
SET Data = @data
WHERE FileId = @FileID;
IF @append = 1
UPDATE dbo.FileUpload
SET Data.write(@data,NULL,0)

Any help would be appreciated

--
Regards,

Phillip Johnson (MCSD For .NET)
PJ Software Development
www.pjsoftwaredevelopment.com

Thread Tools
Show Printable Version Email this Page
Display Modes
Linear Mode Switch to Hybrid Mode Switch to Threaded Mode

sqlTransaction Complete Error-dataype was image now varbinary(max)

microsoft.public.sqlserver.clients microsoft.public.sqlserver.clients

RE: sqlTransaction Complete Error-dataype was image now varbinary(max) - 01-24-2008 , 05:42 AM