April 4, 2003 at 9:24 am
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
April 4, 2003 at 10:30 am
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
April 4, 2003 at 10:51 am
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
April 4, 2003 at 11:12 am
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