sqlTransaction Complete Error-dataype was image now varbin..

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

Run it directly from SQL and see if you get a better error message.

"Phil Johnson" wrote:

> 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