?
Solved

SQL2K: BULK INSERT: Check if files exists

Posted on 2003-03-07
9
Medium Priority
?
575 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 
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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Viewers will learn how the fundamental information of how to create a table.
Suggested Courses

752 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