April 19, 2011 at 7:13 am
Wow talk abot wasting time (perf wise).
Run the dir command ONCE at the begining of the code and put the right index on the table. Then just do the exists.
Or just DO IT RIGHT and process the freaking documents in the folder that needs to be processed instead of looping 70 000 times and executing command shell 70 000 times! :sick::exclamationmark:
April 19, 2011 at 8:08 am
Listen,..10 or 10000 the script now works.....i couldn't care if it took all day ....its a one of insert ....just helping robert out...as usual its a hack...once its working you can spend all day fine tuning it...time i don't have...just giving a steer/helping out a fellow developer...
April 19, 2011 at 8:13 am
Tx for the assist. Just pointing out the "correct" way to do it so the newbie can learn.
Besides it's just a matter of moving the cmdshell out of the loop.
April 19, 2011 at 8:51 am
Hi Robin,
Yes, this is more a eastern present: thanks a lot for the good job. But it still doesnot function as expected. I changed the folderpath in:
'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
So nothing special and for the rest I changed nothing. Then execute sys x times (I raised the counter to 50) 2 rows affected, but when I look in the database no rows are added.
Furthermore when I fill in the wrong path I get an error on exactly the right places, that is were an image might be expected. So everything is OK, but no results.
Sorry for asking again, but any ideas?
To be continued I'm afraid.
Grz,
Robert
April 19, 2011 at 8:56 am
Have you tried removing the insert just to see if the select actually works?
April 19, 2011 at 9:19 am
Yes, after your suggestion of course, and the sqlstring looks like this:
SELECT 1 AS ImageID, '1.jpg' As ImageName,
BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\1.jpg',
SINGLE_BLOB) As BLOB
So I think that the string is OK. So the mystery gets bigger and bigger.
Grz,
Robert
April 19, 2011 at 9:28 am
There's no place for "think it works" in sql server. You must RUN the code and see it work.
This works for me.
Do you get any errors?
USE [tempdb]
GO
BEGIN TRAN
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE Renner_Foto (
[ImageID] [int] NULL,
[ImageName] [nvarchar](50) NULL,
[ImageData] [varbinary](max) NULL
) ON [PRIMARY]
GO
SET ANSI_PADDING OFF
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData)
SELECT 1 AS ImageID, '1.jpg' As ImageName,
--BulkColumn FROM OPENROWSET( Bulk 'C:\test image\dellbtn.gif',
BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\1.jpg',
SINGLE_BLOB) As BLOB
SELECT * FROM Renner_Foto
ROLLBACK
April 19, 2011 at 9:29 am
PS copy / run exactly as is, (I run in tempdb volontarely).
Do you get any errors, do you see the data in the table??
April 19, 2011 at 9:38 am
The output looks as follows:
11.jpg0xFFD8FFE000 etc.
So again: no problems with one image. With my webpage you can see the image and it is OK.
April 19, 2011 at 9:40 am
Then what error are you getting on the insert when it fails?
For me I get this error :
Cannot bulk load because the file "D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\1.jpg" could not be opened. Operating system error code 3(The system cannot find the path specified.).
April 19, 2011 at 9:48 am
I think your error has to do with the filepath, more specific the space in [Mijn Documenten]. If you put that piece of code between "" the problems should be over.
The strange thing is: I don't get any errors on one hand, but also no records on the other hand. Per loop the message is [2 rows affected], but not in the database for one reason or another.
Grz,
Robert
April 19, 2011 at 9:54 am
r_slot (4/19/2011)
I think your error has to do with the filepath, more specific the space in [Mijn Documenten]. If you put that piece of code between "" the problems should be over.The strange thing is: I don't get any errors on one hand, but also no records on the other hand. Per loop the message is [2 rows affected], but not in the database for one reason or another.
Grz,
Robert
For the nth time. PRINT the freaking commands and run them MANUALLY. You'll get the error and missing problem!
April 19, 2011 at 10:10 am
I think I am doing/have done what you said:
USE WielerDatabaseSQL
GO
BEGIN TRAN
DECLARE @counter AS int
DECLARE @folderpath AS varchar(255)
DECLARE @fullpath AS varchar(4000)
DECLARE @shellpath AS varchar(4000)
DECLARE @shellfullpath 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 = 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @shellpath = 'D:\"Mijn Documenten"\Documents\DWCoureurLocaleData\Irfanview_26012011'
SET @counter = 24
WHILE @counter >23 and @counter < 50
BEGIN
SET @imagename = convert(varchar, @counter) + '.jpg'
SET @imagenamequoted = @single_quote + @imagename + @single_quote
SET @fullpath = @folderpath + '\' + @imagename
SET @shellfullpath = @shellpath + '\' + @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 ' + @shellfullpath
INSERT #tmp EXEC master.dbo.xp_cmdshell @cmd
IF EXISTS(SELECT 1 FROM #tmp WHERE s=@imagename)
BEGIN
exec sp_executesql @sqlstring
END
SELECT 'go to results in textmode' as Outputmode
SELECT @sqlstring
DROP TABLE #tmp
SET @counter = @counter + 1
END
ROLLBACK
See the two Select statements. If you mean something else, let me know. Another thing is, that in your temp-variant I get letters and figures code and when using my old method (see the first page of this bible) I get the announcement <binary data>.
The result of the string is:
INSERT INTO Renner_Foto (ImageID, ImageName, ImageDAta ) SELECT 32 AS ImageID, '32.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\32.jpg', SINGLE_BLOB) As BLOB
Grz,
Robert
April 19, 2011 at 10:27 am
Well if you actually run begin tan / rollback tran you certainly won't have anything left in the db after you run the code!
April 19, 2011 at 10:30 am
I mean copy - paste this into SSMS. Then run the code 1 line a t a time and see what works and what fails and WHY.
Repeat process untill it works 100%.
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 24 AS ImageID, '24.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\24.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 25 AS ImageID, '25.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\25.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 26 AS ImageID, '26.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\26.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 27 AS ImageID, '27.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\27.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 28 AS ImageID, '28.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\28.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 29 AS ImageID, '29.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\29.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 30 AS ImageID, '30.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\30.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 31 AS ImageID, '31.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\31.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 32 AS ImageID, '32.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\32.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 33 AS ImageID, '33.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\33.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 34 AS ImageID, '34.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\34.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 35 AS ImageID, '35.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\35.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 36 AS ImageID, '36.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\36.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 37 AS ImageID, '37.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\37.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 38 AS ImageID, '38.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\38.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 39 AS ImageID, '39.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\39.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 40 AS ImageID, '40.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\40.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 41 AS ImageID, '41.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\41.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 42 AS ImageID, '42.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\42.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 43 AS ImageID, '43.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\43.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 44 AS ImageID, '44.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\44.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 45 AS ImageID, '45.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\45.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 46 AS ImageID, '46.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\46.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 47 AS ImageID, '47.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\47.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 48 AS ImageID, '48.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\48.jpg', SINGLE_BLOB) As BLOB ;
INSERT INTO Renner_Foto (ImageID, ImageName, ImageData ) SELECT 49 AS ImageID, '49.jpg' As ImageName, BulkColumn FROM OPENROWSET( Bulk 'D:\Mijn Documenten\Documents\DWCoureurLocaleData\Irfanview_26012011\49.jpg', SINGLE_BLOB) As BLOB ;
Viewing 15 posts - 31 through 45 (of 61 total)
You must be logged in to reply to this topic. Login to reply