SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Using Variable in Openrowset


Using Variable in Openrowset

Author
Message
Balaji Shenbagarajan
Balaji Shenbagarajan
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 17
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\'+[color=#CCCCCC]@filename[/color], Single_Blob) as tt

------------------------ Problem Area ---------------------


Set @filepath=' '
Set @count = @count + 1

end

end
SQL ORACLE
SQL ORACLE
SSCrazy
SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)SSCrazy (2.6K reputation)

Group: General Forum Members
Points: 2617 Visits: 1314
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.
Balaji Shenbagarajan
Balaji Shenbagarajan
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 17
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14746 Visits: 9518
The 'data_file' 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 'arg_name' it means that it has to be a quoted string literal, such as 'c:\foo\bar.txt'.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14746 Visits: 9518
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



-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Balaji Shenbagarajan
Balaji Shenbagarajan
Grasshopper
Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)Grasshopper (24 reputation)

Group: General Forum Members
Points: 24 Visits: 17
Thanks
Balaji
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14746 Visits: 9518
Glad to help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
rommel.pagtakhan
rommel.pagtakhan
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 30
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
RBarryYoung
RBarryYoung
SSChampion
SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)SSChampion (14K reputation)

Group: General Forum Members
Points: 14746 Visits: 9518
Look at my code again. If you aren't using EXEC(@stringVariable) somewhere then you aren't really doing dynamic SQL.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
rommel.pagtakhan
rommel.pagtakhan
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 30
hi, i took a breather and analyzed my code again. i overlooked some syntax. i must be working too many long hours.

thanks!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search