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

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:

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
.

Relevant Pages

  • RE: sqlTransaction Complete Error-dataype was image now varbinary(
    ... This means the first call will run fine when the @append is ... change the datatype back to image it all works fine. ... Phillip Johnson ... @FileID Uniqueidentifier, ...
    (microsoft.public.sqlserver.clients)
  • RE: sqlTransaction Complete Problem-dataype change from image to varbi
    ... And the isolation level of the transaction is ReadCommitted. ... I had the stored procedure below, which worked until the image datatype was ... @FileID Uniqueidentifier, ... IF @append = 0 ...
    (microsoft.public.dotnet.languages.csharp)
  • RE: sqlTransaction Complete Problem-dataype change from image to varbi
    ... chunks, ... I had the stored procedure below, which worked until the image datatype was ... IF @append = 0 ...
    (microsoft.public.dotnet.languages.csharp)
  • Re: 97 to 2003 Database Conversion - Datatype
    ... This is one of the reasons to use an append query, ... you can switch them to be append queries, to a known table format, instead. ... Steve Clark, Access MVP ... > After converting from 97 to 2003, text datatype is defaulting to binary. ...
    (microsoft.public.access.queries)
  • RowFilter Question
    ... i have a column that is transaction date the datatype is set to date for the ... my question is i need to set a row filter that will show ... WSToreyII ...
    (microsoft.public.dotnet.languages.vb)
Loading