I want delete a file from the NTFS file system (assuming the right permissions are given) using the script or any other way. But I need to schedule a job which reads some table in SQL server and determines which files needs to be deleted and then I need to phyiscally delete these files.
Please can anybody help me putting me in the right direction
Thanks in advance.
when I try to delete a scheduled job I just get an error. I'm using the administrator account.
Any ideas?
We are not certain if this has happened due to the SSIS FTP Task, but incidently the Excel file that is being copied from the FTP site using an SSIS FTP Task got corrupted a couple hours after the package was scheduled as a SQL Server Agent Job on SQL Server 2005.
I had a SQL Server 2000 DTS package doing the same thing, but it was never an issue then. I was using the FTP Task there along with an Excel Data source in that and has been working for a couple years atleast with never any corruption related issues.
In the SSIS SQL Server 2005 package I am using an FTP Task with an Excel Connection Manager and Excel source and the Excel file got corrupted within a couple hours of the package being scheduled as a SQL Server Agent job.
Has anyone experienced this issue? Any inputs will be appreciated.
Just as an fyi, the excel file has a lot of vlookups.
Thanks,
MShah
In a server we had File Growth,And then We had to Add New Hard Drive And New File On It.And Now We have New server with a Huge Hard Drive.But all files remaind.Can I Reduce This files to One data file or not ?
View 3 Replies View RelatedI have a table in my database on SQL Server which holds a file namethat refers to a file that is stored on the server. I would like tocreate a trigger to delete this file from the server if the row in thetable is deleted. I have been trying to use this command in a trigger(<filename> is the name and path of the file):xp_cmdshell "delete <filename>"If some one could please help I would appreciate it very much. Iwould love a code sample if you have one. Thank you so very much.From,Ryan
View 2 Replies View RelatedIs there any idea regarding query to delete any operating system file from sql server.
Basically i want to delete temp files in tmp folder of Windows.
-----
sim
Hi,I have some database files (.MDF, .LDF,...) on the server. When I tryto delete them, the warning "Cannot delete file: There has been asharing violation. The source or destination file may be in use."appears.Since I am new to the environment I don't know where the files comefrom and where they might be used.Can anybody tell me what to do to delete those files?Thank you.
View 36 Replies View RelatedHi,
I have created a DTS job that contains one 'Execute SQL Task' job. This SQL task runs a cmd file. The cmd file runs a few windows commands and then runs a Micorosoft Access Macro. Once finished both access and the cmd screen close down.
If I open up the DTS job and execute it manually, it works fine (takes about 1/2 an hour to run). My problem is that when I schedule the DTS job, the job starts up at the correct time but it never actually starts running the cmd file and it gives no error. It says Executing until I actually stop the job manually.
The Job details are:
Type: Operating System Command [CmdExec]
Command: DTSRun /~Z0x2F3FF84472BB6E7FF356EB006BA1AEC62C95AB3BF506F3 4A241F228CE148AB09DBC66B8651A450B725E6C4E6A1D328E4 EC2F2C0F8E323F1C7D501FD5B8FD00E25656514AF2224407DB 1C569163CBE383A8E7D8BE4974A0911F5CEB
The DTS details are:
C:BatchesDTSrunofUpdatesqlpmi.cmd
The cmd program details are:
echo on
:Start
if not exist "c:appsCI_Databasespmipmiload.mdb" exit
rem cleanup unfinsihed runs
if exist "c:appsCI_Databasespmipmiload.mdb" if exist "c:appsCI_Databasespmipmiloadold.mdb" del "c:appsCI_Databasespmipmiloadold.mdb"
rem main file locked assume its being work on so don't run this job.
if exist "c:appsCI_Databasespmipmiload.ldb" exit
rem if a tmp exits then the last job never completed - continue to add to tmp file
if exist "c:appsCI_Databasespmipmiloadtmp.mdb" goto Load
copy "c:appsCI_Databasespmipmiload.mdb" "c:appsCI_Databasespmipmiloadtmp.mdb"
:Load
"C:Program Filesmicrosoft office 2003OFFICE11msaccess.exe" "c:appsCI_Databasespmipmiloadtmp.mdb"/x updatedata
rem Compact databases
"C:Program Filesmicrosoft office 2003OFFICE11msaccess.exe" "c:appsCI_Databasespmipmiloadtmp.mdb"/compact
ren "c:appsCI_Databasespmipmiload.mdb" "pmiloadold.mdb"
ren "c:appsCI_Databasespmipmiloadtmp.mdb" "pmiload.mdb"
if exist "c:appsCI_Databasespmipmiload.mdb" if exist "c:appsCI_Databasespmipmiloadold.mdb" del "c:appsCI_Databasespmipmiloadold.mdb"
All help is greatly appreciated as this has been bugging me for some time now.
Thanks
Sam
Currently we has a database of size about 300G. Because our backup system failed some time past we were left with a transaction log file which grew to about 160G. However our backups are working again and everything is working fine. My understanding is that now the transaction log file is practically empty but the capacity remains at 160G.
When you delete records the deleted transactions are going to get logged to the transaction file. My understanding is when a backup is done these transactions get discarded out of the transaction file.
could I make use of this relatively large transaction file and start deleting transactions without out actually adding to the transaction file size.
The plan is to delete records from logging tables that are not referenced to by any other table without this increasing the transaction log file.For example over a period of a few weeks we can delete a chunk of records from a table. Then after it has completed a backup we can delete another chunk of records out of this table until we have got the table down to the records that we now need.Will this work?
I have an SSIS package that I created that looks like the following:
Execute SQL Task [Determines the file name]
Data Flow Task
Source - Query [Grabs the data]
Destination [Saves data to file on network drive]
Send Mail Task [Tells user to get file from network]
When I run this package inside Microsoft
Visual Studio I get the results I want. It sends the file to the
network drive and because there are SSN's in the file, it doesn't
actually e-mail the file but puts it in a secure location for the
person to get the file.
When I schedule this in SQL Server 2005 I get the file saved to: c:windowssystem32dynamicfilename.csv
So somehow it is still able to determine what the file name should be, but instead of sending the file to the right destination when you run it through VS, it flops out and sends it to this wacky directory.
I've
spent too much time on this and was wondering if someone can point me
in the right direction without going to the extent of writing a script
like this suggests. thread1555-1265108
Thanks,
Keith
Recently converted from 7 to sql 2k.
Running NT 4.0, sp6a. and sql 2k. I have a DTS job that works just fine if i go to 'design view' and then 'execute' it. But if I schedule it, the following error appears.. My question is... why? Also I modified the path from the current below (denapp02cmc$cd01.txt to the local path of the server as well... still came up with the same problem. ?? TIA! ::
DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: Copy Data from Results to denapp02cmc$cd01.txt Step DTSRun OnError: Copy Data from Results to denapp02cmc$cd01.txt Step, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied.
I'm using SSMS 2012. I have multiple queries that are run daily(I also have a few that are run every Monday) that I want to schedule to run at midnight and export to a new xls file on the network drive to be used the next morning. Is there something that I can add to each query to accomplish this or would I have to set something else up to call the queries?
View 3 Replies View RelatedI have an existing MS SQL database (2008 R2). I have a very simple SQL script. I need to run this script on a daily basis and save the results as a .csv file.
I've tried creating a job in SQL Manager - that didn't work. Then I found a tutorial that had me use MS Visual Studio... couldn't get that to function either.
what program I should be using, and how to go about creating this as a task / job that can run on a scheduled basis?
I have created a File System task which is contained in a Foreach Loop Container. I have .bak files that are populating a directory from a maintenance backup plan.
There is a point where I need to delete the .bak file's after I've zipped them all up.
How do I set the SourceVariable to read through the directory and pick up just the .bak file's in the directory to delete.
I have some simple files but they are failing because the delete history task is failing as it is looking for files in a non existent directory.
It is looking for files in C:Program FilesMicrosoft SQL ServerMSSQL10_50.INSTANCEMSSQLLog whereas it should be looking in C:Program FilesMicrosoft SQL ServerMSSQL10_50.MSSQLSERVERMSSQLLog
how I can get this corrected so I can get the Maintenance Plans to run correctly.
I have tried deleting and recreating the Plan but to no avail
Hi,
I have by mistake created a file by name abc.ndf on Primary filegroup on a database.
Now when I delete it it says can't delete because file is not empty.
I want to get rid of this file.
Any suggestion.
Our server is getting pretty full, the data has already been backed up, but it did not create additional room on the server. Can I delete the log file as long as I have backed up the items I need? We are running Microsoft SQL 2000.
Thanks for the help!
Can anyone point me to an example or tell me. How to run a job that delete's a file from a directory on a server? A have a ftp process that send's a larger flat file to my sql server every hour, which after about 4hr will full my server. I need to delete this file from its d drive location.
Thanks Reggie
Hello
We have a SQL Server 2000 database with 2 transaction log files.
The 2nd file was created when we were running out of disk space and the person creating it was not familiar with the dbcc shrink command.
I now want to get rid of the 2nd log file. I ran the following steps with no success:
DBCC SHRINKFILE ('Log_file', EMPTYFILE )
--Message: Cannot shrink log file 3 (log_file) because all logical log files are in use.
ALTER DATABASE db1 REMOVE FILE 'Log_file'
--Message: The file 'Log_file' cannot be removed because it is not empty.
There are no users or open transactions in the database. I have also tried sp_detach_db and sp_attach_single_file_db but that does not work either as the database attaches both the transaction logs back.
Please advise.
Thanks
Nina
Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.
how to delete a very huge log file, to free up some harddisk space
View 3 Replies View Relatedhi...
how to delete text files from c drive (ex: c:sale300407.txt) using ms sql express edition 2005 ?
plz help me...
I've inherited a procedure that performs a few transforms and writes to flat files in a working directory using a connection in connection manager.
After the manipulation, the system cleans up by deleting the working files. Works fine in development, but from the command line gives:
The process cannot access the file <filename> because it is being used by another process.".
I suspect this is because connection manager still has the file open.
Any ideas?
Thanks
Guy
Hi,
I've created a few audit trace sessions and each of them is producing files.
The procedure I used is listed below:
IF EXISTS (SELECT 1 FROM sysobjects WHERE [name] = 'sp_Turn_Audit_On' AND type = 'P')
DROP PROC sp_Turn_Audit_On
GO
CREATE proc sp_Turn_Audit_On
as
/************************************************** **/
/* Created by: SQL Profiler */
/* Date: 11/15/2006 05:16:40 PM */
/************************************************** **/
-- Create a Queue
declare @rc int
declare @TraceID int
declare @maxfilesize bigint
set @maxfilesize = 1024
exec @rc = sp_trace_create @TraceID output, 2, N'E:Program FilesMicrosoft SQL ServerMSSQLTraceAudit_Info.trc', @maxfilesize, NULL
if (@rc != 0) goto error
-- Client side File and Table cannot be scripted
-- Set the events
declare @on bit
set @on = 1
begin
exec sp_trace_setevent @TraceID, 14, 1, @on
exec sp_trace_setevent @TraceID, 14, 6, @on
exec sp_trace_setevent @TraceID, 14, 9, @on
exec sp_trace_setevent @TraceID, 14, 10, @on
exec sp_trace_setevent @TraceID, 14, 11, @on
exec sp_trace_setevent @TraceID, 14, 12, @on
exec sp_trace_setevent @TraceID, 14, 13, @on
exec sp_trace_setevent @TraceID, 14, 14, @on
exec sp_trace_setevent @TraceID, 14, 16, @on
exec sp_trace_setevent @TraceID, 14, 17, @on
exec sp_trace_setevent @TraceID, 14, 18, @on
exec sp_trace_setevent @TraceID, 15, 1, @on
exec sp_trace_setevent @TraceID, 15, 6, @on
exec sp_trace_setevent @TraceID, 15, 9, @on
exec sp_trace_setevent @TraceID, 15, 10, @on
exec sp_trace_setevent @TraceID, 15, 11, @on
exec sp_trace_setevent @TraceID, 15, 12, @on
exec sp_trace_setevent @TraceID, 15, 13, @on
exec sp_trace_setevent @TraceID, 15, 14, @on
exec sp_trace_setevent @TraceID, 15, 16, @on
exec sp_trace_setevent @TraceID, 15, 17, @on
exec sp_trace_setevent @TraceID, 15, 18, @on
exec sp_trace_setevent @TraceID, 20, 1, @on
exec sp_trace_setevent @TraceID, 20, 6, @on
exec sp_trace_setevent @TraceID, 20, 9, @on
exec sp_trace_setevent @TraceID, 20, 10, @on
exec sp_trace_setevent @TraceID, 20, 11, @on
exec sp_trace_setevent @TraceID, 20, 12, @on
exec sp_trace_setevent @TraceID, 20, 13, @on
exec sp_trace_setevent @TraceID, 20, 14, @on
exec sp_trace_setevent @TraceID, 20, 16, @on
exec sp_trace_setevent @TraceID, 20, 17, @on
exec sp_trace_setevent @TraceID, 20, 18, @on
exec sp_trace_setevent @TraceID, 37, 1, @on
exec sp_trace_setevent @TraceID, 37, 6, @on
exec sp_trace_setevent @TraceID, 37, 9, @on
exec sp_trace_setevent @TraceID, 37, 10, @on
exec sp_trace_setevent @TraceID, 37, 11, @on
exec sp_trace_setevent @TraceID, 37, 12, @on
exec sp_trace_setevent @TraceID, 37, 13, @on
exec sp_trace_setevent @TraceID, 37, 14, @on
exec sp_trace_setevent @TraceID, 37, 16, @on
exec sp_trace_setevent @TraceID, 37, 17, @on
exec sp_trace_setevent @TraceID, 37, 18, @on
exec sp_trace_setevent @TraceID, 46, 1, @on
exec sp_trace_setevent @TraceID, 46, 6, @on
exec sp_trace_setevent @TraceID, 46, 9, @on
exec sp_trace_setevent @TraceID, 46, 10, @on
exec sp_trace_setevent @TraceID, 46, 11, @on
exec sp_trace_setevent @TraceID, 46, 12, @on
exec sp_trace_setevent @TraceID, 46, 13, @on
exec sp_trace_setevent @TraceID, 46, 14, @on
exec sp_trace_setevent @TraceID, 46, 16, @on
exec sp_trace_setevent @TraceID, 46, 17, @on
exec sp_trace_setevent @TraceID, 46, 18, @on
exec sp_trace_setevent @TraceID, 47, 1, @on
exec sp_trace_setevent @TraceID, 47, 6, @on
exec sp_trace_setevent @TraceID, 47, 9, @on
exec sp_trace_setevent @TraceID, 47, 10, @on
exec sp_trace_setevent @TraceID, 47, 11, @on
exec sp_trace_setevent @TraceID, 47, 12, @on
exec sp_trace_setevent @TraceID, 47, 13, @on
exec sp_trace_setevent @TraceID, 47, 14, @on
exec sp_trace_setevent @TraceID, 47, 16, @on
exec sp_trace_setevent @TraceID, 47, 17, @on
exec sp_trace_setevent @TraceID, 47, 18, @on
exec sp_trace_setevent @TraceID, 104, 1, @on
exec sp_trace_setevent @TraceID, 104, 6, @on
exec sp_trace_setevent @TraceID, 104, 9, @on
exec sp_trace_setevent @TraceID, 104, 10, @on
exec sp_trace_setevent @TraceID, 104, 11, @on
exec sp_trace_setevent @TraceID, 104, 12, @on
exec sp_trace_setevent @TraceID, 104, 13, @on
exec sp_trace_setevent @TraceID, 104, 14, @on
exec sp_trace_setevent @TraceID, 104, 16, @on
exec sp_trace_setevent @TraceID, 104, 17, @on
exec sp_trace_setevent @TraceID, 104, 18, @on
exec sp_trace_setevent @TraceID, 107, 1, @on
exec sp_trace_setevent @TraceID, 107, 6, @on
exec sp_trace_setevent @TraceID, 107, 9, @on
exec sp_trace_setevent @TraceID, 107, 10, @on
exec sp_trace_setevent @TraceID, 107, 11, @on
exec sp_trace_setevent @TraceID, 107, 12, @on
exec sp_trace_setevent @TraceID, 107, 13, @on
exec sp_trace_setevent @TraceID, 107, 14, @on
exec sp_trace_setevent @TraceID, 107, 16, @on
exec sp_trace_setevent @TraceID, 107, 17, @on
exec sp_trace_setevent @TraceID, 107, 18, @on
exec sp_trace_setevent @TraceID, 109, 1, @on
exec sp_trace_setevent @TraceID, 109, 6, @on
exec sp_trace_setevent @TraceID, 109, 9, @on
exec sp_trace_setevent @TraceID, 109, 10, @on
exec sp_trace_setevent @TraceID, 109, 11, @on
exec sp_trace_setevent @TraceID, 109, 12, @on
exec sp_trace_setevent @TraceID, 109, 13, @on
exec sp_trace_setevent @TraceID, 109, 14, @on
exec sp_trace_setevent @TraceID, 109, 16, @on
exec sp_trace_setevent @TraceID, 109, 17, @on
exec sp_trace_setevent @TraceID, 109, 18, @on
exec sp_trace_setevent @TraceID, 110, 1, @on
exec sp_trace_setevent @TraceID, 110, 6, @on
exec sp_trace_setevent @TraceID, 110, 9, @on
exec sp_trace_setevent @TraceID, 110, 10, @on
exec sp_trace_setevent @TraceID, 110, 11, @on
exec sp_trace_setevent @TraceID, 110, 12, @on
exec sp_trace_setevent @TraceID, 110, 13, @on
exec sp_trace_setevent @TraceID, 110, 14, @on
exec sp_trace_setevent @TraceID, 110, 16, @on
exec sp_trace_setevent @TraceID, 110, 17, @on
exec sp_trace_setevent @TraceID, 110, 18, @on
exec sp_trace_setevent @TraceID, 115, 1, @on
exec sp_trace_setevent @TraceID, 115, 6, @on
exec sp_trace_setevent @TraceID, 115, 9, @on
exec sp_trace_setevent @TraceID, 115, 10, @on
exec sp_trace_setevent @TraceID, 115, 11, @on
exec sp_trace_setevent @TraceID, 115, 12, @on
exec sp_trace_setevent @TraceID, 115, 13, @on
exec sp_trace_setevent @TraceID, 115, 14, @on
exec sp_trace_setevent @TraceID, 115, 16, @on
exec sp_trace_setevent @TraceID, 115, 17, @on
exec sp_trace_setevent @TraceID, 115, 18, @on
-- Set the Filters
declare @intfilter int
declare @bigintfilter bigint
exec sp_trace_setfilter @TraceID, 10, 0, 7, N'SQL Profiler'
-- Set the trace status to start
exec sp_trace_setstatus @TraceID, 1
-- display trace id for future references
select TraceID=@TraceID
goto noCursor
error:
select ErrorCode=@rc
noCursor:
return
end
Unfortunately, I didn't mention the @stoptime for each of the trace sessions and now I'm unable to delete the trace files. Is there any select statement that will be useful in finding the @traceid for these auditting sessions? Also, how am I able to stop the sessions without stopping the related services?
Thank you in advance.
Best regards,
Alla
I have to run a DTS package to export data into a dBase file. I first have to delete the existing data within the dbase file, but I can only get it to delete the data, not the actual rows. The file continues to grow in size. Is there a way to delete the rows from the dbase file through the DTS package?
This would help out a lot, thank you!!
I am trying to restore a DB. After Detaching the DB I wanna delete the Ldf . How to delete this physical file Using Sql Query????
Thank You in advance.
i have a text file that is like:date = OCT0606asdfsdafasdfasdgsdghasdfsdfasdgSTART-OF-DATAasdfasdfgasdfgdfgsfgsadfsdfgsaasdfgsdfgEND-OF-DATAasdfgalsdkdfklmlkmasdfgasdfgi need to clear everything from this file except the data between theSTART-OF-DATA and END-OF-DATA using a batcj file... elternitavly i amopen to suggestions of how to import using bulk insert in sql withoutchanging the file at all. data is pipe seperated but obvioulsy hasplenty of junk data in it. i have 2 similar files at about 30mb and60mb in size. thnks everyone
View 15 Replies View RelatedI have a file in my script task, which does this job.
Public Sub Main()
Dim fs As New FileStream("\abcdefgabcdergFiles2.ftp", FileMode.Create, FileAccess.Write)
Dim s As New StreamWriter(fs)
s.BaseStream.Seek(0, SeekOrigin.End)
s.WriteLine("open 112.123.123.12")
s.WriteLine("abcdef")
s.WriteLine("abcd")
s.Close()
Dts.TaskResult = Dts.Results.Success
End Sub
After this task gets completed, I want to delete this file-the file which was created just now. How to write that code to delete that file. I am not sure, if this is the right place to put this issue.
Thanks.
Hi,
I want to delete all files in a folder where creation date is an year older. I created a For Each Loop container to go over the whole folder and then execute the following Script Task
Dim FileArchive As Int16
Dim dteArchive As Date
Dim FilePath As String
FileArchive = CType(Dts.Variables("FileArchive").Value, Int16) ' One Year FileArchive = 365
dteArchive = DateAdd("d", -FileArchive, Now.Date)
FilePath = Dts.Variables("FilePath").Value.ToString
If File.GetCreationTime(FilePath) <= dteArchive Then
Try
File.Delete(FilePath)
Catch ex As Exception
'Do Nothing
End Try
End If
Dts.TaskResult = Dts.Results.Success
Is there a better approach to this? As most of the time job may have to delete 10 or 15 files but he has to read each file and then check the date if it matches the criteria and it delete.
I have to load and unload many datatabases for testing. So I wanted to quickly remove a database, its devices and respective dat files
all at once.
Is there a way of completely dropping a database so that all trace of it is quickly gone?
How do I delete records in the destination file in SSIS using BI Development Studio?
View 1 Replies View RelatedHow do I delete records in the destination file in SSIS using BI Development
Studio? Thanks.
I encounter one weird problem, I have a database with around 7 GB ...when I delete a bunch of data from it, it suppose to reduce thedatabase file size, but weirdly, the file size increase to 8 GB.Wondering why. Is it suppose to be like that?Is it the architecture is designed to work like that?Is there any way for me to reduce the database file size?Thanks.Peter CCH
View 2 Replies View Related