SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

/****** Object:  SQL_SCALAR_FUNCTION  fun_FolderExist       Script Date: 2026-05-15 19:31:09.387 ******/
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.387 ******/
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_agent       Script Date: 2026-05-15 19:31:09.387 ******/
IF EXISTS (SELECT * FROM [dbo].[sysobjects] WHERE ID = object_id(N'[dbo].[prc_dump_survive_info_agent]')) DROP PROCEDURE [dbo].[prc_dump_survive_info_agent];
GO

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

PURPOSE
Generate sqlserver agent settings text file.

The text filename name is info_sqlagent.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
For info_agent.txt
 - msdb.dbo.sp_get_sqlagent_properties;
 - xp_instance_regread (undocumented extended procedure)

SQLSERVER CONFIGURATION
 - OLE Automation has to be enabled.

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

HISTORY
2026-04-09 - Created procedure

TAGS
<program>
  <description>Generate sqlserver agent 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 @sqlagent_properties TABLE (
    [auto_start] int null,
    [msx_server_name] sysname null,
    [sqlagent_type] int null,
    [startup_account] nvarchar(255) null,
    [sqlserver_restart] int null,
    [jobhistory_max_rows] int null,
    [jobhistory_max_rows_per_job] int null,
    [errorlog_file] nvarchar(255) null,
    [errorlogging_level] int null,
    [error_recipient] nvarchar(30) null,
    [monitor_autostart] int null,
    [local_host_server] sysname null,
    [job_shutdown_timeout] int null,
    [cmdexec_account] varbinary(64) null,
    [regular_connections] int null,
    [host_login_name] sysname null,
    [host_login_password] varbinary(512) null,
    [login_timeout] int null,
    [idle_cpu_percent] int null,
    [idle_cpu_duration] int null,
    [oem_errorlog] int null,
    [sysadmin_only] int null,
    [email_profile] nvarchar(64) null,
    [email_save_in_sent_folder] int null,
    [cpu_poller_enabled] int null,
    [alert_replace_runtime_tokens] int null);
  DECLARE @auto_start INT
  DECLARE @sqlagent_type INT
  DECLARE @startup_account NVARCHAR(128)
  DECLARE @sqlserver_restart INT
  DECLARE @jobhistory_max_rows INT
  DECLARE @jobhistory_max_rows_per_job INT
  DECLARE @alert_replace_runtime_tokens INT
  DECLARE @use_database_mail INT;
  DECLARE @mail_profile_name NVARCHAR(255);

  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_sqlagent.txt';

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

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

  INSERT INTO @sqlagent_properties (
    [auto_start],
    [msx_server_name],
    [sqlagent_type],
    [startup_account],
    [sqlserver_restart],
    [jobhistory_max_rows],
    [jobhistory_max_rows_per_job],
    [errorlog_file],
    [errorlogging_level],
    [error_recipient],
    [monitor_autostart],
    [local_host_server],
    [job_shutdown_timeout],
    [cmdexec_account],
    [regular_connections],
    [host_login_name],
    [host_login_password],
    [login_timeout],
    [idle_cpu_percent],
    [idle_cpu_duration],
    [oem_errorlog],
    [sysadmin_only],
    [email_profile],
    [email_save_in_sent_folder],
    [cpu_poller_enabled],
    [alert_replace_runtime_tokens])
  EXEC msdb.dbo.sp_get_sqlagent_properties;

  SELECT
    @auto_start = [auto_start],
    @sqlagent_type = [sqlagent_type],
    @startup_account = [startup_account],
    @sqlserver_restart = [sqlserver_restart],
    @jobhistory_max_rows = [jobhistory_max_rows],
    @jobhistory_max_rows_per_job = [jobhistory_max_rows_per_job],
    @alert_replace_runtime_tokens = [alert_replace_runtime_tokens]
  FROM @sqlagent_properties;

  -- Use_database_mail
  EXEC xp_instance_regread N'HKEY_LOCAL_MACHINE', N'SOFTWARE\Microsoft\MSSQLServer\SQLServerAgent', N'UseDatabaseMail', @param = @use_database_mail OUT;

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

  SELECT @body = 
    'SQL Server agent settings' + @crlf +
    '------------------------------'+ @crlf +
    'Info_date                   : ' + ISNULL(@info_date,'') + @crlf +
    'Server_name                 : ' + ISNULL(@server_name,'') + @crlf +
    'Auto_start                  : ' + ISNULL(CONVERT(VARCHAR,@auto_start),'') + @crlf +
    'Sqlagent_type               : ' + ISNULL(CONVERT(VARCHAR,@sqlagent_type),'') + @crlf +
    'Startup_account             : ' + ISNULL(@startup_account,'') + @crlf +
    'Sqlserver_restart           : ' + ISNULL(CONVERT(VARCHAR,@sqlserver_restart),'') + @crlf +
    'Jobhistory_max_rows         : ' + ISNULL(CONVERT(VARCHAR,@jobhistory_max_rows),'') + @crlf +
    'Jobhistory_max_rows_per_job : ' + ISNULL(CONVERT(VARCHAR,@jobhistory_max_rows_per_job),'') + @crlf +
    'Alert_replace_runtime_tokens: ' + ISNULL(CONVERT(VARCHAR,@alert_replace_runtime_tokens),'') + @crlf +
    'Use_database_mail           : ' + ISNULL(CONVERT(VARCHAR,@use_database_mail),'') + @crlf +
    'Mail_profile_name           : ' + ISNULL(@mail_profile_name,'') + @crlf;

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