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 md5: 221c4765d32b91ae264e6e9d20c64d90
Сообщения
