MSSQL 2005 procedure to write information to text file

CURRENT CONFIGURATION: MSSQL server 2005

OBJECTIVE: Write line to text, log file from MSSQL server in middle of backup operation to be able to confirm successful result from windows batch script or whatever.

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_write_to_file]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[dba_write_to_file]
GO

CREATE PROCEDURE [dbo].[dba_write_to_file]
    @file_name varchar(1000),
    @string varchar(1000),
    @send_email_proc  nvarchar(256),
    @log int = 2
AS
-- *****************************************************************************
-- Author:  Vadim Zenin http://vadimszenins.blogspot.com
-- Version:    1.00
-- Date:      10/12/2009 19:25:02
-- Procedure to write string to file
--
-- Usage:
-- In batch file: sqlcmd -E -dsysman -Q"EXEC sysman..dba_write_to_file 'C:\temp\testfile.txt', 'my test string', 'send_email', 1" >>%LOGFILE%
--
-- Parametrs:
-- @file_name example: 'C:\temp\testfile.txt'
-- @string example: 'my test string'
-- @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),
    @FileID         int

-- 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: ' + @file_name;
  PRINT 'Parametr 2: ' + @string;
  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

-- Opening a file
EXEC @hr = sp_OAMethod @fso, 'OpenTextFile', @FileID OUT,@file_name, 8, 1 -- Append if required (8)
--execute @hr = sp_OAMethod @fso, 'OpenTextFile', @FileID OUT,@file_name,  1
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @fso
    SELECT    @email_subj = N' Error opening file.' + @procname
  SELECT    @email_body = 'Error opening file' + @file_name + ' . 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

-- Writing Text to File
EXEC @hr = sp_OAMethod @FileID, 'WriteLine', Null, @string
IF @hr <> 0
BEGIN
    EXEC sp_OAGetErrorInfo @fso
    SELECT    @email_subj = N' Error writing to file.' + @procname
  SELECT    @email_body = 'Error writing to file' + @file_name + ' . 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'Writing to file ' + @file_name + ' by procedure ' + @procname
end

EXECUTE @hr = sp_OADestroy @FileID
IF @hr <> 0 EXEC sp_OAGetErrorInfo @fso

-- 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_WriteToFile.zip

Download md5: 221c4765d32b91ae264e6e9d20c64d90