CODEWITHSUNDEEP
c#.netsqlt-sqlstored-procedures
War
War

Reputation: 8618

Stored procedure woes ... inserting binary

Ok so I have this storedproc in my SQL 2008 database (works in 2005 too / used to) ...

CREATE PROCEDURE [dbo].[SetBinaryContent]
    @Ref nvarchar(50),
    @Content varbinary(MAX),
    @ObjectID uniqueidentifier
AS
BEGIN

    DELETE ObjectContent WHERE ObjectId = @ObjectID AND Ref = @Ref

    IF DATALENGTH(@Content) > 5
    BEGIN
        INSERT INTO ObjectContent
            (Ref,BinaryContent,ObjectId)
        VALUES
            (@Ref,@Content,@ObjectId)
    END

    UPDATE Objects SET [Status] = 1
    WHERE ID = @ObjectID
END

Relatively simple, I take a byte array in C# and chuck it in @Content i then give it a guid and string for the other params and off we go.

...

Great, it used to work ... but it don't anymore ... so erm ... What's wrong with this stored proc?

I've stepped through my C# code thinking I screwed up somehow in that but it definately adds the params and gives them the correct values so what would cause the server to just stop executing this storedproc correctly?

When called this proc executes but nothing changes in the db ... no new records are added to the ObjectContent table.

Weird huh ...

Upvotes: 0

Views: 484

Answers (2)

War
War

Reputation: 8618

HLGEM ...

I'm gonna give you this because if I could use profiler it would have shown the actual fix.

Essentially i dug a bit deeper in my code and found that I had in fact been a pleb ... I have several connection strings in my config file and somehow I was picking up the wrong one, so it was working perfectly ... just on the worng db.

I'm sorry for wasting anyone's time.

Don't I feel dumb now ...

I up voted you too ... jeez i'm a muppet !!

Upvotes: 0

HLGEM
HLGEM

Reputation: 96570

HAve you run Profiler to see exactly what is being sent to the SQL Server?

Have you tried to manually exec the stored proc using the values you got from Profiler?

Upvotes: 3

Related Questions