asked on

SQL2K: BULK INSERT: Check if files exists

I need to check to see if a file exists before I attempt a BULK INSERT in SQL Server 2K. I have tried code *similar* to the following:

INSERT TBL_Export_Tracking FROM 'C:\last.track' WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n')

IF @@ERROR <> 0
BEGIN
PRINT 'boo'
END

However, the error routine is not reached SQL Server instead returns and says the file is not found. SS7 had a procedure that could check for the existence of a file, but for soem reason 2000 does not. Any ideas?

nigelrivett

declare @objFSys int
declare @FileName varchar(128)
declare @i int

select @FileName = 'C:\myfile.txt'

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @FileName
if @i = 1
print 'exists'
else
print 'not exists'
set objFSys = nothing

Frostbyte_Zero

Add a ActiveX Script to a DTS or in your SQL Server Job like this:

Function Main()

Set fso = CreateObject("Scripting.FileSystemObject")

i = fso.FileExists("\\SERVER\SHARE\FILENAME.TXT")
If i = False Then
Main = DTSTaskExecResult_Failed
Exit Function
End If

Main = DTSTaskExecResult_Success

End Function

tpatten

ASKER

In both cases we are using the file scripting object. Off the top of your head do you know if this will conflict with Norton Utilities' Script Blocking as can happen in ASP?

tpatten

ASKER

nigel -- this blows up: set objFSys = nothing even if I make it this: set @objFSys = nothing

On the syntax chekc it says that "nothing" is not a column

EugeneZ

try this code:
----------------------------------------------------

create table #fileexists (
doesexist smallint,
fileindir smallint,
direxist smallint)

Insert into #fileexists exec master..xp_fileexist 'C:\last.track'

If exists (select doesexist from #fileexists F
where F.doesexist = 1)
Begin

INSERT TBL_Export_Tracking FROM 'C:\last.track' WITH (FIELDTERMINATOR = ',' ,ROWTERMINATOR = '\n')

Print 'File Does Exists and Running Package'
End
Else Begin
Print 'File Does Not Exists'
End

DROP TABLE #fileexists

nigelrivett

Sorry - some VB crept in

declare @objFSys int
declare @FileName varchar(128)
declare @i int

select @FileName = 'C:\myfile.txt'

exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
exec sp_OAMethod @objFSys, 'FileExists', @i out, @FileName
if @i = 1
print 'exists'
else
print 'not exists'
exec sp_OADestroy @objFSys

ASKER CERTIFIED SOLUTION

EugeneZ

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

tpatten

ASKER

Nigel and Eugene, I need to split the points somehow. I used both of your answers in one.

I think the key for me is the stored procedure 'xp_fileexist'. I like not having to directly use the FileSystemObject and instead using SQL procedures.

nigelrivett

xp_fileexists is an undocumented (hence unsupported) procedure. Saying that it's widely used so is probably fairly safe.
You could also use xp_cmdshell with a dir command into a temp table if you don't want to use FileSystemObject (quite a reasonable opinion).

Don't worry about the points it's not important.