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

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 713
  • Last Modified:

Deleting files through tsql

Need to detect the existence of a given filename and delete the file if it exists.
0
billy21
Asked:
billy21
1 Solution
 
Eugene ZCommented:
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
 
BillAn1Commented:
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
 
Eugene ZCommented:
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
arbertCommented:
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
 
Eugene ZCommented:
on sql server 2005 (Yukon) - you can write your own  Vb.Net \C#
 undocumented (:-))
Stored procedures with
FileSystemObject ...
0
 
arbertCommented:
There's a difference between "undocumented" as supplied by microsoft out of the box and "undocumented"  developed using built in tools/languages...
0
 
Eugene ZCommented:
if xp_fileexist is not in BOL does not mean it not documented...
it means:  it is undocumented in the BOL

0
 
arbertCommented:
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
 
Eugene ZCommented:
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
 
billy21Author Commented:
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
 
arbertCommented:
"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
 
Eugene ZCommented:
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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now