Produced by Araxis Merge on 5/2/2019 1:17:38 PM Central Daylight Time. See www.araxis.com for information about Merge. This report uses XHTML and CSS2, and is best viewed with a modern standards-compliant browser. For optimum results when printing this report, use landscape orientation and enable printing of background images and colours in your browser.
# | Location | File | Last Modified |
---|---|---|---|
1 | C:\AraxisMergeCompare\Pri_un\EPRS\EPRS_ODSDB\EPRS.ODSDB\EPRS.ODSDB\Programmability\Stored Procedures | uspDoAdminNotificationsCount.sql | Thu Apr 18 16:51:44 2019 UTC |
2 | C:\AraxisMergeCompare\Pri_re\EPRS\EPRS_ODSDB\EPRS.ODSDB\EPRS.ODSDB\Programmability\Stored Procedures | uspDoAdminNotificationsCount.sql | Tue Apr 30 12:50:36 2019 UTC |
Description | Between Files 1 and 2 |
|
---|---|---|
Text Blocks | Lines | |
Unchanged | 3 | 226 |
Changed | 2 | 4 |
Inserted | 0 | 0 |
Removed | 0 | 0 |
Whitespace | |
---|---|
Character case | Differences in character case are significant |
Line endings | Differences in line endings (CR and LF characters) are ignored |
CR/LF characters | Not shown in the comparison detail |
No regular expressions were active.
1 | -- ======= ========== ========== ========== ======== | |
2 | -- Descrip tion: Noti fies admin istrators by email o f the numb er of user accounts that have been added /changed | |
3 | -- | |
4 | -- Mainten ance Log: | |
5 | -- | |
6 | -- Update By Upd ate Date Descrip tion | |
7 | -- ------- - --- ------ --------- ---------- --------- | |
8 | ||
9 | -- ======= ========== ========== ========== ======== | |
10 | CREATE PRO CEDURE [db o].[uspDoA dminNotifi cationsCou nt] | |
11 | ||
12 | AS | |
13 | DE CLARE @ACC ESS_STATUS varchar(2 5), | |
14 | @USER_ ROLE varch ar(25), | |
15 | @TABLE _DATA NVAR CHAR(max), | |
16 | @BODY_ DATA NVARC HAR(MAX), | |
17 | @useri d int, | |
18 | @email varchar(1 00), | |
19 | @COUNT int | |
20 | ||
21 | DE CLARE @res ultsTable table | |
22 | ( | |
23 | User RoleID int , | |
24 | Date Created Da tetime2 | |
25 | ) | |
26 | ||
27 | DECL ARE @email List table | |
28 | ( | |
29 | User ID int, | |
30 | emai lAddress v archar(50) , | |
31 | Name varchar(5 0), | |
32 | Role Name varch ar(25) | |
33 | ) | |
34 | BEGIN | |
35 | -- SET NOCOU NT ON adde d to preve nt extra r esult sets from | |
36 | -- interferi ng with SE LECT state ments. | |
37 | SE T NOCOUNT ON; | |
38 | ||
39 | BEGI N TRY | |
40 | BEGI N | |
41 | SET @C OUNT = 0 | |
42 | SET @A CCESS_STAT US = 'REQU ESTED' | |
43 | SET @U SER_ROLE = 'ADMINIST RATOR' | |
44 | ||
45 | INSERT INTO @res ultsTable (UserRoleI D, DateCre ated) | |
46 | SELECT seclyr.Us erRoles.Us erRoleID, seclyr.Use rRoles.Dat eCreated | |
47 | FROM s eclyr.User Roles | |
48 | WHERE UPPER(se clyr.UserR oles.Acces sStatus) = @ACCESS_S TATUS | |
49 | ||
50 | SET @C OUNT = (SE LECT COUNT (DISTINCT UserRoleID ) | |
51 | FROM @ resultsTab le) | |
52 | END | |
53 | ||
54 | BEGI N | |
55 | INSERT INTO @ema ilList (Us erID, emai lAddress, Name, Role Name) | |
56 | SELECT DISTINC T seclyr. Users.User ID, seclyr .Users.Ema ilAddress, seclyr.Us ers.LastNa me + ', ' +seclyr.Us ers.FirstN ame AS Nam e, seclyr. Roles.Role Name | |
57 | FROM s eclyr.Role s INNER JO IN | |
58 | sec lyr.UserRo les ON sec lyr.Roles. RoleID = s eclyr.User Roles.Role ID INNER J OIN | |
59 | sec lyr.Users ON seclyr. UserRoles. UserID = s eclyr.User s.UserID | |
60 | WHERE UPPER(secl yr.Roles.R oleName) = @USER_ROL E | |
61 | AND UP PER(seclyr .UserRoles .AccessSta tus) = 'AP PROVED' | |
62 | END | |
63 | ||
64 | IF @ COUNT > 0 | |
65 | BEGI N | |
66 | SET @B ODY_DATA = '<html><b ody><h3>En terprise P rogram Rep orting Sys tem action s</h3><h4> There are currently ' + | |
67 | FORMAT(@ COUNT, '#, 0')+ | |
68 | ' pendin g user acc ount actio ns that re quire atte ntion.</h4 > | |
69 | <p>Run D ate: ' + | |
70 | CONVERT( varchar(18 ), GETDATE (), 100) + | |
71 | '<p>You ca n access t hese actio ns here: < a href="ht tps:// DNS .webdev.ts m.occ DOMAIN . EXT /EPRSDev/A dministrat ion">User Worklist</ a></p> | |
72 | <p><br / >This mess age was ge nerated fr om an auto mated serv er. <u>Do not reply </u> to th is message .</b></p> | |
73 | </body ></html>' | |
74 | ||
75 | DECLAR E userid C URSOR FOR | |
76 | SELECT userid FR OM @emailL ist | |
77 | ||
78 | OPEN u serid | |
79 | ||
80 | set @u serid = 0 | |
81 | set @e mail = '' | |
82 | ||
83 | FETCH FROM useri d into @us erid | |
84 | WHILE @@FETCH_ST ATUS = 0 | |
85 | BEGIN | |
86 | SET @ema il = (Sele ct top 1 e mailAddres s from @em ailList wh ere UserID = @userid ) | |
87 | ||
88 | FETCH NE XT FROM us erid into @userid | |
89 | ||
90 | EXEC msd b.dbo.sp_s end_dbmail @profile_ name='SQL Admins', | |
91 | @recipie nts=@email , | |
92 | --@copy_re cipients = 'steve.ko pecky@ DOMAIN . EXT ;kenneth.b aker2@ DOMAIN . EXT ;joshua.da ncy@ DOMAIN . EXT ', | |
93 | @subject ='EPRS Adm inistrator Notificat ions', | |
94 | @body=@B ODY_DATA, | |
95 | @body_fo rmat = 'HT ML' | |
96 | END | |
97 | CLOSE userid | |
98 | DEALLO CATE useri d | |
99 | RETURN ( @COUNT ) | |
100 | END | |
101 | ELSE | |
102 | BEGI N | |
103 | RETURN (0) | |
104 | END | |
105 | ||
106 | END TRY | |
107 | ||
108 | BEGI N CATCH | |
109 | PRIN T 'There w as an erro r sending admin noti fication c ounts' + C HAR(13); | |
110 | SELE CT * FROM dbo.ufnGet ErrorInfo( ); | |
111 | EXEC uspRaiseE rrorInfo; | |
112 | EN D CATCH | |
113 | ||
114 | END | |
115 | GO |
Araxis Merge (but not the data content of this report) is Copyright © 1993-2016 Araxis Ltd (www.araxis.com). All rights reserved.