Solved

Deleting files through tsql

Posted on 2004-09-15
12
682 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
12 Comments
 
LVL 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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 42

Expert Comment

by:EugeneZ
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

For both online and offline retail, the cross-channel business is the most recent pattern in the B2C trade space.
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties

806 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