Q: how do you copy a blob/image record to another?

  • Greetings,

    Running into an odd problem that I cannot find a particular solution to:

    Need to create a new record from a table that contains blob/image data in the same table.

    CREATE TABLE [SketchTable] (

    [intID] [int] IDENTITY (1, 1) NOT FOR REPLICATION NOT NULL ,

    [intMasterAcctID] [int] NOT NULL ,

    [intBuildingID] [int] NOT NULL ,

    [imgSketch] [image] NOT NULL CONSTRAINT [DF_SketchTable_imgSketch] DEFAULT ('A')

    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    GO

    So passing in both the new values for intmasteracctid/intbuildingid and the old I came up with this proc:

    Create PROCEDURE Sp_CopySketchImage

    @intOldMasterAcctID as int,

    @intOldBuildingID as int,

    @intNewMasterAcctID as int,

    @intNewBuildingID as int

    AS

    Declare @oldImage as Image

    Select @oldImage=imgSketch from SketchTable where intMasterAcctId=@intOldMasterAcctID and intBuildingID=@intOldbuildingId

    INSERT INTO

    SketchTable

    (

    intMasterAcctID,

    intBuildingID,

    imgSketch

    )

    VALUES

    (

    @intNewMasterAcctID,

    @intNewBuildingID,

    @oldImage

    )

    I get an error of:

    Server: Msg 2739, Level 16, State 1, Procedure sp_CopySketchImage, Line 0

    The text, ntext, and image data types are invalid for local variables.

    So, any idea on how to get this done?

    Chris

  • Try to remove: Declare @oldImage. Replace Select/Insert pair with single INSERT...SELECT statement:

    INSERT INTO SketchTable

    (intMasterAcctID,intBuildingID,imgSketch)

    SELECT @intNewMasterAcctID,@intNewBuildingID,

    @oldImage from SketchTable where intMasterAcctId=@intOldMasterAcctID and intBuildingID=@intOldbuildingId

  • Still cant get around not declaring @oldimage as image in your example.

    I tried the following addition;

    INSERT INTO

    SketchTable

    (

    intMasterAcctID,

    intBuildingID,

    imgSketch

    )

    VALUES

    (

    @intMasterAcctID,

    @intBuildingID,

    (Select imgSketch from SketchTable where intMasterAcctID=@intOldMasterAcctID and intBuildingID=@intOldbuildingID)

    GO

    error received:

    Server: Msg 1046, Level 15, State 1, Procedure Sp_CopySketchImage, Line 21

    Subqueries are not allowed in this context. Only scalar expressions are allowed.

    now I know why people thing blobs are bad.. not because of any perceived performance issue but more that they're just a pain in the arse to manipulate 😉

    thanks and still looking,

    Chris

  • Sorry, I mistyped the INSERT...SELECT 🙁

    Remove @oldImage from your stored procedure at all. And don't use subquery. Use this:

    INSERT INTO SketchTable

    (intMasterAcctID,intBuildingID,imgSketch)

    SELECT @intNewMasterAcctID,@intNewBuildingID,

    imgSketch from SketchTable where intMasterAcctId=@intOldMasterAcctID and intBuildingID=@intOldbuildingId

Viewing 4 posts - 1 through 3 (of 3 total)

You must be logged in to reply to this topic. Login to reply