|
database
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
Check if file exists then delete itI am almost done with my HUGE stored procedure and iowe it mostly to
contributions from all of you guys, THANKS The last part of the T-SQL coding is I need to check for a file in the server , delete it before I attempt to re-create it with new data for the next day, any code for this ? thanks all mdscorp wrote:
> The last part of the T-SQL coding is I need to check for a file in the mdscorp,> server , delete it before I attempt to re-create it with new data for > the next day, any code for this ? You said, 'check for a file in the server'. What sort of file? Or do you mean table. If you mean table then you need this script in your stored procedure: if exists (select * from sysobjects where id = object_id(N'[dbo].[<table name>]') and OBJECTPROPERTY(id, N'IsUserTable') = 1) drop table [dbo].[<table name>] CREATE TABLE dbo.[<table name>] ... -Tav.- Tavis Pitt Tav wrote:
Show quote > mdscorp wrote: Sorry, this store procedure creates a flat ascii file, before it> > The last part of the T-SQL coding is I need to check for a file in the > > server , delete it before I attempt to re-create it with new data for > > the next day, any code for this ? > > mdscorp, > > You said, 'check for a file in the server'. What sort of file? Or do > you mean table. If you mean table then you need this script in your > stored procedure: > > if exists (select * from sysobjects where id = > object_id(N'[dbo].[<table name>]') and OBJECTPROPERTY(id, > N'IsUserTable') = 1) > drop table [dbo].[<table name>] > > CREATE TABLE dbo.[<table name>] > ... > > > -Tav.- > > Tavis Pitt creates it I must check if already exists, delete it then create it with new data. take a look at xp_fileexist
master..xp_fileexist 'C:\Program Files\Microsoft SQL Server\MSSQL\readme.txt' This is undocumented so be careful Denis the SQL Menace http://sqlservercode.blogspot.com/ mdscorp wrote: Show quote > I am almost done with my HUGE stored procedure and iowe it mostly to > contributions from all of you guys, THANKS > The last part of the T-SQL coding is I need to check for a file in the > server , delete it before I attempt to re-create it with new data for > the next day, any code for this ? > > thanks all SQL Menace wrote:
Show quote > take a look at xp_fileexist ok, this works to let me know if the file exists, how do i kill it or> > master..xp_fileexist 'C:\Program Files\Microsoft SQL > Server\MSSQL\readme.txt' > > This is undocumented so be careful > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > > mdscorp wrote: > > I am almost done with my HUGE stored procedure and iowe it mostly to > > contributions from all of you guys, THANKS > > The last part of the T-SQL coding is I need to check for a file in the > > server , delete it before I attempt to re-create it with new data for > > the next day, any code for this ? > > > > thanks all delete it or remove it ? assuming you have permissions
master..xp_cmdshell 'del c:\deletethis.txt' where c:\deletethis.txt' would be the path + filename Denis the SQL Menace http://sqlservercode.blogspot.com/ mdscorp wrote: Show quote > SQL Menace wrote: > > take a look at xp_fileexist > > > > master..xp_fileexist 'C:\Program Files\Microsoft SQL > > Server\MSSQL\readme.txt' > > > > This is undocumented so be careful > > > > Denis the SQL Menace > > http://sqlservercode.blogspot.com/ > > > > > > > > mdscorp wrote: > > > I am almost done with my HUGE stored procedure and iowe it mostly to > > > contributions from all of you guys, THANKS > > > The last part of the T-SQL coding is I need to check for a file in the > > > server , delete it before I attempt to re-create it with new data for > > > the next day, any code for this ? > > > > > > thanks all > > ok, this works to let me know if the file exists, how do i kill it or > delete it or remove it ? SQL Menace wrote:
Show quote > assuming you have permissions yes this wirks but i find the following problem.> > master..xp_cmdshell 'del c:\deletethis.txt' > > where c:\deletethis.txt' would be the path + filename > > Denis the SQL Menace > http://sqlservercode.blogspot.com/ > > > > mdscorp wrote: > > SQL Menace wrote: > > > take a look at xp_fileexist > > > > > > master..xp_fileexist 'C:\Program Files\Microsoft SQL > > > Server\MSSQL\readme.txt' > > > > > > This is undocumented so be careful > > > > > > Denis the SQL Menace > > > http://sqlservercode.blogspot.com/ > > > > > > > > > > > > mdscorp wrote: > > > > I am almost done with my HUGE stored procedure and iowe it mostly to > > > > contributions from all of you guys, THANKS > > > > The last part of the T-SQL coding is I need to check for a file in the > > > > server , delete it before I attempt to re-create it with new data for > > > > the next day, any code for this ? > > > > > > > > thanks all > > > > ok, this works to let me know if the file exists, how do i kill it or > > delete it or remove it ? the file created has a name that changes on a daily basis basically is a name plus the date looking something like this: "TextFile_07-14-06.txt" so I do SET @FileName = REPLACE('\\server\upload\TextFile_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-') and carry on but when ty to use the xp_cmdshell to delete it as in EXEC master..xp_FileExist @FileName, @FileExists out IF @FileExists =1 EXEC master..xp_cmdShell 'del ' + @FileName i get an error because the command for the xp_cmdshell to work must be embedded into single quotes all together... any workaround this ? > SET @FileName = How about,> REPLACE('\\server\upload\TextFile_'+CONVERT(char(8),GETDATE(),1)+'.txt','/','-') > > and carry on but when ty to use the xp_cmdshell to delete it as in > > EXEC master..xp_FileExist @FileName, @FileExists out > IF @FileExists =1 > EXEC master..xp_cmdShell 'del ' + @FileName > > i get an error because the command for the xp_cmdshell to work must be > embedded into single quotes all together... DECLARE @cmd VARCHAR(255); SET @cmd = 'del '+@FileName; .... IF @FileExists = 1 EXEC master..xp_cmdShell @cmd; If you need to verify a file system file, you could use osql, or you could
attempt to bcp the file into a #Temp table and check the error -bit klutzy, but could work 'down and dirty'. -- Show quoteArnie Rowland* "To be successful, your heart must accompany your knowledge." "mdscorp" <l***@mds-corp.com> wrote in message news:1152887151.104212.266790@b28g2000cwb.googlegroups.com... >I am almost done with my HUGE stored procedure and iowe it mostly to > contributions from all of you guys, THANKS > The last part of the T-SQL coding is I need to check for a file in the > server , delete it before I attempt to re-create it with new data for > the next day, any code for this ? > > thanks all >  |
|||||||||||||||||||||||
Other interesting topics