MSSQL 2005 procedure a file move rename

CURRENT CONFIGURATION: MSSQL server 2005

OBJECTIVE: Move or rename a file from MSSQL

SOLUTION:

I wrote SQL procedure. You can change destination database to whatever you want.

USE master
GO

-- set required options
EXEC sp_configure 'show advanced options',1
RECONFIGURE
GO
EXEC sp_configure 'Ole Automation Procedures',1
RECONFIGURE
GO

USE [sysman]
GO

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[dba_file_move_rename]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_file_move_rename]
GO

CREATE PROCEDURE [dbo].[dba_file_move_rename]
    @OldFilePath    nvarchar(512),
    @NewFilePath    nvarchar(512),
    @send_email_proc  nvarchar(256),
    @log int = 2
AS
-- *****************************************************************************
-- Author:  Vadim Zenin http://vadimszenins.blogspot.com
-- Version:    1.00
-- Date:      10/12/2009 18:37:34
-- Procedure for file move or rename
--
-- Usage:
-- In batch file: sqlcmd -E -dsysman -Q"EXEC sysman..dba_file_move_rename 'C:\temp\oldfile.txt', 'C:\temp\newile.txt', 'send_email', 1" >>%LOGFILE%
--
-- Parametrs:
-- @OldFilePath example: 'C:\temp\oldfile.txt'
-- @NewFilePath example: 'C:\temp\newile.txt'
-- @send_email_proc example: 'send_email'
-- @log (0 - none, 1 - minimum, 2 - standard(default), 4 - debug) optinal
--
-- Tested platform:
-- MS SQL 2005
--
-- Version 1.00 revision:
--
--
-- This code is made available as is, without warranty of any kind. The entire
-- risk of the use or the results from the use of this code remains with the user.
-- *****************************************************************************

DECLARE
    @procname        nvarchar(256),
    @fso            int,
    @hr              int,
    @sqlcmd            nvarchar(600),
    @email_subj        nvarchar(255),
    @email_body        nvarchar(3000)

-- Get current stored procedure name
SELECT @procname = OBJECT_NAME(@@PROCID)
IF @log > 1
begin
    PRINT RTRIM(CAST(GETDATE() AS NVARCHAR(30))) + ' ' + @procname + ' procedure has started';
    PRINT N'The Database Engine instance ' + RTRIM(@@SERVERNAME) + N' is running SQL Server build '
    + RTRIM(CAST(SERVERPROPERTY(N'ProductVersion ') AS NVARCHAR(128)));
end
IF @log > 2
BEGIN
  PRINT 'Parametr 1: ' + @OldFilePath;
  PRINT 'Parametr 2: ' + @NewFilePath;
  PRINT 'Parametr 3: ' + @send_email_proc;
  PRINT 'Parametr 4: ' + RTRIM(@log);
END

-- Check requrements
If not exists (Select * from dbo.sysobjects where xtype='p' and name=@send_email_proc)
BEGIN
    SELECT @email_body = N'!? Stored procedure sysman..' + @send_email_proc + ' does not exist'
    IF @log >= 0
    PRINT @email_body
    RAISERROR (@email_body,16,1) with log
END

--------------------------------------------------------------------------------
-- Main procedure
--------------------------------------------------------------------------------

SET @hr = 0

-- Creating File System Object
EXEC @hr=sp_OACreate 'Scripting.FileSystemObject',@fso OUT
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @fso
    SELECT    @email_subj = N' Error creating File System Object.'+ @procname
  SELECT    @email_body = 'Error creating File System Object. Procedure name: ' + @procname
  IF @log >= 0
  BEGIN
      PRINT N' Sending failure email notification with subject: '
      PRINT @email_subj
    END
    SELECT @sqlcmd = '[sysman]..[' + @send_email_proc + ']'
    IF @log > 2
    PRINT    '- Command: ' + @sqlcmd;
  EXEC @sqlcmd @email_subject = @email_subj,@email_msg = @email_body
  RAISERROR (@email_body,16,1) with log
END

EXECUTE @hr=sp_OAMethod @fso, 'MoveFile', null, @OldFilePath, @NewFilePath
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @fso
    SELECT    @email_subj = N' Error moving or renaming File.' + @procname
  SELECT    @email_body = ' Error moving or renaming File from ' + @OldFilePath +
      ' to ' + @NewFilePath + ' Procedure name: ' + @procname
  IF @log >= 0
  BEGIN
      Print N' Sending failure email notification with subject: '
      Print @email_subj
  END
    SELECT @sqlcmd = '[sysman]..[' + @send_email_proc + ']'
    IF @log > 2
    PRINT    '- Command: ' + @sqlcmd;
  EXEC @sqlcmd @email_subject = @email_subj,@email_msg = @email_body
  RAISERROR (@email_body,16,1) with log
END
else
begin
    IF @log > 0
      Print N'Moving or renaming File from ' + @OldFilePath + ' to ' + @NewFilePath + ' by procedure ' + @procname
end

-- Destroying File System Object
EXEC @hr=sp_OADestroy @fso
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

IF @log > 1
    PRINT RTRIM(CAST(GETDATE() AS NVARCHAR(30))) + ' ' + @procname + ' procedure has finished';

DOWNLOAD: sysman-dba_File_move_rename.zip

Download md5: 60853e73c93a656f7f373809ce3f997b