Solved

Deleting files through tsql

Posted on 2004-09-15
12
677 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 34

Expert Comment

by:arbert
Comment Utility
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
Comment Utility
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
Comment Utility
There's a difference between "undocumented" as supplied by microsoft out of the box and "undocumented"  developed using built in tools/languages...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 42

Expert Comment

by:EugeneZ
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
"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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

I wrote this interesting script that really help me find jobs or procedures when working in a huge environment. I could I have written it as a Procedure but then I would have to have it on each machine or have a link to a server-related search that …
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

771 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now