SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.393 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[fun_FolderExist]')) DROP FUNCTION [dbo].[fun_FolderExist];
GO


CREATE FUNCTION [dbo].[fun_FolderExist] (
  @dirname VARCHAR(265))
RETURNS BIT
AS
BEGIN
/*
SQLServer User-Defined Function

PURPOSE
Check if folder exists.

Return value (BIT):
 0 (False) - Folder does not exist
 1 (True)  - Folder exists

EXAMPLES
SELECT dbo.fun_FolderExist('C:\Program Files')
1

SELECT dbo.fun_FolderExist('C:\xxxx')
0

-- IIF is available starting with SQL Server 2012
SELECT IIF(dbo.fun_FolderExist('C:\Program Files')=1,'Yes','No')
Yes

HISTORY
2015-11-24 - Created function
2024-02-09 - Changed history table
2026-01-23 - Changed return value type to BIT
2026-03-30 - Changed examples

TAGS
<program>
  <description>Check if folder exists</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2015-11-24</created>
  <lastmodified>2026-03-30</lastmodified>
</program>
*/
  DECLARE @fso INT
  DECLARE @hr INT
  DECLARE @ofolder INT
  DECLARE @name VARCHAR(265)
  DECLARE @result BIT

  EXEC @hr = sp_OACreate 'Scripting.FileSystemObject', @fso OUT;
  EXEC @hr = sp_OAMethod @fso, 'FolderExists', @ofolder OUT, @dirname;
  EXEC @hr = sp_OADestroy @fso;

  SET @result = @ofolder;
  RETURN @result;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_save_text_to_file       Script Date: 2026-05-15 19:31:09.393 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_save_text_to_file]')) DROP PROCEDURE [dbo].[prc_save_text_to_file];
GO


CREATE PROCEDURE [dbo].[prc_save_text_to_file] (
  @text VARCHAR(MAX), 
  @filename VARCHAR(265))
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Write text variable to ansi file.

OLE Automation Procedures has to be enabled.
Existing files will be overwritten.
If path does not exist, no error is raised. Check path before with function fun_FolderExist.
If file could not be created, no error is raised. Check file afterwards with function fun_FileExist.
Character set Windows-1252 is used, 1 byte per character.
Character set Windows-1252 includes ascii extended characters dec 128-159, except dec 129, 141, 143, 144 and 157.
Character set iso-8859-1 does not handle ascii extended characters dec 128-159 in a proper way, so this character set is unusable here.

Parameters
1 @text       VARCHAR(MAX)  = (nonbinary) text
2 @filename   VARCHAR(265)  = filespec

HISTORY
2017-01-25 - Created procedure
2017-01-31 - iso-8859-1 (Latin-1) character set is used
2025-06-06 - Windows-1252 character set is used

TAGS
<program>
  <description>Write text variable to ansi file</description>
  <generic>1</generic>
  <author>Gerrit Mantel</author>
  <created>2017-01-25</created>
  <lastmodified>2025-06-06</lastmodified>
</program>
*/
  DECLARE @objtoken INT

  EXEC sp_OACreate 'ADODB.Stream', @objtoken OUTPUT;
  EXEC sp_OASetProperty @objtoken, 'Type', 2;
  EXEC sp_OASetProperty @objtoken, 'ContentType', 'text/plain'
  EXEC sp_OASetProperty @objtoken, 'Encoding', 'quoted-printable'
  EXEC sp_OASetProperty @objtoken, 'Charset', 'Windows-1252'
  EXEC sp_OAMethod @objtoken, 'Open';
  EXEC sp_OAMethod @objtoken, 'WriteText', NULL, @text;
  EXEC sp_OAMethod @objtoken, 'SaveToFile', NULL, @filename, 2;
  EXEC sp_OAMethod @objtoken, 'Close';
  EXEC sp_OADestroy @objtoken;
END


GO

/****** Object:  SQL_STORED_PROCEDURE  prc_dump_survive_info_dbmail       Script Date: 2026-05-15 19:31:09.393 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_dump_survive_info_dbmail]')) DROP PROCEDURE [dbo].[prc_dump_survive_info_dbmail];
GO

CREATE PROCEDURE [dbo].[prc_dump_survive_info_dbmail] (
  @dump_path VARCHAR(255) = '')
AS
BEGIN
/*
SQLServer Stored Procedure

PURPOSE
Generate database mail settings text file.

The script filename name is info_dbmail.txt and is dumped to the give dump_path.
The dump_path must exist and will be checked.

PARAMETERS
 - 1 @dump_path VARCHAR(255)   Path to dump file survivekit.sql to.

USED OBJECTS

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

EXAMPLES
EXEC dbo.prc_dump_survive_info_dbmail @dump_path='C:\Data\MSSQL\survivekit'

HISTORY
2026-04-09 - Created procedure

TAGS
<program>
  <description>Generate database mail settings text file</description>
  <generic>0</generic>
  <author>Gerrit Mantel</author>
  <created>2026-04-09</created>
  <lastmodified>2026-04-09</lastmodified>
</program>
*/
  SET NOCOUNT ON

  DECLARE @message VARCHAR(512)
  DECLARE @crlf VARCHAR(2)
  DECLARE @body NVARCHAR(MAX)
  DECLARE @dump_file VARCHAR(265)

  DECLARE @info_date VARCHAR(20)
  DECLARE @server_name NVARCHAR(128)
  DECLARE @mail_profile_name NVARCHAR(255);
  DECLARE @mail_profile TABLE (
    [profile_id] INT NULL,
    [name] NVARCHAR(128) NULL,
    [description] NVARCHAR(256) NULL);
  DECLARE @mail_profile_account TABLE (
    [profile_id] INT NULL,
    [profile_name] NVARCHAR(128) NULL,
    [account_id] INT NULL,
    [account_name] NVARCHAR(128) NULL,
    [sequence_numer] INT NULL);
  DECLARE @mail_account TABLE (
    [account_id] INT NULL,
    [name] NVARCHAR(128) NULL,
    [description] NVARCHAR(256) NULL,
    [email_address] NVARCHAR(128) NULL,
    [display_name] NVARCHAR(128) NULL,
    [replyto_address] NVARCHAR(128) NULL,
    [server_type] NVARCHAR(128) NULL,
    [server_name] NVARCHAR(128) NULL,
    [port] INT NULL,
    [username] NVARCHAR(128) NULL,
    [use_default_credentials] INT NULL,
    [enable_ssl] BIT);
  DECLARE @mail_profile_id INT
  DECLARE @mail_account_id INT
  DECLARE @mail_account_name NVARCHAR(128)
  DECLARE @mail_description NVARCHAR(256)
  DECLARE @mail_email_address NVARCHAR(128)
  DECLARE @mail_replyto_address NVARCHAR(128)
  DECLARE @mail_server_type NVARCHAR(128)
  DECLARE @mail_server_name NVARCHAR(128)
  DECLARE @mail_port INT
  DECLARE @mail_username NVARCHAR(128)
  DECLARE @mail_use_default_credentials INT
  DECLARE @mail_enable_ssl BIT

  SET @crlf = CHAR(13)+CHAR(10);

  IF ISNULL(@dump_path,'') = ''
  BEGIN
    SET @message = 'ERROR - No dump path given';
    RAISERROR(@message, 16, 1);
    RETURN 1
  END

  -- Strip '\' from dump_path
  IF RIGHT(@dump_path,1) = '\' SET @dump_path = LEFT(@dump_path,LEN(@dump_path)-1);

  -- Check if dump_path exists
  IF dbo.fun_FolderExist(@dump_path) = 0
  BEGIN
    SET @message = 'ERROR - Dump path "' + @dump_path + '" does not exist';
    RAISERROR(@message, 16, 1);
    RETURN 1;
  END

  -- Dump file name
  SET @dump_file = @dump_path + '\info_database_mail.txt';

  -- Info date
  SET @info_date = CONVERT(VARCHAR,GETDATE(),120);

  -- Server name
  SET @server_name = @@SERVERNAME;

  -- Mail profile_name
  EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'DatabaseMailProfile', @param = @mail_profile_name OUT;

  -- Mail profile_id
  INSERT INTO @mail_profile ([profile_id], [name], [description])
  EXEC msdb.dbo.sysmail_help_profile_sp @profile_name = @mail_profile_name;
  SET @mail_profile_id = (SELECT [profile_id] FROM @mail_profile);

  -- Mail profile account_id
  INSERT INTO @mail_profile_account ([profile_id], [profile_name], [account_id], [account_name], [sequence_numer])
  EXEC msdb.dbo.sysmail_help_profileaccount_sp @profile_id = @mail_profile_id;
  SET @mail_account_id = (SELECT [account_id] FROM @mail_profile_account);

  -- Mail account
  INSERT INTO @mail_account ([account_id], [name], [description], [email_address], [display_name], [replyto_address], [server_type], [server_name], [port], [username], [use_default_credentials], [enable_ssl])
  EXEC msdb.dbo.sysmail_help_account_sp @account_id = @mail_account_id;

  SELECT
    @mail_account_name = [name],
    @mail_description = [description],
    @mail_email_address = [email_address],
    @mail_replyto_address  = [replyto_address],
    @mail_server_type = [server_type],
    @mail_server_name = [server_name],
    @mail_port = [port],
    @mail_username = [username],
    @mail_use_default_credentials = [use_default_credentials],
    @mail_enable_ssl = [enable_ssl]
  FROM @mail_account;

  SELECT @body = 
    'Database mail settings (profile used for the SQL Server Agent only)' + @crlf +
    '------------------------------'+ @crlf +
    'Info_date                   : ' + ISNULL(@info_date,'') + @crlf +
    'Server_name                 : ' + ISNULL(@server_name,'') + @crlf +
    'Profile_name                : ' + ISNULL(@mail_profile_name,'') + @crlf +
    'Account_name                : ' + ISNULL(@mail_account_name,'') + @crlf +
    'Description                 : ' + ISNULL(@mail_description,'') + @crlf +
    'Email_address               : ' + ISNULL(@mail_email_address,'') + @crlf +
    'Replyto_address             : ' + ISNULL(@mail_replyto_address,'') + @crlf +
    'Server_type                 : ' + ISNULL(@mail_server_type,'') + @crlf +
    'Server_name                 : ' + ISNULL(@mail_server_name,'') + @crlf +
    'Port                        : ' + ISNULL(CONVERT(VARCHAR,@mail_port),'') + @crlf +
    'Username                    : ' + ISNULL(@mail_username,'') + @crlf +
    'Use_default_credentials     : ' + ISNULL(CONVERT(VARCHAR,@mail_use_default_credentials),'') + @crlf +
    'Enable_ssl                  : ' + ISNULL(CONVERT(VARCHAR,@mail_enable_ssl),'') + @crlf;

  -- Dump script
  EXEC [dbo].[prc_save_text_to_file] @body, @dump_file;
END
GO