March 14, 2008 at 12:18 am
For loading the images from the folder to database i need to specify the file location as variable inside the Openrowset. But i got some error over Problem Area as i mentioned below
Here is my code
Create PROC insert2img
as
Begin
Declare @img1 as varbinary(max)
Declare @dircmd as varchar(max)
DECLARE @filename varchar(100)
DECLARE @filepath varchar(100)
DECLARE @maxRowID int
DECLARE @count int
DECLARE @tempXMLFileName table (RowId int identity(1,1), name varchar(100))
Set @count =1
Set @dircmd = 'MASTER..XP_CMDSHELL "dir/b '+ 'F:\Images\*.*"'
INSERT
@tempXMLFileName exec (@dircmd)
SET @maxRowID = (SELECT max(RowId) FROM @tempXMLFileName)
WHILE @count <= @maxRowID
BEGIN
SET @filename = (SELECT name FROM @tempXMLFileName
WHERE [RowId] = @count)
Set @filepath = 'F:\Images\'+@filename
------------------------ Problem Area ---------------------
Insert into img (sno,imgdate,imgname,img)
Select @count,getdate(),'Image',
BulkColumn from Openrowset(
Bulk 'F:\Images\'+@filename, Single_Blob) as tt
------------------------ Problem Area ---------------------
Set @filepath=' '
Set @count = @count + 1
end
end
March 14, 2008 at 9:48 am
Waht are your errors?
You can use the built-in debug tool for stored procedure in QA.
Also, you may add some PRINT commands between your code and see whether or not you can get expected results in executing your script.
March 14, 2008 at 11:16 pm
Actually i got the following errors like
Incorrect syntax near '@filename'.
For inserting images in to database i used the following code
BulkColumn from Openrowset( Bulk 'F:\Images\picture005.jpg', Single_Blob) as tt
But i need to store lot image file from the folder, so for that i need to use filename variable inside the Openrowset. but it does not about to use variable inside that
BulkColumn from Openrowset( Bulk 'F:\Images\'+@filename, Single_Blob) as tt
March 15, 2008 at 7:30 am
The [font="System"]'data_file'[/font] argument to OPENROWSET(.. BULK.. ) must be a quoted string literal, it cannot be a variable or an expression.
Although Help and BOL are not entirely consistent, usually when you see an argument or parameter specified like [font="System"]'arg_name'[/font] it means that it has to be a quoted string literal, such as [font="Courier New"]'c:\foo\bar.txt'[/font].
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 15, 2008 at 8:09 am
Ultimately, to do this you will need to use dynamic SQL, like so:
Create PROC insert2img
as
Begin
Declare @img1 as varbinary(max)
Declare @dircmd as varchar(max)
DECLARE @filename varchar(100)
DECLARE @filepath varchar(100)
DECLARE @maxRowID int
DECLARE @count int
DECLARE @tempXMLFileName table (RowId int identity(1,1), name varchar(100))
Declare @sql as varchar(max)
Set @count =1
Set @dircmd = 'MASTER..XP_CMDSHELL "dir/b '+ 'F:\Images\*.*"'
INSERT
@tempXMLFileName exec (@dircmd)
SET @maxRowID = (SELECT max(RowId) FROM @tempXMLFileName)
WHILE @count <= @maxRowID
BEGIN
SET @filename = (SELECT name FROM @tempXMLFileName
WHERE [RowId] = @count)
Set @filepath = 'F:\Images\'+@filename'
Set @sql = '
Insert into img (sno,imgdate,imgname,img)
Select @count,getdate(),''Image'',
BulkColumn from Openrowset(
Bulk ''' + @filepath + ''', Single_Blob) as tt
'
Exec (@sql)
Set @filepath=' '
Set @count = @count + 1
end
end
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
March 20, 2008 at 1:18 am
Thanks
Balaji
March 20, 2008 at 10:45 am
Glad to help.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 17, 2009 at 9:30 pm
Hello, I'm in a same dilema today with SQL 2008. I tried your advice using dynamic SQL in replacing the 'data_file' part of the OPENROWSET. Here is part of the sp code...
select * from OPENROWSET (bulk @PathFilename, single_blob) as document
where @Pathfilename stores the path and filename of the file.
i tried
select * from OPENROWSET (bulk '''' + @PathFilename + '''', single_blob) as document
but i get this > Incorrect syntax near '+'
I hope you have another workaround for this. Thanks
September 17, 2009 at 9:59 pm
Look at my code again. If you aren't using EXEC(@stringVariable) somewhere then you aren't really doing dynamic SQL.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
September 18, 2009 at 12:13 am
hi, i took a breather and analyzed my code again. i overlooked some syntax. i must be working too many long hours.
thanks!
September 18, 2009 at 5:46 am
Hi,
Here is the code what i used to execute successfully,
Please have the Image column at the last in insert query,
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create PROC [dbo].[insert2img]
as
Begin
Declare @img1 as varbinary(max) --Declare @img2 ras varbinary(max)
Declare @dircmd as varchar(max)
DECLARE @filename varchar(100)
DECLARE @filepath varchar(100)
DECLARE @maxRowID int
DECLARE @count int
DECLARE @tempXMLFileName table (RowId int identity(1,1), name varchar(100))
delete from img
Set @count =1
Set @dircmd = 'MASTER..XP_CMDSHELL "dir/b '+ 'F:\Images\*.*"'
INSERT
@tempXMLFileName exec (@dircmd)
SET @maxRowID = (SELECT max(RowId) FROM @tempXMLFileName)
--Select @maxRowID as maxrowid
WHILE @count 1)
begin
Insert into img (sno,imgdate,imgname,img)
Select @count,getdate(),@filename,convert(varbinary(max),dbo.getimg(@filepath),120)
--select dbo.getimg()
end
Set @filepath=' '
Set @count = @count + 1
end
end
create FUNCTION dbo.getimg(@filenames varchar(100))
RETURNS NVARCHAR(500)
AS
BEGIN
DECLARE @sql NVARCHAR(500)
SET @sql = N'BulkColumn from Openrowset(Bulk '''+ @filenames + ''' , Single_Blob) as Testimage '
return @sql
end
by
Bala
September 21, 2009 at 7:37 pm
Thanks Balaji for the code.
June 1, 2010 at 10:40 am
That was a great help! I too was stuck with the variable filename issue in OpenXML. The dynamic sql did the trick.
October 14, 2011 at 12:37 am
I think MS should modify this function to accept variable string rather than literal "const" string without any possibility to construct the string argument. dynamic SQL helps but, as there's nothing in SQL like Const declaration it would be more consistent to allow this argument to be itself dynamically constructed and passed to the function, i think.
November 16, 2011 at 11:33 pm
[font="Verdana"]Hello everybody,
I am having some problems in storing an image in BLOB in SQL SERVER 7/2000. I am getting an error msg : Incorrect syntax near the keyword 'Bulk'.
Please help.
---
Thanks in advance
[/font]
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply