March 4, 2009 at 12:55 pm
I am trying to create a CLR stored procedure with a parameter for a network file path. The purpose of the procedure is to check the last modified date of a backup file and if it is more than X days old, it will delete the file.
However, when i test the script below on the local drives it works fine (C:\TestFiles\DeleteMe.txt), but when I try to test on a network from using a UNC path (\\remotePC\TestFiles\DeleteMe.txt), I get nothing. It doesn't delete any files or even find the files if I switch the code around to see if the file exists.
Has anyone found a way around this or have any other suggestions as to how to accomplish the same task?
Thanks!
_
Public Shared Sub uspCLR_FileExists _
(ByVal iFilePath As String, _
ByVal iMode As String, _
ByRef oResult As SqlDateTime)
Try
Dim lastwrite As DateTime
If iMode = "File" Then
Dim fi As New FileInfo(iFilePath)
lastwrite = fi.LastWriteTime
If lastwrite < DateAdd(DateInterval.Day, -5, Now) Then
fi.Delete()
End If
End If
Catch ex As Exception
Dim sp As SqlPipe = SqlContext.Pipe
sp.Send(ex.Message)
End Try
End Sub
March 4, 2009 at 1:49 pm
- has your sproc been granted external access ?
- does the service account have delete authority on that unc pointed fileshare ?
You might as well use a vbscript in a sqlagent job.
The script might look like this:
' vbs
'Input parameters
' 0= strBuFolder (use doubble quotes !!)
' 1 = RetentionDays (integer)
Dim strBuFolder, intRetentionDays
Dim obArgs
DIM RetentionDays
Set obArgs = WScript.Arguments
strBuFolder = obArgs.Item(0)
RetentionDays = cLng(obArgs.Item(1))
DIM JJJJMMDD, dag , maand , JJJJMMDDvgl, Dagen, Vandaag
dag = day(date)
maand = month(date)
if dag < 10 then
dag = "0" & dag
end if
if maand < 10 then
maand = "0" & maand
end if
Vandaag = year(date) & maand & dag
strComputer = "."
Set objWMIService = GetObject("winmgmts:" _
& "{impersonationLevel=impersonate}!\\" & strComputer & "\root\cimv2")
Set FileList = objWMIService.ExecQuery _
("ASSOCIATORS OF {Win32_Directory.Name='" & strBuFolder & "'} Where " _
& "ResultClass = CIM_DataFile")
For Each objFile In FileList
If LCase(objFile.Extension) = "bak" then
if isnumeric( Right(objFile.filename,8) ) then
if LCase(Left(Right(objFile.filename,12),3)) = "log" then
JJJJMMDDvgl = Right(objFile.filename,8)
Dagen = CLng(Vandaag) - RetentionDays
if CLng(JJJJMMDDvgl) < CLng(Dagen) then
strNewName = objFile.Drive & objFile.Path & objFile.FileName & "." & objFile.Extension
objFile.Delete strNewName, force
end if
end if
end if
if LCase(Right(objFile.filename,3)) = "log" Then
JJJJMMDD = Left(objFile.CreationDate,8)
strNewName = objFile.Drive & objFile.Path & objFile.FileName & "_" & JJJJMMDD & "." & objFile.Extension
errResult = objFile.Rename(strNewName)
end if
End If
Next
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply