Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Using Variable in Openrowset Expand / Collapse
Author
Message
Posted Friday, March 14, 2008 12:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 18, 2009 5:50 AM
Points: 4, 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
Post #469199
Posted Friday, March 14, 2008 9:47 AM
UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: Friday, April 05, 2013 4:43 PM
Points: 1,473, Visits: 1,314
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.
Post #469503
Posted Friday, March 14, 2008 11:16 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 18, 2009 5:50 AM
Points: 4, 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

Post #469777
Posted Saturday, March 15, 2008 7:30 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #469813
Posted Saturday, March 15, 2008 8:09 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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 &lt;= @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."
Post #469822
Posted Thursday, March 20, 2008 1:18 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, September 18, 2009 5:50 AM
Points: 4, Visits: 17
Thanks
Balaji
Post #472084
Posted Thursday, March 20, 2008 10:45 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
Glad to help.

-- RBarryYoung, (302)375-0451 blog: MovingSQL.com, Twitter: @RBarryYoung
Proactive Performance Solutions, Inc.
"Performance is our middle name."
Post #472412
Posted Thursday, September 17, 2009 9:30 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:24 AM
Points: 5, Visits: 27
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
Post #790083
Posted Thursday, September 17, 2009 9:59 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Friday, March 28, 2014 2:25 PM
Points: 9,902, Visits: 9,479
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."
Post #790092
Posted Friday, September 18, 2009 12:13 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, April 03, 2014 1:24 AM
Points: 5, Visits: 27
hi, i took a breather and analyzed my code again. i overlooked some syntax. i must be working too many long hours.

thanks!
Post #790131
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse