Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

SQL2K: BULK INSERT: Check if files exists

Posted on 2003-03-07
9
Medium Priority
?
600 Views
Last Modified: 2007-12-19
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?
0
Comment
Question by:tpatten
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8090549
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
0
 
LVL 3

Expert Comment

by:Frostbyte_Zero
ID: 8090554
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
0
 
LVL 1

Author Comment

by:tpatten
ID: 8090578
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?
0
NEW Veeam Backup for Microsoft Office 365 1.5

With Office 365, it’s your data and your responsibility to protect it. NEW Veeam Backup for Microsoft Office 365 eliminates the risk of losing access to your Office 365 data.

 
LVL 1

Author Comment

by:tpatten
ID: 8090643
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
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 8090650
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


0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8090678
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
0
 
LVL 43

Accepted Solution

by:
Eugene Z earned 500 total points
ID: 8090684
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


0
 
LVL 1

Author Comment

by:tpatten
ID: 8090755
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.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 8101439
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.
0

Featured Post

Receive 1:1 tech help

Solve your biggest tech problems alongside global tech experts with 1:1 help.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question