dbo.GetSchedulerAlertEmail.StoredProcedure.sql
3.98 KB
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
USE [AIADatabaseV5]
GO
/****** Object: StoredProcedure [dbo].[GetSchedulerAlertEmail] Script Date: 2/1/2018 12:15:55 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[GetSchedulerAlertEmail]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[GetSchedulerAlertEmail]
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