SQL 2005 how I can delete specific files between 2

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL 2005 how I can delete specific files between 2

Author

Topic

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-03-11 : 07:40:59

• how I can delete specific files using Stored Procedures.

Two Machines CO1 and CO2.

One (CO1) is Sql 2005 runing. Another (CO2) is Coding and files storing.

I want runing Stored procedure that time delete CO2 system files. How can it possible.

I am using following procedure

declare @ImagePath varchar(100)
DECLARE @cmd varchar(1000)
select @ImagePath= PhotoPath from UserPhotoAlbumInfo WHERE UserAlbumInfoId = @UserAlbumInfoId
SET @cmd = 'del "\\192.183.1.19\e$\webprojects\My\Files\Images\UserPhotoAlbum\' + @ImagePath +'"'
EXEC master..xp_cmdshell @cmd
This procedure is not working. because,that CO2 admin username,password is i know. but where its given???

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-03-11 : 07:42:59

You need to give file access to the path in mind.
Or see this blog post http://weblogs.sqlteam.com/tarad/archive/2008/08/25/How-to-run-a-process-using-different-credentials.aspx

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-11 : 08:47:33

enabling xp_cmdshell and giving file access are both possible security weaknesses - especially when combined.
You could achieve your aim quite easily using a vbs script file running under windows scheduler - it could run on a central server pointing to remote location, or run locally in target machine.
E.g. using FSO you can access a folder's filename, datemodified, deletefile, copyfile etc.

This way you can use Windows security to restrict access, it's faster, more secure, less complicated and puts no strain on SQL Server.

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-03-11 : 09:33:29

How can i do this method

Pls. help me.

quote:
Originally posted by darkdusky

enabling xp_cmdshell and giving file access are both possible security weaknesses - especially when combined.
You could achieve your aim quite easily using a vbs script file running under windows scheduler - it could run on a central server pointing to remote location, or run locally in target machine.
E.g. using FSO you can access a folder's filename, datemodified, deletefile, copyfile etc.

This way you can use Windows security to restrict access, it's faster, more secure, less complicated and puts no strain on SQL Server.

rvs.suresh@hotmail.com
Starting Member

12 Posts

Posted - 2009-03-12 : 06:45:59

I required file access, same time required well security.

quote:
Originally posted by Peso

You need to give file access to the path in mind.
Or see this blog post http://weblogs.sqlteam.com/tarad/archive/2008/08/25/How-to-run-a-process-using-different-credentials.aspx

E 12°55'05.63"
N 56°04'39.26"

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-03-12 : 07:43:45

You can create a vbscript file to query table using DMO and FileSystemObject to delete files etc.

Save this as "delfiles.vbs" - any name ending with .vbs will do. It can be scheduled using windows scheduler.

'Start of script
Option Explicit
'************************************************************
'USE DMO to retrieve value from table
'*************************************************************

' any used constants from the type library must be explicitly declared
Const SQLDMODataFile_TabDelimitedChar = 2
Const SQLDMOBCPDataFile_Char = 1

Dim oServer ' the SQL Server object
Dim oDatabase ' the target database to use
Dim strTableSQL
Dim num, strRowResult
Dim QueryResult
'Put SQL query here:
strTableSQL = "SELECT top 1 * from sys.all_columns"

Set oServer = CreateObject("SQLDMO.SQLServer")
oServer.LoginSecure = True
'Set instance name
oServer.Connect "MySQLInstanceName"
'set DB
Set oDatabase = oServer.Databases("MyDatabaseName")

Set QueryResult = oServer.ExecuteWithResults(strTableSQL)
'This can be used to build a list from different rows or columns
For num = 1 To QueryResult.Rows
strRowResult = QueryResult.GetColumnString(num, 1)
'Msgbox( strRowResult )
Next

oServer.DisConnect
'*****************************************************
'Finished DMO
'Use result of query to delete files
'*****************************************************
Dim FolderPath
Dim LastModifiedDate
Dim ErrorFound
Dim msg , tempstring

'location of file to be deleted - adds result from query to path
FolderPath = "C:\temp\test1\" & strRowResult

Dim FSO
Dim FolderFile , ThisFileName

On Error Resume Next
Set FSO = CreateObject("Scripting.FileSystemObject").GetFolder(FolderPath)
Set fsoc = CreateObject("Scripting.FileSystemObject")

For Each FolderFile In FSO.Files
With FolderFile
ThisFileName = .Name
LastModifiedDate =DateValue(.DateLastModified)
'Msgbox(ThisFileName & " " & LastModifiedDate )
'Check each file's properties:
'If need to check file dates or extension e.g.".doc" etc
' If (LastModifiedDate < now()-5) AND right(ThisFileName,4) = ".doc" Then
'fsoc.deletefile FolderPath & "\" & ThisFileName, TRUE
' End If
'If need to delete everything in the folder
fsoc.deletefile FolderPath & "\*.*" , TRUE
End With

Next
Set FSO = Nothing
Set FolderFile = Nothing
Set FolderSubFolder = Nothing

Set fsoc = Nothing

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources