USE [AIADatabaseV5]
GO
/****** Object:  StoredProcedure [dbo].[GetSchedulerAlertEmail]    Script Date: 02/06/2018 10:49:30 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[GetSchedulerAlertEmail] @sSchedulerEmail VARCHAR(255) OUTPUT, @sDeploymentEnvironment VARCHAR(255) OUTPUT
AS
BEGIN
	DECLARE @FileName varchar(255)
	DECLARE @ExecCmd VARCHAR(255)
	DECLARE @y INT
	DECLARE @x INT
	DECLARE @FileContents VARCHAR(8000)
	DECLARE @idoc INT
	DECLARE @sSchedulerAlertEMailVariable VARCHAR(255) = 'SchedulerAlertEMail'
	DECLARE @sEnvironmentVariable VARCHAR(255) = 'DeploymentEnvironment'
	DECLARE @datapath VARCHAR(255)

	CREATE TABLE #tempXML(PK INT NOT NULL IDENTITY(1,1), ThisLine VARCHAR(255))

	EXEC master..xp_regread 'HKEY_LOCAL_MACHINE','SOFTWARE\A.D.A.M.','DBConnectionXMLLocation',@datapath OUTPUT
	
	SET @FileName = '"'+@datapath+'"'
	SET @ExecCmd = 'type ' + @FileName
	SET @FileContents = ''

	INSERT INTO #tempXML EXEC master.dbo.xp_cmdshell @ExecCmd
	SELECT @y = COUNT(*) from #tempXML

	SET @x = 0
	WHILE @x <> @y
		BEGIN
			SET @x = @x + 1
			SELECT @FileContents = @FileContents + ThisLine FROM #tempXML WHERE PK
	 = @x
		END
	SET @FileContents = (SELECT REPLACE(@FileContents,'<?xml version="1.0" standalone="yes"?>','<?xml version="1.0" encoding="ISO8859-1"?>'))
	DROP TABLE #tempXML 

	EXEC sp_xml_preparedocument @idoc OUTPUT, @FileContents

	SET @sSchedulerEmail = (SELECT Value FROM OPENXML(@idoc,'/DBConnection/EnvironmentVariable',2)
	WITH (Name VARCHAR(MAX),
			Value VARCHAR(MAX) ) WHERE Name = @sSchedulerAlertEMailVariable)
			
	SET @sDeploymentEnvironment = (SELECT Value FROM OPENXML(@idoc,'/DBConnection/EnvironmentVariable',2)
	WITH (Name VARCHAR(MAX),
			Value VARCHAR(MAX) ) WHERE Name = @sEnvironmentVariable)			

END
GO