Solved

Deleting files through tsql

Posted on 2004-09-15
12
687 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
How Do You Stack Up Against Your Peers?

With today’s modern enterprise so dependent on digital infrastructures, the impact of major incidents has increased dramatically. Grab the report now to gain insight into how your organization ranks against your peers and learn best-in-class strategies to resolve incidents.

 
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

Comprehensive Backup Solutions for Microsoft

Acronis protects the complete Microsoft technology stack: Windows Server, Windows PC, laptop and Surface data; Microsoft business applications; Microsoft Hyper-V; Azure VMs; Microsoft Windows Server 2016; Microsoft Exchange 2016 and SQL Server 2016.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Question about Common Table Expressions 3 45
What is needed to become a DBA? 7 53
SQL Query 9 27
SQL Server Reports (SSRS 2014) - Reports Timing Out 11 115
Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Recently we ran in to an issue while running some SQL jobs where we were trying to process the cubes.  We got an error saying failure stating 'NT SERVICE\SQLSERVERAGENT does not have access to Analysis Services. So this is a way to automate that wit…
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

734 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