Archive IIS Log Files

The following PowerShell script is using 7Zip to compress IIS Log files.  It identifies IIS Log folder and parses through the contents to find the log files older than $days.  It zips them up, and delete the original log files. The resulting compressed archive will be about 4.5% of the size of the original log files.

<##################################################################################
 NAME:  Archive_IIS_Log_Files.ps1
  
 COMMENT:  The script parse through the IIS logs directory contents to find the log files 
   older than $days. Zip them up, and delete the original  
           log files. The resulting compressed archive will be about  
           4.5% of the size of the original log files. 
  
 REQUIRED: 7-Zip is required for this to work. By default this script  
           looks for the 7-Zip executable, 7za.exe, in C:\Program Files\7-Zip\ 
 
 Running Scripts Without Starting Windows PowerShell
   powershell.exe -noexit c:\MyScripts\Archive_IIS_Log_Files.ps1

 Scheduling Action: powershell.exe c:\MyScripts\Archive_IIS_Log_Files.ps1
##################################################################################>

  
Function Archive_IIS_Log_Files{
 $strComputer = "."
 $days = "7"
 $exe = 'C:\Program Files\7-Zip\7z.exe'
 $emailFrom = "noreply@server.com"
 $emailTo = "admin@server.com"
 $subject = "Scheduled job 'Archive IIS Log Files' failed"
 $body = "Error in : "
 $smtpServer = "smtp.mail.com"
 Write-host "`r` "
 Write-host "`r` "
 Write-host "`t` `t` Start process `t` `t` "  -foregroundcolor Black -backgroundcolor White
 Write-host "`r` "
 Write-host "`t` `t` Inluding .Net Web.Administration"  -foregroundcolor "Green"
 [System.Reflection.Assembly]::LoadFile( "C:\windows\system32\inetsrv\Microsoft.Web.Administration.dll")
 $iis = new-object Microsoft.Web.Administration.ServerManager
 Write-host "`r` "
 Write-host "`t` `t` Achive logs for the following websites: `t` `t` "  -foregroundcolor DarkCyan
 $iis.sites | select-object Id, Name
 $TopFolder = $IIS.SiteDefaults.LogFile.Directory 
 $TopFolder = $TopFolder -replace "%SystemDrive%", "C:"
 Write-host "`t` `t` Folders to process:" -foregroundcolor DarkCyan
 Get-ChildItem -path $TopFolder | select pspath
 Write-Host "To cancel the process, Press ‘Ctrl + C’" -foregroundcolor Yellow
 Start-sleep -s 5
 foreach ($site in $iis.sites){
  $TargetFolder = "$TopFolder\W3SVC"
  $TargetFolder = $TargetFolder += $site.Id
  $zipName = Get-Date -format 'yyyy_MM_dd_HH_mm_ss'
  $zipName = "$TargetFolder\$zipName.logs.zip"
  Write-host "`r` "
  Write-Host "Zip file: " $zipName -foregroundcolor DarkCyan
  $SiteName = GetSiteNameForByLogFolder $TopFolder  $TargetFolder
  Write-host "Processing logs for: $SiteName" -foregroundcolor "Yellow"
  if (Test-Path $TargetFolder){
   #Warn you the targeted folder, so you can double check
   Write-host "`r` "
   Write-host "The Targeted Folder is:" $TargetFolder -foregroundcolor DarkCyan
   $Now = Get-Date
   # Notice the minus sign before $days
   $LastWrite = $Now.AddDays(-$days)
   $Files = get-childitem $TargetFolder -include *.log -recurse |Where {$_.LastWriteTime -le "$LastWrite"}
   If ($Files -eq $NULL){
    write-host "No files for processing." -foregroundcolor Green
    return
   }
   try{
    foreach ($File in $Files){
     Write-host "`r` "
     Write-host "Running: " $exe a $zipName $File -foregroundcolor Yellow
     & $exe a $zipName $File
     Write-host "`r` "
     write-host "Deleting File $File" -foregroundcolor "Red"
     #You can add -whatif to see the consequence – Remove-item $File -Whatif
     $File.Delete()
    }
    #throw $_.exception
   }
   catch{
    #Send error message
    $body = "$body$File"
    $smtp = new-object Net.Mail.SmtpClient($smtpServer)
    $smtp.Send($emailFrom, $emailTo, $subject, $body)
    Write-Error  $body
   }
  } Else {
   Write-host "`r` "
   Write-Host "The Folder $TargetFolder Does Not Exist!"
  }
 }
 Write-host "`r` "
 Write-host "`t` `t` End process `t` `t` "  -foregroundcolor Black -backgroundcolor White
}
Function GetSiteNameForByLogFolder($TopFolder , $TargetFolder){
 $TargetFolderLength = $TargetFolder.Length
 $TopFolderLength = "$TopFolder\W3SVC".Length
 $SiteID = $TargetFolder.substring($TopFolderLength,$TargetFolderLength - $TopFolderLength)
 Write-host "`r` "
 [System.Reflection.Assembly]::LoadFile( "C:\windows\system32\inetsrv\Microsoft.Web.Administration.dll")
 $iis = new-object Microsoft.Web.Administration.ServerManager
 foreach ($site in $iis.sites){
  if  ($site.id -eq $SiteID){
   $SiteName = $Site.Name
   break
  }
 }
 return $SiteName
}
Archive_IIS_Log_Files

If you find this script useful please donate generously.

Autonomy Interwoven TeamSite -> TortoiseSVN integration process

The following Perl script synchronises the TeamSite ‘STAGING’ area with SVN repository.



#!/usr/bin/perl
####################################################################
            # Purpose:                   Teamsite -> SVN integration
            # Args:                        $iwwapath
####################################################################
use POSIX qw(getcwd strftime);
use Cwd;
    try {
      my $dir = $ARGV[0];
      ## Constants declaration
      my $odLogIndex = "E:\\Req_Sys_SW\\Interwoven\\OpenDeployNG\\log\\rcv. appsPROD_flist.RUNDEPOLY.branch.log";
      my $odLog = "E:\\Req_Sys_SW\\Interwoven\\OpenDeployNG\\log\\rcv.appsPROD_flist.RUNDEPOLY.SourceServer.to. branch.log";                          
      my $username = "SVNuser";  #User should have access to all SVN applications Trunk folders
      my $password = "****";
      my $drive = "f:";
      my $share = "teamsite_integration";
      my $appDir = "application";
      my $target = "checkout";
      my $webdev = "\\inetpub\\webdev\\";
      my $commitSVN = " --message \"Automatic Teamsite Update\"";
      ##
      my $workDir = $drive . "\\" . $share . "\\" . $appDir . "\\";
      my $scriptLog = $0;
      my $currentLocation = substr($scriptLog, 0, get_last_pos($scriptLog,"\\"));
      my @svn, @app, @devSite, @url, $line, $drive, $appVal, $svnVal, $tmpVal, $tmpApp;
      my $pos1, $pos2, $devSiteVal, $appIndex, $replace , $find, $i;
      my $chkDirExists, $chkFolders, $lid, @folders, $chkFolderExists;
      my $msg = "";
      my $cmd = "";
      my $tmpTarget = "";
      my $beginJob = 0;
      my $endJob = 0;
      my $count_files = 0;
     
                  $commitSVN = $commitSVN . " --username " . $username . " --password " . $password . " --non-interactive";
                  $conf::date_format = "%d/%m/%Y";
                  $conf::time_format = "%H:%M:%S";
                  $scriptLog =~ s/.pl/.log/g;
                  $app[0] = "app1";
                  $app[1] = "app2";
                  $app[2] = "app3";
                  $svn[0] = "svn://server/app1/Trunk/";
                  $svn[1] = "svn://server/app2/Trunk/";
                  $svn[2] = " svn://server/app3/Trunk/";
                  $devSite[0] = $drive . $webdev . "app1";
                  $devSite[1] = $drive . $webdev . "app2";
                  $devSite[2] = $drive . $webdev . "app3";
                  # Get Open Deploy Job ID
                              if(-e $odLogIndex && -r $odLogIndex) {
                                          open(IN, "<$odLogIndex") || die "Couldn't open $odLogIndex: $!";
                              }
                              while ($line = <IN>) {
                                          if ((index($line, "Elapsed Time=") > -1)) {
                                                                  $pos1 = index ($line, "Job ID=") + length("Job ID=");
                                                                  $pos2 = index ($line, "   Deployment leg=") - length("   Deployment leg=");
                                                                  $jobid = substr($line, $pos1, $pos2 - $pos1);
                                                      }
                              }
                              close(IN);
                              if (length($jobid) eq 0) {
                                          exit;
                              }       
                              $msg = "Step 1 of 9 : Get Open Deploy Job ID";
                              debug($scriptLog, $msg);
                              $msg = "Start: " . get_now_date() . " " . get_now_time();
                              debug($scriptLog, $msg);    
                              $msg = "Open Deploy Job ID: " . $jobid;
                              debug($scriptLog, $msg);
                              $msg = "Step 2 of 9 : Initialising - removing $target directory if exists";
                              debug($scriptLog, $msg);
                              $currentLocation = $currentLocation . "\\" . $target;
                              if (-d $currentLocation) {
                                $cmd = "rmdir " . $drive . "\\" . $share . "\\" . $target . " /S /Q" . " >> " . $scriptLog;
                                $msg = "Run: " . $cmd;
                                debug($scriptLog, $msg);
                               `$cmd`;
                              }
                              $tmpVal = "-- Processing file(";
                              $msg = "Step 3 of 9 : Extract all Received File/s to array";
                              debug($scriptLog, $msg);
                              if(-e $odLog && -r $odLog) {
                                          open(IN, "<$odLog") || die "Couldn't open $odLog: $!";
                              }
                              while ($line = <IN>) {
                                          if ($beginJob eq 0) {
                                                      if ((index($line, "Job ID=" . $jobid) > -1) && (index($line, "Elapsed Time=") <= -1)) {
                                                                  $beginJob = 1;
                                                      }
                                          }       
                                          if ($beginJob eq 1) {
                                                      if (index($line, $tmpVal) > -1) {
                                                                  $pos1 = index($line, $tmpVal) + length($tmpVal);
                                                                  $pos2 = index($line, ")", $pos1);
                                                                  $dir = substr($line, $pos1, $pos2 - $pos1);
                                                                  push(@flist, $dir);
                                                      }
                                          }
                                          if ((index($line, "Job ID=" . $jobid) > -1) && (index($line, "Elapsed Time=") > -1)) {
                                                      last;
                                          }       
                              }
                              close(IN);
                              $appIndex = 0;
                              foreach $tmpFile (@flist){
                                          $msg = "** Processing: " . $tmpFile . " **";
                                          debug($scriptLog, $msg);
                                          $count_files++;
                                          $i = 0; 
                                          foreach $tmpApp (@app){
                                                      if (index($tmpFile, $tmpApp) gt -1){
                                                                  $appVal = $app[$i];
                                                                  $svnVal = $svn[$i];
                                                                  $devSiteVal = $devSite[$i];
                                                                  last;
                                                      }       
                                                      $i++;
                                          }
                                          if ($appIndex eq 0) {
                                                      $msg = "Step 4 of 9 : Checking out";
                                                      debug($scriptLog, $msg);
                                                      $cmd = "SVN checkout " . $svnVal . " " . $drive . "\\" . $share . "\\" . $target . "\\" . $appVal . " --username \"Teamsite\" --non-interactive" . " >> " . $scriptLog;
                                                      $msg = "Run: " . $cmd;
                                                      debug($scriptLog, $msg);
                                                      `$cmd`;
                                                      $appIndex++;
                                          }
                                          $msg = "Step 5 of 9 : Updating $target from Teamsite deployment";
                                          debug($scriptLog, $msg);
                                          $tmpTarget = $tmpFile;
                                          $replace = "\\\\" . $appDir . "\\\\";
                                          $find = "\\" . $target . "\\";
                                          $tmpTarget =~ s/$replace/$find/g;
                                          $cmd = "md " . substr($tmpTarget, 0, get_last_pos($tmpTarget, "\\")) . " >> " . $scriptLog;
                                          $msg = "Run: " . $cmd;
                                          debug($scriptLog, $msg);
                                          `$cmd`;
                                          $tmpVal = $tmpFile;
                                          $replace = "\\\\" . $appDir . "\\\\" . $appVal . "\\\\";
                                          $find = "\\" . $appDir . "\\generic\\";
                                          $tmpVal =~ s/$replace/$find/g;
                                          #Update this file from the current sub-site
                                          if (-e $tmpFile ) {
                                                      $cmd = "COPY " . $tmpFile . " " . $tmpTarget . " /Y" . " >> " . $scriptLog;
                                                      $msg = "Run: " . $cmd;
                                                      debug($scriptLog, $msg);
                                                      `$cmd`;
                                          } else {
                                                      $msg = "Warning: File " . $tmpFile . " not exists.";
                                                      debug($scriptLog, $msg);
                                          }
                                          $tmpVal = substr($tmpFile, index($tmpFile, "\\" . $appVal . "\\") + length("\\" . $appVal . "\\"), length($tmpFile));
                                          $devSiteVal = $devSiteVal . "\\" . $tmpVal;
                                          $cmd = "md " . substr($devSiteVal, 0, get_last_pos($devSiteVal,"\\")) . " >> " . $scriptLog;
                                          $msg = "Run: " . $cmd;
                                          debug($scriptLog, $msg);
                                          `$cmd`;
                                          $msg = "Step 6 of 9 : Copying from " . $tmpTarget . " to " . $devSiteVal;
                                          debug($scriptLog, $msg);
                                          $cmd = "COPY " . $tmpTarget . " " . $devSiteVal . " /Y" . " >> " . $scriptLog;
                                          $msg = "Run: " . $cmd;
                                          debug($scriptLog, $msg);
                                          `$cmd`;
                                          $cmd = "DEL /A:H /F /S /Q " . $drive . "\\" . $share . "\\" . $target . " >> " . $scriptLog;
                                          $msg = "Delete all hidden files: " . $cmd;
                                          debug($scriptLog, $msg);
                                          `$cmd`;
                                          $msg = "Step 7 of 9 : Adding back to SVN";
                                          debug($scriptLog, $msg);                            
                                          $chkDirExists = substr($tmpTarget, 0, get_last_pos($tmpTarget, "\\")) . ".svn";
                                          $msg = "Directory " . $chkDirExists;
                                          if (-d $chkDirExists ) {
                                                      $msg = $msg . " exists.";
                                              debug($scriptLog, $msg);
                                              $cmd = "SVN add " . $tmpTarget . " --force" . " >> " . $scriptLog;         
                                              $msg = "Run: " . $cmd;
                                                      debug($scriptLog, $msg);
                                                      `$cmd`;
                                                      $msg = "Step 8 of 9 : Committing back to SVN";
                                                      debug($scriptLog, $msg);
                                                      $cmd = "SVN commit " . $tmpTarget . $commitSVN . " >> " . $scriptLog;
                                                      $msg = "Run: " . $cmd;
                                                      debug($scriptLog, $msg);
                                                      `$cmd`;
                                          } else {
                                              $msg = $msg . " not exists.";
                                                      debug($scriptLog, $msg);                                                                           
                                                      $lid = $drive . "\\" . $share . "\\" . $target . "\\" . $appVal;
                                                      $chkFolders = substr(substr($tmpTarget, 0, get_last_pos($tmpTarget, "\\")), length($lid) + 1, length(substr($tmpTarget, 0, get_last_pos($tmpTarget, "\\"))));
                                                      @folders = split /\\/, $chkFolders;
                                                      $i = scalar @folders;
                                                      if ($i gt 0) {
                                                                  $chkFolderExists = $lid;
                                                                  foreach $dir (@folders)
                                                                              {       
                                                                                          $chkFolderExists = $chkFolderExists . "\\" . $dir;
                                                                              $cmd = "SVN add " . $chkFolderExists . " --force" . " >> " . $scriptLog;
                                                                              $msg = "Run: " . $cmd;
                                                                              debug($scriptLog, $msg);
                                                                              `$cmd`;
                                                                              $msg = "Step 8 of 9 : Committing back to SVN";
                                                                                          debug($scriptLog, $msg);
                                                                                          $cmd = "SVN commit " . $chkFolderExists . $commitSVN . " >> " . $scriptLog;
                                                                                          $msg = "Run: " . $cmd;
                                                                                          debug($scriptLog, $msg);
                                                                                          `$cmd`;
                                                                              last;   
                                                                              }
                                                      }
                                          }                                                                   
                              }
                              $msg = "Step 9 of 9 : Finalising - removing $target directory";
                              debug($scriptLog, $msg);
                              $cmd = "rmdir " . $drive . "\\" . $share . "\\" . $target . " /S /Q" . " >> " . $scriptLog;
                              $msg = "Run: " . $cmd;
                              debug($scriptLog, $msg);
                              `$cmd`;
                              $msg = "End: " . get_now_date() . " " . get_now_time();    
                              debug($scriptLog, $msg);
                              $msg = "Processed: " . $count_files . " file/s.";        
                              debug($scriptLog, $msg);
                              $msg = "____________________________________________________________________";
                              debug($scriptLog, $msg);                                    sub debug {
                  open(OUT, ">>@_[0]") || return;
                  chmod(0664, "@_[0]");
                  print OUT "@_[1]\n";
                  close(OUT);
      }
     
      sub get_now_date{
                  return POSIX::strftime($conf::date_format, localtime(time()));
      }
     
      sub get_now_time{
                  return POSIX::strftime($conf::time_format, localtime(time()));
      }
     
      sub get_last_pos{
                  $pos = -1;
                  while (($pos = index(@_[0], @_[1], $pos)) > -1){
                              $pos++;
                              $pos_last = index(@_[0], @_[1], $pos);
                              if ($pos_last <= -1){
                                          $pos_last = $pos;
                              }
                  }
                  return $pos_last;       
      }                   
    } catch Error with {
           my $ex = shift;
           $msg = "** Error: " . $ex . " **";
           debug($scriptLog, $msg);
    };
All (dynamic) application files should be imported initially to the TeamSite branch.  The script above should be called by the workflow associated with the TeamSite branch after content approval phase.  The process consists of the following steps:
·         Find OpenDeploy Job ID from the index log file.
·         Extract all received file/s referenced by the Job ID from the OpenDeploy log file.
·         Remove the checkout folder from the integration target location
·         Checkout appropriate application files into checkout folder
·         Update checkout files from the TeamSite deployment
·         Promote updated file/s to the appropriate environment (eg. DEV)
·         Delete all hidden files as required by SVN framework
·         Add updated file/s to the appropriate SVN repository
·         Commit updated file/s to the SVN repository
·         Remove temporary created checkout directory
The TeamSite->SVN integration area (\\destinationServer\Teamsite_Integration shared folder on the destination server) shows below

If you find this script useful please donate generously.

Autonomy Interwoven TeamSite javascript

The following javascript uses Form API to change the visability of dropdown boxes of the content entry form:

/*************************************************************************************
 Script: datacapture.js

 Usage: Insert the following line into the datacapture template

  <script language="javascript" src="/iw-mount/default/main/branchname/STAGING/templatedata/site/standard/presentation/datacapture.js"/>


 The init() function is executed at the end of this script.

 It register registers event handlers, and calls them to

 set the initial state of the form.

*************************************************************************************/
function init() {
 IWEventRegistry.addItemHandler("/Master", "onItemChange", typeMaster);
 typeMaster(IWDatacapture.getItem("/Master"));
 IWEventRegistry.addItemHandler("/Page_Type", "onItemChange", typeHandler);
 typeHandler(IWDatacapture.getItem("/Page_Type"));
}

function typeHandler(typeItem) {
 // Show Category combo-box based on selected Page_Type radio-button option
 var pagetypeValue = typeItem.getOptions()[typeItem.getValue()].value;
 var areaVar = IWDatacapture.getItem("/Page_Type");

 for (var i = 0; i <= areaVar.getOptions().length; i++) {
  if (pagetypeValue == areaVar.getOptions()[i].value) {
   setItemVisibleStatus (IWDatacapture.getItem("/Category" + (i + 1)), true);
  }
  else {
   setItemVisibleStatus (IWDatacapture.getItem("/Category" + (i + 1)), false);
  }
 }
}

function typeMaster(typeItem) {
    var mastertypeValue = typeItem.getOptions()[typeItem.getValue()].value;
    var reqItem = IWDatacapture.getItem("/Masters_Location"); 
    if (mastertypeValue == "n") {
     setItemVisibleStatus (reqItem, false);
    }
    else {
     setItemVisibleStatus (reqItem, true);
    }
}

function setItemVisibleStatus(item, status) {
    item.setVisible(status);
}  

// Call the initialization routine on load.           
IWEventRegistry.addFormHandler("onFormInit", init);

If you find this script useful please donate generously.

VB script to run MS Access Project application

The following VB script add registry key Trusted Locations for this application, creates destination folder on the desktop, copyes MS Access Project application file on the desktop and runs the application:

Option Explicit

'This Script runs AppName.ade
Dim objFSO, objShell, fromPath, toPath, fName, iPos, objFile
Dim filesys, regFile, fromFile, toFile, strComputer
Dim objWMIService, colProcessList, objProcess, id

 fromPath = "j:\databases\AppName\"
 toPath = "e:\App\" ' Trailing \ is required
 fName = "AppName"
 regFile = fromPath & fName & ".reg"
 fromFile = fromPath & fName & ".ade"
 toFile = toPath & fName & ".ade"
 'regKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\12.0\Access\Security\Trusted Locations\Location0\Path" 'Access 2007
 regKey = "HKEY_CURRENT_USER\Software\Microsoft\Office\13.0\Access\Security\Trusted Locations\Location0\Path" 'Access 2010

 'Add registry key TrustedLocations for this application
 If Err.number = vbEmpty Then
  Set objShell = CreateObject("WScript.Shell")
  objShell.RegWrite regKey, toPath, "REG_SZ"
 Else WScript.echo "VBScript Error: " & err.number
 End If

 'Create destination folder on the desktop
 iPos = InStr(4, toPath, "\", 0) ' Skip drive letter check
 Set objFSO = CreateObject("Scripting.FileSystemObject")
 While(iPos <> 0)
     If(Not(objFSO.FolderExists(Left(toPath, iPos)))) Then
        objFSO.CreateFolder(Left(toPath, iPos))
     End If
     iPos = InStr(iPos+1, toPath, "\", 0)
 Wend

 'Copy application file on the desktop
 Set objFile = objFSO.GetFile(fromFile)
 objFile.Copy toFile, True
 'Run the application
 If Err.number = vbEmpty Then
  objShell.run ("msaccess " & toFile)
 Else WScript.echo "VBScript Error: " & err.number
 End If
 Set objFile = Nothing
 Set objShell = Nothing
 Set objFSO = Nothing
 WScript.Quit

If you find this script useful please donate generously.

SQL Reporting Services security report

The following query retrieves SQL Reporting Services security settings:

SELECT   C.Path
        ,C.Name
        ,R.RoleName
        ,U.UserName
        FROM dbo.Users U
            ,dbo.PolicyUserRole PUR
            ,dbo.Roles R
            ,dbo.Policies P
            ,dbo.Catalog C
WHERE P.PolicyID = C.PolicyID
AND U.UserID = PUR.UserID
AND PUR.RoleID = R.RoleID
AND PUR.PolicyID = P.PolicyID

If you find this script useful please donate generously.

SQL Reporting Services Multi Value Parameter

The following T-SQL user defind function converts comma delimited string to a table (
usage described at http://social.msdn.microsoft.com/Forums/en/sqlreportingservices/thread/c02370b5-aeda-47ec-a3a8-43b2ec1e6c26):

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON

GO

PRINT 'drop function dbo.udfMulti_Value'

GO

if exists (select * from dbo.sysobjects

where id = object_id(N'dbo.udfMulti_Value') and xtype in (N'FN', N'IF', N'TF'))

drop function dbo.udfMulti_Value

GO

PRINT 'CREATE FUNCTION dbo.udfMulti_Value'

GO

/******************************************************************************
NAME: udfMulti_Value

PURPOSE:

REVISIONS:

$Log$

Ver Date Author Description
******************************************************************************/
CREATE FUNCTION dbo.udfMulti_Value(@code NVARCHAR(MAX))
RETURNS @temp table(Code NVARCHAR(MAX))
AS
BEGIN

If ISNULL(@code ,'') = '' BEGIN
   SET @code = 'ALL' END
ELSE

 WHILE LEN(@code ) > 0 BEGIN
  IF PATINDEX('%,%', @code ) > 0 BEGIN
  INSERT INTO @temp VALUES(RTRIM(SUBSTRING(@code ,1,PATINDEX('%,%', @code )-1)))
  SET @code = LTRIM(SUBSTRING(@code ,PATINDEX('%,%', @code )+1,1024)) END
 ELSE BEGIN
  INSERT INTO @temp VALUES(LTRIM(RTRIM(@code )))
  BREAK
 END
END

RETURN
END
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

If you find this script useful please donate generously.

Convert String to "Proper" Case

The following T-SQL user defined function converts a string value to "Proper" case:

SET QUOTED_IDENTIFIER ON
GO

SET ANSI_NULLS ON
GO

PRINT 'drop function [dbo].[udfProper]'
GO

if exists (select * from dbo.sysobjects
where id = object_id(N'[dbo].[udfProper]')
and xtype in (N'FN', N'IF', N'TF'))

drop function [dbo].[udfProper]
GO

PRINT 'CREATE function dbo.udfProper'
GO
/******************************************************************************
NAME: udfProper

PURPOSE: Converting a String to "Proper" Case

REVISIONS:

$Log$
Ver Date Author Description
******************************************************************************/
CREATE FUNCTION dbo.udfProper(@in varchar(255))
RETURNS varchar(255)
as
BEGIN
   DECLARE @in_pos tinyint
          ,@inter varchar(255)
          ,@inter_pos tinyint 

  select @in_pos = 0, @in = lower(@in)
  select @inter = @in
  select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)

  while @inter_pos > 0 begin
   select @in_pos = @in_pos + @inter_pos
   select @in = stuff(@in, @in_pos, 1, upper(substring(@in, @in_pos, 1))),
   @inter = substring(@inter, @inter_pos + 1, datalength(@inter) - @inter_pos)
   select @inter_pos = patindex('%[^0-9A-Za-z]%', @inter)

   if @inter_pos > 0 begin
      select @in_pos = @in_pos + @inter_pos
      select @inter = substring(@inter, @inter_pos + 1, datalength(@inter) - @inter_pos)
      select @inter_pos = patindex('%[0-9A-Za-z]%', @inter)
   end
  end
RETURN(@in)
END
GO

SET QUOTED_IDENTIFIER OFF
GO

SET ANSI_NULLS ON
GO

If you find this script useful please donate generously.