inserting image using Openrowset

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

inserting image using Openrowset

Author

Topic

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-02 : 08:55:48

hi

I have a stored Proc: How do I use the Openrowset to insert a picture using the spForInsert stored Procedures? Thanks

Create Proc spForInsert
@ProdID as int,
@ProdPic as Varbinary(max)
@Prodinfo as Nvarchar(4000)
as
INSERT INTO [tblProduct]
([ProdID]
,[ProdPic]
,[Prodinfo])
VALUES
(@ProdID,@ProdPic, @Prodinfo)

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 09:07:17

From BOL

OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB)

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-02 : 09:15:49

hi

As i am using a stored Procedures, do you mean

Create Proc spForInsert
@ProdID as int,
@Prodinfo as Nvarchar(4000)
as
INSERT INTO [tblProduct]
([ProdID]
,[ProdPic]
,[Prodinfo])
VALUES
(@ProdID,@OPENROWSET(BULK N'C:\Text1.txt', SINGLE_BLOB), @Prodinfo)

It didn't seems to work? Thanks

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 09:57:05

Create Proc spForInsert
@ProdID as int,
@Prodinfo as Nvarchar(4000)
as
INSERT INTO [tblProduct]
([ProdID]
,[ProdPic]
,[Prodinfo])
SELECT
@ProdID,* FROM OPENROWSET(BULK N'Image_path', SINGLE_BLOB) as t, @Prodinfo

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-02 : 10:05:14

hi

Sorry, it is not able to create the stored proc. The error message was:
Msg 156, Level 15, State 1, Procedure spForInsert, Line 6
Incorrect syntax near the keyword 'OPENROWSET'.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-02 : 10:14:46

See my edited reply

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-02 : 11:02:26

i am really really sorry for the trouble.

It seems that it is not working. Error message is:
Msg 1087, Level 16, State 1, Procedure spForInsert, Line 10
Must declare the table variable "@Prodinfo".

Thanks

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-02 : 20:34:20

hi

Thanks. I got it work. Thanks again.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2009-03-03 : 02:25:40

quote:
Originally posted by sg2255551

hi

Thanks. I got it work. Thanks again.

Well. Post the modified code

Madhivanan

Failing to plan is Planning to fail

sg2255551
Constraint Violating Yak Guru

274 Posts

Posted - 2009-03-03 : 05:19:45

hi

Since my table structure is ProdID, ProdPic, ProdInfo order. I guess that my insert statement do not necessarily follow that orders of the table structure. So i modify to

Create Proc spForInsert
@ProdID as int,
@Prodinfo as Nvarchar(4000)
as
INSERT INTO [tblProduct]
([ProdID]
,[Prodinfo]
,[ProdPic])
SELECT @ProdID, @Prodinfo,* FROM OPENROWSET(BULK N'Image_path', SINGLE_BLOB) as t

Thanks

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources