Introduction

Sometime we need to rename file using ms sql server query. In this article I am going to explain how to rename file using ms sql server cxp_cmdshell command , even file name contain space. First we need to check if file is exists? If Yes then we will do our next step. Below I have mention Store procedure which return all information about file.

To get all details of file we use xp_getfiledetails procedure.

xp_getfiledetails

'DateCreated' - Return Created date of file.
'DateLastAccessed' - Return Last access date of file.
'DateLastModified' - Return Last modified date of file.
'Drive' - Return file located drive name.
'Name' - Return name of file.
'ParentFolder' - Return parent folder of file.
'Path' -  Return path of file.
'ShortPath' - Returnshort path of file.
'Size' - Return size of file.
'Type'  - Return Type of file(.txt,.doc,etc...)

xp_cmdshell

rename file

 

Background

Consider that we are generating log file using any background service and that log file will be update after particular time interval. What happen in this case? Ultimately size of log file will be increase more and more. And when this log file size is goes beyond 50 mb then it's very difficult to open in notepad++. So to resolve this issue I have one idea to create new log file after previous log file size become 40 mb. But for this we need to rename previous log file. Thats why I rename  file using MS sql server xp_Cmdshell command.

Using the code

Step 1. Configure Xp_getfiledetails in sql server.

Run below query on Sql Server Management Studio(SSMS).

--Execute the code only once
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO

Step 2. Create Procedure xp_getfiledetails

In this query we are going to select only LastModifiedDate and Size of file as per our requirement.

CREATE PROCEDURE xp_getfiledetails
@filename NVARCHAR(255) = NULL --(full path)
AS
DECLARE @fileobj INT , @fsobj INT
DECLARE @exists INT, @error INT
DECLARE @src VARCHAR(255), @desc VARCHAR(255)

--create FileSystem Object
EXEC @error = sp_OACreate 'Scripting.FileSystemObject', @fsobj OUT
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj, @src OUT, @desc OUT
SELECT error=CONVERT(varbinary(4),@error), Source=@src, Description=@desc
RETURN 2
END

--check if specified file exists
EXEC @error = sp_OAMethod @fsobj, 'FileExists', @exists OUT, @filename

IF @exists = 0
BEGIN
RAISERROR 22004 'The system cannot find the file specified.'
RETURN 2
END

--Create file object that points to specified file
EXEC @error = sp_OAMethod @fsobj, 'GetFile' , @fileobj OUTPUT, @filename
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj
RETURN 2
END

--Declare variables holding properties of file
DECLARE @Attributes TINYINT,
@DateCreated DATETIME,
@DateLastAccessed DATETIME,
@DateLastModified DATETIME,
@Drive VARCHAR(1),
@Name NVARCHAR(255),
@ParentFolder NVARCHAR(255),
@Path NVARCHAR(255),
@ShortPath NVARCHAR(255),
@Size INT,
@Type NVARCHAR(255)

--Get properties of fileobject
EXEC sp_OAGetProperty @fileobj, 'Attributes', @Attributes OUT
EXEC sp_OAGetProperty @fileobj, 'DateCreated', @DateCreated OUT
EXEC sp_OAGetProperty @fileobj, 'DateLastAccessed', @DateLastAccessed OUT
EXEC sp_OAGetProperty @fileobj, 'DateLastModified', @DateLastModified OUT
EXEC sp_OAGetProperty @fileobj, 'Drive', @Drive OUT
EXEC sp_OAGetProperty @fileobj, 'Name', @Name OUT
EXEC sp_OAGetProperty @fileobj, 'ParentFolder', @ParentFolder OUT
EXEC sp_OAGetProperty @fileobj, 'Path', @Path OUT
EXEC sp_OAGetProperty @fileobj, 'ShortPath', @ShortPath OUT
EXEC sp_OAGetProperty @fileobj, 'Size', @Size OUT
EXEC sp_OAGetProperty @fileobj, 'Type', @Type OUT

--destroy File Object
EXEC @error = sp_OADestroy @fileobj
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fileobj
RETURN
END

--destroy FileSystem Object
EXEC @error = sp_OADestroy @fsobj
IF @error <> 0
BEGIN
EXEC sp_OAGetErrorInfo @fsobj
RETURN 2
END

--return results
SELECT
@DateLastModified as [DateLastModified] ,  
CEILING((@Size*1.0)/(1024*1024)) as [Size]  -- size is converted into mb

--EOF--

 

Step 3.  Configure xp_cmdshell

 

-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1
GO
-- To update the currently configured value for advanced options.
RECONFIGURE
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1
GO
-- To update the currently configured value for this feature.
RECONFIGURE
GO

Step 4. Create function to check  Is File Exists

create FUNCTION dbo.fc_FileExists(@path varchar(8000))  
RETURNS BIT  
AS  
BEGIN  
     DECLARE @result INT  
     EXEC master.dbo.xp_fileexist @path, @result OUTPUT  
     RETURN cast(@result as bit)  
END; 

Step 5.  Create Log monitoring procedure in which we are checking if file size is greater than 40 mb then xp_cmdshell rename that file.

In below query our scenarioa are we have three file located in c drive in Log folder. Names are

Log file1-2015-03-25.log

Log file2-2015-03-25.log

Log file3-2015-03-25.log

Here we place three condition

1. If log file is exists

2. If log file is modified in last one our

3. If log file size is greater than 40 mb

If  first condition is satisfy then and only then remaining two condition will execute else Log file exists mail will be send on given mail id..

When second condition is not satisfy then Log file not working mail will be send to given mail id.

When third condition is satisfy then rename current file

ex Previous file name is Log file1-2015-03-25.log  - new file name is Log file1-2015-03-25-101020.log

note - 101020 are hhmmss(hour minute second).

All these steps will happen one by one for three files.

 

Create Procedure [dbo].[LogMonitoring]
As    
Begin    
SET NOCOUNT OFF    
    
Declare @IsFileExists int;    
Declare @LogFile1FilePath varchar(2000);    
Declare @LogFile2FilePath varchar(2000);    
Declare @LogFile3FilePath varchar(2000);    
Declare @day varchar(2), @month varchar(2), @year varchar(4);    
Declare @Result varchar(max)='<html><body><table>';    
   
    
--Set day in two digit    
Set @day = (SELECT CONVERT(Varchar, day(GETDATE())))    
IF LEN(@day)=1    
BEGIN    
    SET  @day = '0'+@day;    
END    
--set day in two digit    
    
--Set month in two digit    
Set @month = (SELECT CONVERT(Varchar, month(GETDATE())))    
IF LEN(@month)=1    
BEGIN    
    SET  @month = '0'+@month;    
END    
--set month in two digit    
    
SET @year=(SELECT CONVERT(Varchar, year(GETDATE())))    
    
Set @LogFile1FilePath='C:\logs\Log file1-'+@year+'-'+@month+'-'+@day+'.log'; --Creating file name which are exist in our system (Log file1-2015-03-25.log)
Set @LogFile2FilePath='C:\logs\Log file2-'+@year+'-'+@month+'-'+@day+'.log'; --Log file2-2015-03-25.log    
Set @LogFile3FilePath='C:\logs\Log file3-'+@year+'-'+@month+'-'+@day+'.log'; --Log file3-2015-03-25.log    
    
Declare @Index int=0    
Declare @CurrFilePath varchar(2000)    
 
-- we are checking here for three files
 
While @Index<3    
BEGIN    
      IF @Index=0    
      Begin    
      SET @CurrFilePath=@LogFile1FilePath    
      END    
          
       IF @Index=1    
      Begin    
      SET @CurrFilePath=@LogFile2FilePath    
      END    
          
       IF @Index=2    
      Begin    
      SET @CurrFilePath=@LogFile3FilePath    
      END    
          
          
          
      set @IsFileExists = (select dbo.fc_FileExists(@CurrFilePath));    
        declare @AssignSubject varchar(200),@AssignBody varchar(2000);   
      
       If @IsFileExists=1    
      Begin    
             Declare @LastModT datetime;
             Declare @Size bigint;
             declare @LastModTime as table (LastmodDate datetime,Size Bigint)  
                insert into @LastModTime exec  xp_getfiledetails @CurrFilePath  
                
                set @LastModT = (select top 1 LastmodDate from @LastModTime order by LastmodDate desc)  
                Set @Size = (select top 1 Size from @LastModTime order by LastmodDate desc)  
                
                
                 Declare @timeDifference int ;  
                 Set @timeDifference=(SELECT DATEDIFF(mi,@LastModT,GetDate()))  
                  
                 If @timeDifference>=60  
                 Begin             
                 set @Result =@Result + '<tr><td>' + 'Please check Log file not working.' + @CurrFilePath + '</td></tr>';                  
                 End
             
                
                
                If @Size>40 --file size greater than 40 mb then rename file.
                Begin
                        declare @cmdStr varchar(8000)
                        declare @newTime varchar(100)
                        select @newTime= '-'+CONVERT(VARCHAR(8),GETDATE(),108)
                        declare @newFileName varchar(100);
                        set @newFileName=Replace(Replace(@CurrFilePath,'C:\logs\',''),'.log','')+Replace(@newTime,':','')+'.log'
                        set @cmdStr='Rename "'+@CurrFilePath+'" "'+@newFileName+'"'
                        exec xp_cmdshell @cmdStr
                End
      End    
      ELSE    
      Begin    
      set @Result =@Result + '<tr><td>' +  'Log file not exists' + @CurrFilePath + '</td></tr>';    
        
      END    
      delete from @LastModTime  
       
     set @Index=@Index+1;    
END    
       -- Sending mail
        if @Result<>'<html><body><table>'      
        Begin      
          set @AssignSubject= 'Log Monitoring'      
          set @AssignBody= @Result + '</table></body></html>';      
         EXEC msdb.dbo.sp_send_dbmail @recipients='test@gmail.com',      
            @subject = @AssignSubject,      
            @body =@AssignBody,      
            @body_format = 'HTML',      
            @profile_name = 'Database Mail';       
       End      
      
END