April 18, 2011 at 10:49 am
This would also be a much smarter way to go. List the files that are actually in the folder. And only try to import those :
IF OBJECT_ID('tempdb..#out') > 0 DROP TABLE #out
IF OBJECT_ID('tempdb..#cmds') > 0 DROP TABLE #cmds
CREATE TABLE #out (t VARCHAR(500) NULL, dt DATETIME NULL, TYPE CHAR(1))
INSERT INTO #out (t)
--EXEC xp_cmdshell 'dir / B /OD Q:\*.bak'
EXEC xp_cmdshell 'dir Q:\*.bak'
--DELETE t FROM #out t WHERE t.t NOT LIKE '%-%.bak' OR t IS NULL
--UPDATE #out SET t = 'Q:\' + t, dt = REPLACE(LEFT(RIGHT(t, 20), 16), 'H', ':'), TYPE = CASE WHEN LOWER(t) LIKE '%full%' THEN 'D' ELSE 'L' END
SELECT * FROM #out
April 18, 2011 at 11:14 am
Yes I am a bad listener, sorry for that. I didnot really understand where you were talking about. Though with your example the sun is coming through, I could copy your work and I didnot get any error.
There is a weird thing though and that is that the query says under messages one row effected (counter 670 should be added to the table) but when I look in the table there is no row added.
Another question is: what do we learn from this (apart from the problem mentioned above)? When I raise the counter to <675 I get 8 times the message row effected, but not a single row is added to the table. Do I miss something (again)?
Grz,
Robert
April 18, 2011 at 11:20 am
Did the row really insert?
Trigger deleting the row?
transaction not closed?
April 18, 2011 at 11:23 am
Sorry I missed your second suggestion. This is going a bit too far for me: with other words I don't quite understand your code. You produce a temp table with the filenames in it? Where should my filepath fit in? And where should this code be placed? When I run the code in this form then I get an error about the path.
Grz
Robert
April 18, 2011 at 11:28 am
dir is an ms - dos commande.
That's where the path goes. Google it for full list of options.
Once you have a list of files to process just go at it in a loop like you do for the counter.
April 18, 2011 at 11:32 am
OK, we try it out. An answer to one of your former questions: the row is not inserted. I closed up everything, so the transaction must be closed. So the code that only produces one record has no effect, but also has no errors. I don't catch that.
Any ideas?
Robert
April 18, 2011 at 11:35 am
Flush the insert command and rerun the loop for a couple rows, maybe 5 or 10. See what is being selected. That should give you the answer.
April 18, 2011 at 12:36 pm
Can you give me a clue? Flush the insert mode? I don't get any further than using your code and increase the counter to < 680. According to the jpg files that should add records to the database, but nothing happens. In fact the same situation as with one record. When the counter is 670 it should add one record (imageid = 670, imagename=' ', imagedata = 670.jpg).
I really don't get it. Perhaps you can do one more attempt to clear things up? Would be great.
Grz,
Robert
April 18, 2011 at 1:01 pm
I am looking into de exec command and I don't understand how the current directory (dir) can match with my photo-directory when no further information has been supplied to the EXEC command.
I would imagine that the code should be the following:
EXEC xp_cmdshell 'dir D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\*.jpg'
but this gives an error (cannot find the path).
Furthermore I understand that you create a temp table 'out' and fill this with the directory listing.
How I get this up and running? Sorry for the question-flush, but I cannot get any better (yet).
Grz,
Robert
April 18, 2011 at 1:48 pm
I solved one problem: my directory path contained a space in [Mijn documenten]. Putting that between quotes solved the non-executing problem. Next I try to get things running. Any help is welcome.
Grz,
Robert
April 18, 2011 at 2:34 pm
Last call for today on my part. I am running around in circles.
When I execute the following code:
USE WielerDatabaseSQL
GO
DECLARE @counter int
DECLARE @folderpath AS varchar(4000)
DECLARE @fullpath AS varchar(4000)
DECLARE @sqlstring AS varchar(4000)
SET @folderpath = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 674
WHILE @counter < 675
BEGIN
SET @fullpath = @folderpath + '\' + convert(varchar, @counter) + '.jpg'
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageData) '
SET @sqlstring = @sqlstring + 'SELECT '' + convert(varchar, @counter) + '' AS ImageID, '
SET @sqlstring = @sqlstring + 'NULL'' As ImageName,'
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk '' + @fullpath + '','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
--select 'go into results to text mode' as OutputMode
EXEC sp_executesql @sqlstring
SET @counter = @counter + 1
END
I get the following error:
Msg 214, Level 16, State 2, Procedure sp_executesql, Line 1
Procedure expects parameter '@statement' of type 'ntext/nchar/nvarchar'.
When I change the @sqlstring in nvarchar(4000) at declare level or with a convert statement down the line I get the following error:
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '+'.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ', SINGLE_BLOB) As BLOB '.
Double click doesnot give any clues.
The last problem is the use of the directory with the files. I didnot yet figure out how to past this in the rest of the code. But: tomorrow there is another day. Thanks for your help anyway.
Grz,
Robert
April 19, 2011 at 4:24 am
Hi folks,
We give it another try. The situation is as follows:
1. I filled the Renner_Foto table with an ImageID and an ImageName.
2. I made a tblNummer and a vwNummer. In the tblNummer table are the data as in the Ninja-post (filename, date/time, type), in the view we sorted out the file-numbers as identical to ImageID and the filenames. A screenshot:
1000 1000.jpg
10014 10014.jpg
10263 10263.jpg
10292 10292.jpg
1046 1046.jpg
10734 10734.jpg
10735 10735.jpg
At this moment I don't use this data, but it is available.
3. Then I tried the following with an update statement:
USE WielerDatabaseSQL
GO
DECLARE @Counter As Int
WHILE @Counter < 12000
BEGIN
SET @Counter = 1
UPDATE Renner_Foto
SET ImageData = BulkColumn FROM OPENROWSET
( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\' + @Counter + '.jpg',
SINGLE_BLOB) As BLOB
WHERE ImageID = @Counter
SET @Counter = @Counter + 1
END
GO
We use the counter to check if there is a match with an imageid. If there is we add the image, if not we go to the next record.
Of course I get an error:
Msg 102, Level 15, State 1, Line 11
Incorrect syntax near '+'.
This means that the @counter combination in the bulk-statement is not the right one. I have no idea how to use the quotes in the right way.
Can somebody assist me with some advise?
Grz,
Robert
April 19, 2011 at 5:43 am
I've never imported images using that method so I can't offer much advice.
The only thing I can offer is simply this : instead of guessing at the files in the directory, use my dir command to get a list (check out the commented line below the first dir command, that'll give you an easier time to process the file names).
From there on out it's just a matter of having the right command to do the insert on which I can't really help you out.
1 last thing is that I've had issues in the past trying to concatenate and exec in the same step. So maybe I'd build the command on 1 line and then execute it on the next.
If anything, that gives you the option to print the command and execute it manually untill you get it fixed. Once that's done you have your template and it's really easy to finish the project at that point.
April 19, 2011 at 5:55 am
Thanks a lot so far. I'll do my best to solve the problem.
Grz
Robert
April 19, 2011 at 7:08 am
hi Robert ..xmas come early for you ..here is complete code ...insert only works if the image exists...
i have only 7 images in my images directory...check it out....
-=====================================================
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @sqlstring AS nvarchar(4000)
DECLARE @single_quote varchar(1)
DECLARE @imagename varchar(255)
DECLARE @imagenamequoted varchar(255)
DECLARE @fullpathquoted varchar(255)
DECLARE @cmd varchar(255)
SET @single_quote = ''''
SET @folderpath = 'c:\development_support\images'
SET @counter = 1
WHILE @counter < 10
BEGIN
SET @imagename = convert(varchar, @counter) + '.jpg'
SET @imagenamequoted = @single_quote + @imagename + @single_quote
SET @fullpath = @folderpath + '\' + @imagename
SET @fullpathquoted = @single_quote + @fullpath + @single_quote
SET @sqlstring = ''
SET @sqlstring = @sqlstring + 'INSERT INTO Renner_Foto (ImageID, ImageName, ImageDAta ) '
SET @sqlstring = @sqlstring + 'SELECT ' + convert(varchar, @counter) + ' AS ImageID, '
SET @sqlstring = @sqlstring + @imagenamequoted + ' As ImageName, '
SET @sqlstring = @sqlstring + 'BulkColumn FROM OPENROWSET( Bulk ' + @fullpathquoted + ','
SET @sqlstring = @sqlstring + ' SINGLE_BLOB) As BLOB '
-- See if the file exists first then only insert it
CREATE TABLE #tmp(s varchar(1000))
SET @cmd = 'dir /B ' + @fullpath
INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd
IF EXISTS(SELECT 1 FROM #tmp WHERE s = @imagename)
BEGIN
exec sp_executesql @sqlstring
END
DROP TABLE #tmp
SET @counter = @counter + 1
END
--=============================================================
Viewing 15 posts - 16 through 30 (of 61 total)
You must be logged in to reply to this topic. Login to reply