Solved

Deleting files through tsql

Posted on 2004-09-15
12
690 Views
Last Modified: 2008-01-09
Need to detect the existence of a given filename and delete the file if it exists.
0
Comment
Question by:billy21
[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
12 Comments
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12065057
example:
-- using xp_fileexists
declare      @Path varchar(128) ,
      @FileName varchar(128)
      select      @Path = 'C:\' ,
            @FileName = 'myfile.txt'

declare @i int
declare      @File varchar(1000)
      
      select @File = @Path + @FileName
      exec master..xp_fileexist @File, @i out
      if @i = 1
            print 'exists'
                      set @File='del ' + @File
                       exec master..cmdshell @File
      else
            print 'not exists'


0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 500 total points
ID: 12065061
you can use xp_cmdshell to basically use dos commands dir & del.
Of course, if you don't need to , you can do the del without a check - if the file's not there, no problem

create table #temptable (output varchar(255))
insert into #temptable exec master..xp_cmdshell 'dir c:\'
if exists (select * from #temptable where output like '%MyFileName%')
exec master..xp_cmdshell 'del MyFileName'
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12065175
try this:


-- using the scripting object
declare      @Path varchar(128) ,
      @FileName varchar(128)
      select      @Path = 'C:\' ,
            @FileName = 'myfile.txt'

declare      @objFSys int
declare @i int
declare      @File varchar(1000)
      
      select @File = @Path + @FileName
      exec sp_OACreate 'Scripting.FileSystemObject', @objFSys out
      exec sp_OAMethod @objFSys, 'FileExists', @i out, @File
      if @i = 1
          begin
            print 'exists'
                EXEC  sp_OAMethod @objFSys, 'DeleteFile',   NULL, @File
          end
      else
            print 'not exists'
      exec sp_OADestroy @objFSys
---------------------------------------------------------------------------------------------
--------------------------------------------------------------------------------------------      
-- using xp_cmdshell
-- using xp_fileexists
declare     @Path varchar(128) ,
     @FileName varchar(128)
     select     @Path = 'C:\' ,
          @FileName = 'myfile.txt'

declare @i int
declare     @File varchar(1000)
     
     select @File = @Path + @FileName
     exec master..xp_fileexist @File, @i out
     if @i = 1
       BEGIN
          print 'exists'
                      set @File='del ' + @File
                     exec master..xp_cmdshell @File
           END
     else
          print 'not exists'
0
Major Incident Management Communications

Major incidents and IT service outages cost companies millions. Often the solution to minimizing damage is automated communication. Find out more in our Major Incident Management Communications infographic.

 
LVL 34

Expert Comment

by:arbert
ID: 12066279
I tend to use the DIR method that BillAn1 suggests.  xp_fileexist is an undocumented stored proc that might not be around in future versions of SQL Server....
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12067011
on sql server 2005 (Yukon) - you can write your own  Vb.Net \C#
 undocumented (:-))
Stored procedures with
FileSystemObject ...
0
 
LVL 34

Expert Comment

by:arbert
ID: 12067158
There's a difference between "undocumented" as supplied by microsoft out of the box and "undocumented"  developed using built in tools/languages...
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12067644
if xp_fileexist is not in BOL does not mean it not documented...
it means:  it is undocumented in the BOL

0
 
LVL 34

Expert Comment

by:arbert
ID: 12068917
Yes, I realize that--thanks for the pointer...Search "xp_fileexist" in google and see how many sites (outside of books online) list it as undocumented....

Plus, from working with microsoft, I know it's considered an undocumented function...
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12073750
arbret:

We already had this argument in past but looks like nothing changed...
Brett Archer you are giving good help to this forum but please do not confuse people
about documented – use – undocumented – do not use ideas
It is wrong way to explain professionals what to use or what do not  

But I try again- not for you -  for people who took your advice in this case:

1. If a procedure is undocumented (meant for internal use only)  /from Microsoft/

 If you do not know how to use anything - even it is documented - recommendation do not use!

It means just for experienced\with appropriate permissions  users such activities allowed – for example such  as using any objects from Master DB.
 
You told one of other reasons do not use  xp_fileexist is an undocumented stored proc that might not be around in future versions of SQL Server....
Answer:
Now we are talking about MS SQL Servers 2000
And it is feature of the product and to avoid such functionalities, if you can use them, is  not very wise.


BTW: recommended to delete all extended stored procedures documented  and undocumented in BOL that can compromise security from unauthorized users
XP_CMDSHELL one of them .

But if you have basic DBA knowledge – you will find way (Grant\revoke permissions to such objects) to keep and use these very helpful SP’s (at least xp_fileexists)


 

There are list of trusted by WW SQL server community web sites
That provide documentation about “Undocumented” futures of SQL SERVER 2000
With information from people more then 13years of information technology experience and big respect  (from EE too)


http://www.mssqlcity.com/FAQ/Devel/FileExists.htm
http://www.sql-server-performance.com/ac_sql_server_2000_undocumented_sp.asp
http://www.databasejournal.com/features/mssql/article.php/1439771
http://vyaskn.tripod.com/sp_readerrorlog_undocumented.htm
http://www.extremeexperts.com/sql/articles/DBCCTraceFlags.aspx
http://www.extremeexperts.com/sql/articles/ListDBCCCommands.aspx
http://www.winnetmag.com/Article/ArticleID/21046/21046.html
http://www.sqlservercentral.com/columnists/RDyess/traceflags_printversion.asp
the undocumented Forceload option on sqlmaint.exe from
http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/logship2.mspx
etc. etc.

Finally I say again:

If you do not know how to use something - even it is documented - recommendation do not use or ask professionals – professionals who know how to use it !

P.S. All my word above are not personal attack against this forum member – it is just about – opening mind and think not standard way (do not jump from bridge even there is sign –‘ Jump’) and decide what is better for you not for me or arbret: – just for you and for your business – everything in your brain .

Good luck.



0
 
LVL 6

Author Comment

by:billy21
ID: 12073782
I had already made my mind up long ago to not use undocumented Procs.  I want to be as close to forward compatible as possible.  Also, undocumented doesn't just mean MS might decide to drop it from the next major realease (ie. Yukon) it also means it could be dropped in the next service pack.  I didn't know xp_fileexist was undocumented before Arbert stated so and that information alone made me decide not to use xp_fileexist.
0
 
LVL 34

Expert Comment

by:arbert
ID: 12075123
"about documented – use – undocumented – do not use ideas
It is wrong way to explain professionals what to use or what do not  "

I think it's also wrong to simply post "Try this:" and not explain the risks to an asker.  "Hey, you can use this proc--by the way, it may not work if you apply a service pack"......You better send an email to wish@microsoft.com and tell them to change their documentation telling people to use undocumented functions at their own risk then....

Just because you can find documentation on the internet to build a pipe bomb doesn't mean you should do it....
0
 
LVL 43

Expert Comment

by:Eugene Z
ID: 12075756
arbret:
"Try this" - means try in test environment first..

What risk of xp_fileexist - you can not even say - just use stamps
I can say - I tested and working very good!

BTW: if you have ever work with Registry - MC does not recommend to do it as well and much more

There are not just black and white - many colors around - they are very good.

Many-many years ago more then 13 years - no help or very bad help was around of IT world - and we survived somehow!

P.S. part with pipe bomb - was not good example

billy21:
Right now we are talking about sql server 2000 functionalities - 2010 2100, etc - will be absolutelly different  products
e.g. xp_fileexist was on sql server7 and next sql server SP will not drop extended SP's just add new ones

--------------
that is it for this particular issue
Let's back to work and free (good or bad) help on this forum without personal  stuff on EE

P@L V





0

Featured Post

Transaction Monitoring Vs. Real User Monitoring

Synthetic Transaction Monitoring Vs. Real User Monitoring: When To Use Each Approach? In this article, we will discuss two major monitoring approaches: Synthetic Transaction and Real User Monitoring.

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.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

707 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