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).
<code>--Execute the code only once
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO</code>
Step 2. Create Procedure xp_getfiledetails
In this query we are going to select only LastModifiedDate and Size of file as per our requirement.
<code>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--</code>
Step 3. Configure xp_cmdshell
<code>-- 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</code>
Step 4. Create function to check Is File Exists
<code>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; </code>
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.
<code>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>';
</code>
<code>
--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
</code>