Return File name from path

Always handy - never there when you need it. A simple recursive function to return the file name from the full path


Private Function GetFilenameFromPath(ByVal strPath As String) As String
' Returns the rightmost characters of a string upto but not including the rightmost '\'
' e.g. 'c:\winnt\win.ini' returns 'win.ini'

If Right$(strPath, 1) <> "\" And Len(strPath) > 0 Then
GetFilenameFromPath = GetFilenameFromPath(Left$(strPath, Len(strPath) - 1)) + Right$(strPath, 1)
End If
End Function

Reporting Services Adding VB code / Getting user login whilst still allowing subscriptions

The User!UserID global allows you to determine the active directory user who is running your report. Very handy for customising and security. A big problem is that subscriptions will fail because report server cannot idetify the user from the subscription.

Here is a work around using a custom code function.

If you have really complex tasks - or ones like our problem - then VB code can be embedded in your report server report.

1. In Design view, right-click the design surface outside the border of the report and click Report Properties.
2. Click Code.
3. In Custom code, type the code.
4. Public Function UserName()
   Try
   Return Report.User!UserID
   Catch
   Return "System"
   End Try
   End Function

Now we need to make the user name available as a parameter in the report so we can use it in our underlying SQL.

1. Click on the Report Menu -> Parameters
2. Add a new parameter
3. In the default values use a non queried value of =Code.UserName() to call the custom code we entered above.
4. This particular parameter is needed to determine the list of Market parameters used in this report. It needs to be moved (using up and down arrows) above any parameters dependant on it - in this case Mkt.

The @UserName parameter is now available for us to build our SQL in the data section of the report.

Script to Delete Reporting Services Subscriptions

I wrote this script to delete all subscriptions against a reporting services report.

SQL 2000 offers fairly poor management tools for Reporting Services and I had a situation where automatically generated subscriptions were piling up on a 2000 box to the point where they could not be deleted manually.

To run - create a console program (note - console program) in Visual Studio and put the following code in a module. You will probably need to set up a few dependencies in the references.

I don't know if anyone will have as specific requirements as this but it might be a help if you are looking at programming against the reporting services object model.

Imports System
Imports System.Web.Services.Protocols
Imports RSManagement_Unsubscribe.ReportingServices

'Gerard Conlon 18 Sept 2008
'Program to delete all report subscriptions against a given ReportingServices report
'Written for compilition as a console application in Visual Studio


Module modRsManagement

   Sub Main()
       Dim strReportOwner As String
       Dim strReportPath As String

       Dim strParams() As String
       Dim i As Integer

       'the parameters are passed in the form of RSManagement_Unsubscribe.exe "ReportPath|ReportOwner
       'they are wrapped in double quotes and seperated by the pipe |
       'ie called from the command line as
       ' RSManagement_Unsubscribe.exe "/MyReportsDir/ReportName|MyServer\ASPNET"
       'ReportPath - full report path from the top level directory and including the report name
       'ReportOwner - usually the name of the server hosting reporting services and running under the ASPNET user ie MyServer\ASPNET"

       'Get the passed parameters
       If Len(Command()) > 0 Then
           strParams = Split(Mid$(Command(), 2, Len(Command()) - 2), "|")
           For i = 0 To UBound(strParams)
               If i = 0 Then strReportPath = strParams(0)
               If i = 1 Then strReportOwner = strParams(1)
           Next i
       End If


       'check to see if parameters are passed and if not give descriptive error
       If Trim(strReportPath) = "" Or Trim(strReportOwner) = "" Then
           Console.WriteLine("")
           Console.WriteLine("The Report Path and The Report owner must be specifed as parameters")
           Console.WriteLine("")
           Console.WriteLine("Parameters are seperated by the pipe char (|) and need to be in double quotes")
           Console.WriteLine("")
           Console.WriteLine("Example: RSManagement_Unsubscribe.exe " + """" + "/PrePostTimes/CMS Pretimes|AUS-DB1\ASPNET" + """")
           Console.WriteLine("")
           Console.WriteLine("Press any key to continue.......")
           Console.ReadLine() 'hold the screen open so message can be read

       Else
           Console.WriteLine(fDeleteSubscriptions(strReportPath, strReportOwner))

       End If

   End Sub


   Function fDeleteSubscriptions(ByVal sReport As String, ByVal sOwner As String) As String
       Dim rs As New ReportingService
       Dim subscriptions As Subscription() = Nothing
       Dim schedules As Schedule() = Nothing
       Dim i As Integer
       Dim cnt As Integer

       Console.WriteLine(" Searching for Subscriptions ..........")
       rs.Credentials = System.Net.CredentialCache.DefaultCredentials
       Console.WriteLine(sReport)
       Console.WriteLine(sOwner)
       rs.Timeout = 600000
       Try
           fDeleteSubscriptions = "0"
           cnt = 0
           Console.WriteLine("Deleting Subscription ..........")
           subscriptions = rs.ListSubscriptions(sReport, sOwner)
           'loop through and delete the subscriptions
           If subscriptions.GetLength(0) > 0 Then
               For i = 0 To subscriptions.GetLength(0) - 1
                   'rs.BeginListSchedules()
                   Console.WriteLine("Deleting " + subscriptions(i).Description)
                   rs.DeleteSubscription(subscriptions(i).SubscriptionID)

                   cnt = cnt + 1

               Next
               fDeleteSubscriptions = "Deleted " + CStr(cnt) + " Subscriptions"
           Else
               fDeleteSubscriptions = "Nil (0) Subscriptions"
           End If

       Catch e As SoapException
           fDeleteSubscriptions = fDeleteSubscriptions + (e.Detail.OuterXml)
       End Try
   End Function
End Module

Deleting Log Files

Script to clean up old files.

This is a handy little disk maintenance script I put together to delete files older then x days.

It was written to delete log and text files that custom data feeds produce and which previously had to be deleted manually.

It needs to be saved with a .vbs extension (visual basic scripting). Just add a call to DeleteOldFiles for each directory and set it up under Scheduled tasks.

If you use it, find it useful or have any suggestions/bugs then please leave a comment to let me know. Thanks Gerard

'Gerard Conlon May 2008
'This VB script deletes files older then a certain date in a specifed directory
'The following call will delete all .log and .txt files in the c:\test dir older then 30 days

DeleteOldFiles "c:\test",30, ".log .txt"


'#####################################################
Sub DeleteOldFiles(sDir, iDaysOldToDelete, sExts)
'sDir is the drive and path to the dir for the files to be deleted
'iDaysOldToDelete is the number of days back to delete
'sExts is file extensions to delete - only 3 char extensions preceeded by the dot
Dim oFSO, oFolder, oFiles, oFile
Dim arrSplitName, strExtension
Set oFSO = CreateObject("Scripting.FileSystemObject")
Set oFolder = oFSO.GetFolder(sDir)
Set oFiles = oFolder.Files
For Each oFile In oFiles
'check if it is one of the extensions passed in
If InStr(sExts, LCase(Right(oFile.Name, 4))) Then
'if the last modified date + the age period is less then now then delete the file
If DateAdd("d", iDaysOldToDelete, oFile.DateLastModified) < ofiles =" Nothing" ofolder =" Nothing" ofso =" Nothing">