Cannot bulk load. The file does not exist.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Cannot bulk load. The file does not exist.

Author

Topic

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-22 : 23:19:58

I have been using the following code which works fine if the jpg file exists on the hard drive that is referenced in the path. However if the file does not exist I get this error. Any idea how to change the code so if one of the files does not exist on the hard drive the error does not occur and the code moves on to the next file maybe importing NULL into Image field rather than failing.

Msg 4860, Level 16, State 1, Line 1
Cannot bulk load. The file "J:\VCP DB 2\VCPDataRoot\EventExtraData\12\2012\5\22\0000781960_1.jpg " does not exist.

CREATE TABLE #ib (ID int IDENTITY(1,1) NOT NULL, [FileName] nvarchar(255) NOT NULL, [Image] varbinary(max), [EventID] int, [EventExtraDataID] int)

DECLARE insert_cursor CURSOR FAST_FORWARD FOR

Select [EventID], [EventExtraDataID], [Path], [FileName] from #i

OPEN insert_cursor

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

WHILE @@FETCH_STATUS = 0

BEGIN

--Print @Path
--Print @File

EXEC('INSERT INTO #ib ([EventID], [EventExtraDataID], [FileName], [Image])
SELECT ''' + @EventID1 + ''', ''' + @EventExtraDataID + ''', ''' + @File + ''', *
FROM (
SELECT *
FROM OPENROWSET(BULK ''' + @Path + ''', SINGLE_BLOB) As rs) As im')

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

END

CLOSE insert_cursor
DEALLOCATE insert_cursor

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-23 : 01:15:55

My solution;

CREATE TABLE #ib (ID int IDENTITY(1,1) NOT NULL, [FileName] nvarchar(255) NOT NULL, [Image] varbinary(max), [EventID] int, [EventExtraDataID] int)

DECLARE insert_cursor CURSOR FAST_FORWARD FOR

Select [EventID], [EventExtraDataID], [Path], [FileName] from #i

OPEN insert_cursor

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

WHILE @@FETCH_STATUS = 0

BEGIN

--Print @Path
--Print @File

Exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
Exec sp_OAMethod @objFSys, 'FileExists', @i out, @Path

If @i = 1

Begin

EXEC('INSERT INTO #ib ([EventID], [EventExtraDataID], [FileName], [Image])
SELECT ''' + @EventID1 + ''', ''' + @EventExtraDataID + ''', ''' + @File + ''', *
FROM (
SELECT *
FROM OPENROWSET(BULK ''' + @Path + ''', SINGLE_BLOB) As rs) As im')

END

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

END

CLOSE insert_cursor
DEALLOCATE insert_cursor

Exec sp_OADestroy @objFSys

harlingtonthewizard
Constraint Violating Yak Guru

352 Posts

Posted - 2012-05-23 : 22:16:29

Correction to above in case where no files exist;

Declare @File As nvarchar(255)
Declare @Path As nvarchar(255)
Declare @EventID1 As int
Declare @EventExtraDataID As int
Declare @sql1 As nvarchar(max)
Declare @objFSys int
Declare @i int

END

FETCH NEXT FROM insert_cursor
INTO @EventID1, @EventExtraDataID, @Path, @File

Exec sp_OADestroy @objFSys

END

CLOSE insert_cursor
DEALLOCATE insert_cursor

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources