· 4 years ago · Aug 18, 2021, 02:12 PM
1-- sp_send_dbmail : Sends a mail from Yukon outbox.
2--
3CREATE PROCEDURE [dbo].[sp_send_dbmail]
4 @profile_name sysname = NULL,
5 @recipients VARCHAR(MAX) = NULL,
6 @copy_recipients VARCHAR(MAX) = NULL,
7 @blind_copy_recipients VARCHAR(MAX) = NULL,
8 @subject NVARCHAR(255) = NULL,
9 @body NVARCHAR(MAX) = NULL,
10 @body_format VARCHAR(20) = NULL,
11 @importance VARCHAR(6) = 'NORMAL',
12 @sensitivity VARCHAR(12) = 'NORMAL',
13 @file_attachments NVARCHAR(MAX) = NULL,
14 @query NVARCHAR(MAX) = NULL,
15 @execute_query_database sysname = NULL,
16 @attach_query_result_as_file BIT = 0,
17 @query_attachment_filename NVARCHAR(260) = NULL,
18 @query_result_header BIT = 1,
19 @query_result_width INT = 256,
20 @query_result_separator CHAR(1) = ' ',
21 @exclude_query_output BIT = 0,
22 @append_query_error BIT = 0,
23 @query_no_truncate BIT = 0,
24 @query_result_no_padding BIT = 0,
25 @mailitem_id INT = NULL OUTPUT
26 WITH EXECUTE AS 'dbo'
27AS
28BEGIN
29 SET NOCOUNT ON
30
31 -- And make sure ARITHABORT is on. This is the default for yukon DB's
32 SET ARITHABORT ON
33
34 --Declare variables used by the procedure internally
35 DECLARE @profile_id INT,
36 @temp_table_uid uniqueidentifier,
37 @sendmailxml VARCHAR(max),
38 @CR_str NVARCHAR(2),
39 @localmessage NVARCHAR(255),
40 @QueryResultsExist INT,
41 @AttachmentsExist INT,
42 @RetErrorMsg NVARCHAR(4000), --Impose a limit on the error message length to avoid memory abuse
43 @rc INT,
44 @procName sysname,
45 @trancountSave INT,
46 @tranStartedBool INT,
47 @is_sysadmin BIT,
48 @send_request_user sysname,
49 @database_user_id INT,
50 @sid varbinary(85)
51
52 SET @sid = NULL
53
54 -- Initialize
55 SELECT @rc = 0,
56 @QueryResultsExist = 0,
57 @AttachmentsExist = 0,
58 @temp_table_uid = NEWID(),
59 @procName = OBJECT_NAME(@@PROCID),
60 @tranStartedBool = 0,
61 @trancountSave = @@TRANCOUNT
62
63 EXECUTE AS CALLER
64 SELECT @is_sysadmin = IS_SRVROLEMEMBER('sysadmin'),
65 @send_request_user = SUSER_SNAME(),
66 @database_user_id = USER_ID()
67 REVERT
68
69 --Check if SSB is enabled in this database
70 IF (ISNULL(DATABASEPROPERTYEX(DB_NAME(), N'IsBrokerEnabled'), 0) <> 1)
71 BEGIN
72 RAISERROR(14650, 16, 1)
73 RETURN 1
74 END
75
76 --Report error if the mail queue has been stopped.
77 --sysmail_stop_sp/sysmail_start_sp changes the receive status of the SSB queue
78 IF NOT EXISTS (SELECT * FROM sys.service_queues WHERE name = N'ExternalMailQueue' AND is_receive_enabled = 1)
79 BEGIN
80 RAISERROR(14641, 16, 1)
81 RETURN 1
82 END
83
84
85 -- Get the relevant profile_id
86 --
87 IF (@profile_name IS NULL)
88 BEGIN
89 -- Use the global or users default if profile name is not supplied
90 SELECT TOP (1) @profile_id = pp.profile_id
91 FROM msdb.dbo.sysmail_principalprofile as pp
92 WHERE (pp.is_default = 1) AND
93 (dbo.get_principal_id(pp.principal_sid) = @database_user_id OR pp.principal_sid = 0x00)
94 ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
95
96 --Was a profile found
97 IF(@profile_id IS NULL)
98 BEGIN
99 EXEC msdb.dbo.sp_validate_user @send_request_user, @sid OUTPUT
100 SELECT TOP (1) @profile_id = pp.profile_id
101 FROM msdb.dbo.sysmail_principalprofile as pp
102 WHERE (pp.is_default = 1) AND
103 (pp.principal_sid = @sid)
104 ORDER BY dbo.get_principal_id(pp.principal_sid) DESC
105 IF(@profile_id IS NULL)
106 BEGIN
107 RAISERROR(14636, 16, 1)
108 RETURN 1
109 END
110 END
111 END
112 ELSE
113 BEGIN
114 --Get primary account if profile name is supplied
115 EXEC @rc = msdb.dbo.sysmail_verify_profile_sp @profile_id = NULL,
116 @profile_name = @profile_name,
117 @allow_both_nulls = 0,
118 @allow_id_name_mismatch = 0,
119 @profileid = @profile_id OUTPUT
120 IF (@rc <> 0)
121 RETURN @rc
122
123 --Make sure this user has access to the specified profile.
124 --sysadmins can send on any profiles
125 IF ( @is_sysadmin <> 1)
126 BEGIN
127 --Not a sysadmin so check users access to profile
128 iF NOT EXISTS(SELECT *
129 FROM msdb.dbo.sysmail_principalprofile
130 WHERE ((profile_id = @profile_id) AND
131 (dbo.get_principal_id(principal_sid) = @database_user_id OR principal_sid = 0x00)))
132 BEGIN
133 EXEC msdb.dbo.sp_validate_user @send_request_user, @sid OUTPUT
134 IF(@sid IS NULL)
135 BEGIN
136 RAISERROR(14607, -1, -1, 'profile')
137 RETURN 1
138 END
139 END
140 END
141 END
142
143 --Attach results must be specified
144 IF @attach_query_result_as_file IS NULL
145 BEGIN
146 RAISERROR(14618, 16, 1, 'attach_query_result_as_file')
147 RETURN 2
148 END
149
150 --No output must be specified
151 IF @exclude_query_output IS NULL
152 BEGIN
153 RAISERROR(14618, 16, 1, 'exclude_query_output')
154 RETURN 3
155 END
156
157 --No header must be specified
158 IF @query_result_header IS NULL
159 BEGIN
160 RAISERROR(14618, 16, 1, 'query_result_header')
161 RETURN 4
162 END
163
164 -- Check if query_result_separator is specifed
165 IF @query_result_separator IS NULL OR DATALENGTH(@query_result_separator) = 0
166 BEGIN
167 RAISERROR(14618, 16, 1, 'query_result_separator')
168 RETURN 5
169 END
170
171 --Echo error must be specified
172 IF @append_query_error IS NULL
173 BEGIN
174 RAISERROR(14618, 16, 1, 'append_query_error')
175 RETURN 6
176 END
177
178 --@body_format can be TEXT (default) or HTML
179 IF (@body_format IS NULL)
180 BEGIN
181 SET @body_format = 'TEXT'
182 END
183 ELSE
184 BEGIN
185 SET @body_format = UPPER(@body_format)
186
187 IF @body_format NOT IN ('TEXT', 'HTML')
188 BEGIN
189 RAISERROR(14626, 16, 1, @body_format)
190 RETURN 13
191 END
192 END
193
194 --Importance must be specified
195 IF @importance IS NULL
196 BEGIN
197 RAISERROR(14618, 16, 1, 'importance')
198 RETURN 15
199 END
200
201 SET @importance = UPPER(@importance)
202
203 --Importance must be one of the predefined values
204 IF @importance NOT IN ('LOW', 'NORMAL', 'HIGH')
205 BEGIN
206 RAISERROR(14622, 16, 1, @importance)
207 RETURN 16
208 END
209
210 --Sensitivity must be specified
211 IF @sensitivity IS NULL
212 BEGIN
213 RAISERROR(14618, 16, 1, 'sensitivity')
214 RETURN 17
215 END
216
217 SET @sensitivity = UPPER(@sensitivity)
218
219 --Sensitivity must be one of predefined values
220 IF @sensitivity NOT IN ('NORMAL', 'PERSONAL', 'PRIVATE', 'CONFIDENTIAL')
221 BEGIN
222 RAISERROR(14623, 16, 1, @sensitivity)
223 RETURN 18
224 END
225
226 --Message body cannot be null. Atleast one of message, subject, query,
227 --attachments must be specified.
228 IF( (@body IS NULL AND @query IS NULL AND @file_attachments IS NULL AND @subject IS NULL)
229 OR
230 ( (LEN(@body) IS NULL OR LEN(@body) <= 0)
231 AND (LEN(@query) IS NULL OR LEN(@query) <= 0)
232 AND (LEN(@file_attachments) IS NULL OR LEN(@file_attachments) <= 0)
233 AND (LEN(@subject) IS NULL OR LEN(@subject) <= 0)
234 )
235 )
236 BEGIN
237RAISERROR(14624, 16, 1, '@body, @query, @file_attachments, @subject')
238 RETURN 19
239 END
240 ELSE
241 IF @subject IS NULL OR LEN(@subject) <= 0
242 SET @subject='SQL Server Message'
243
244 --Recipients cannot be empty. Atleast one of the To, Cc, Bcc must be specified
245 IF ( (@recipients IS NULL AND @copy_recipients IS NULL AND
246 @blind_copy_recipients IS NULL
247 )
248 OR
249 ( (LEN(@recipients) IS NULL OR LEN(@recipients) <= 0)
250 AND (LEN(@copy_recipients) IS NULL OR LEN(@copy_recipients) <= 0)
251 AND (LEN(@blind_copy_recipients) IS NULL OR LEN(@blind_copy_recipients) <= 0)
252 )
253 )
254 BEGIN
255 RAISERROR(14624, 16, 1, '@recipients, @copy_recipients, @blind_copy_recipients')
256 RETURN 20
257 END
258
259 --If query is not specified, attach results and no header cannot be true.
260 IF ( (@query IS NULL OR LEN(@query) <= 0) AND @attach_query_result_as_file = 1)
261 BEGIN
262 RAISERROR(14625, 16, 1)
263 RETURN 21
264 END
265
266 --
267 -- Execute Query if query is specified
268 IF ((@query IS NOT NULL) AND (LEN(@query) > 0))
269 BEGIN
270 EXECUTE AS CALLER
271 EXEC @rc = sp_RunMailQuery
272 @query = @query,
273 @attach_results = @attach_query_result_as_file,
274 @query_attachment_filename = @query_attachment_filename,
275 @no_output = @exclude_query_output,
276 @query_result_header = @query_result_header,
277 @separator = @query_result_separator,
278 @echo_error = @append_query_error,
279 @dbuse = @execute_query_database,
280 @width = @query_result_width,
281 @temp_table_uid = @temp_table_uid,
282 @query_no_truncate = @query_no_truncate,
283 @query_result_no_padding = @query_result_no_padding
284 -- This error indicates that query results size was over the configured MaxFileSize.
285 -- Note, an error has already beed raised in this case
286 IF(@rc = 101)
287 GOTO ErrorHandler;
288 REVERT
289
290 -- Always check the transfer tables for data. They may also contain error messages
291 -- Only one of the tables receives data in the call to sp_RunMailQuery
292 IF(@attach_query_result_as_file = 1)
293 BEGIN
294 IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)
295 SET @AttachmentsExist = 1
296 END
297 ELSE
298 BEGIN
299 IF EXISTS(SELECT * FROM sysmail_query_transfer WHERE uid = @temp_table_uid AND uid IS NOT NULL)
300 SET @QueryResultsExist = 1
301 END
302
303 -- Exit if there was an error and caller doesn't want the error appended to the mail
304 IF (@rc <> 0 AND @append_query_error = 0)
305 BEGIN
306 --Error msg with be in either the attachment table or the query table
307 --depending on the setting of @attach_query_result_as_file
308 IF(@attach_query_result_as_file = 1)
309 BEGIN
310 --Copy query results from the attachments table to mail body
311 SELECT @RetErrorMsg = CONVERT(NVARCHAR(4000), attachment)
312 FROM sysmail_attachments_transfer
313 WHERE uid = @temp_table_uid
314 END
315 ELSE
316 BEGIN
317 --Copy query results from the query table to mail body
318 SELECT @RetErrorMsg = text_data
319 FROM sysmail_query_transfer
320 WHERE uid = @temp_table_uid
321 END
322
323 GOTO ErrorHandler;
324 END
325 SET @AttachmentsExist = @attach_query_result_as_file
326 END
327 ELSE
328 BEGIN
329 --If query is not specified, attach results cannot be true.
330 IF (@attach_query_result_as_file = 1)
331 BEGIN
332 RAISERROR(14625, 16, 1)
333 RETURN 21
334 END
335 END
336
337 --Get the prohibited extensions for attachments from sysmailconfig.
338 IF ((@file_attachments IS NOT NULL) AND (LEN(@file_attachments) > 0))
339 BEGIN
340 EXECUTE AS CALLER
341 EXEC @rc = sp_GetAttachmentData
342 @attachments = @file_attachments,
343 @temp_table_uid = @temp_table_uid,
344 @exclude_query_output = @exclude_query_output
345 REVERT
346 IF (@rc <> 0)
347 GOTO ErrorHandler;
348
349 IF EXISTS(SELECT * FROM sysmail_attachments_transfer WHERE uid = @temp_table_uid)
350 SET @AttachmentsExist = 1
351 END
352
353 -- Start a transaction if not already in one.
354 -- Note: For rest of proc use GOTO ErrorHandler for falures
355 if (@trancountSave = 0)
356 BEGIN TRAN @procName
357
358 SET @tranStartedBool = 1
359
360 -- Store complete mail message for history/status purposes
361 INSERT sysmail_mailitems
362 (
363 profile_id,
364 recipients,
365 copy_recipients,
366 blind_copy_recipients,
367 subject,
368 body,
369 body_format,
370 importance,
371 sensitivity,
372 file_attachments,
373 attachment_encoding,
374 query,
375 execute_query_database,
376 attach_query_result_as_file,
377 query_result_header,
378 query_result_width,
379 query_result_separator,
380 exclude_query_output,
381 append_query_error,
382 send_request_user
383 )
384 VALUES
385 (
386 @profile_id,
387 @recipients,
388 @copy_recipients,
389 @blind_copy_recipients,
390 @subject,
391 @body,
392 @body_format,
393 @importance,
394 @sensitivity,
395 @file_attachments,
396 'MIME',
397 @query,
398 @execute_query_database,
399 @attach_query_result_as_file,
400 @query_result_header,
401 @query_result_width,
402 @query_result_separator,
403 @exclude_query_output,
404 @append_query_error,
405 @send_request_user
406 )
407
408 SELECT @rc = @@ERROR,
409 @mailitem_id = @@IDENTITY
410
411 IF(@rc <> 0)
412 GOTO ErrorHandler;
413
414 --Copy query into the message body
415 IF(@QueryResultsExist = 1)
416 BEGIN
417 -- if the body is null initialize it
418 UPDATE sysmail_mailitems
419 SET body = N''
420 WHERE mailitem_id = @mailitem_id
421 AND body is null
422
423 --Add CR
424 SET @CR_str = CHAR(13) + CHAR(10)
425 UPDATE sysmail_mailitems
426 SET body.WRITE(@CR_str, NULL, NULL)
427 WHERE mailitem_id = @mailitem_id
428
429 --Copy query results to mail body
430 UPDATE sysmail_mailitems
431 SET body.WRITE( (SELECT text_data from sysmail_query_transfer WHERE uid = @temp_table_uid), NULL, NULL )
432 WHERE mailitem_id = @mailitem_id
433
434 END
435
436 --Copy into the attachments table
437 IF(@AttachmentsExist = 1)
438 BEGIN
439 --Copy temp attachments to sysmail_attachments
440 INSERT INTO sysmail_attachments(mailitem_id, filename, filesize, attachment)
441 SELECT @mailitem_id, filename, filesize, attachment
442 FROM sysmail_attachments_transfer
443 WHERE uid = @temp_table_uid
444 END
445
446 -- Create the primary SSB xml maessage
447 SET @sendmailxml = '<requests:SendMail xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xsi:schemaLocation="http://schemas.microsoft.com/databasemail/requests RequestTypes.xsd" xmlns:requests="http://schemas.microsoft.com/databasemail/requests"><
448MailItemId>'
449 + CONVERT(NVARCHAR(20), @mailitem_id) + N'</MailItemId></requests:SendMail>'
450
451 -- Send the send request on queue.
452 EXEC @rc = sp_SendMailQueues @sendmailxml
453 IF @rc <> 0
454 BEGIN
455 RAISERROR(14627, 16, 1, @rc, 'send mail')
456 GOTO ErrorHandler;
457 END
458
459 -- Print success message if required
460 IF (@exclude_query_output = 0)
461 BEGIN
462 SET @localmessage = FORMATMESSAGE(14635)
463 PRINT @localmessage
464 END
465
466 --
467 -- See if the transaction needs to be commited
468 --
469 IF (@trancountSave = 0 and @tranStartedBool = 1)
470 COMMIT TRAN @procName
471
472 -- All done OK
473 goto ExitProc;
474
475 -----------------
476 -- Error Handler
477 -----------------
478ErrorHandler:
479 IF (@tranStartedBool = 1)
480 ROLLBACK TRAN @procName
481
482 ------------------
483 -- Exit Procedure
484 ------------------
485ExitProc:
486
487 --Always delete query and attactment transfer records.
488 --Note: Query results can also be returned in the sysmail_attachments_transfer table
489 DELETE sysmail_attachments_transfer WHERE uid = @temp_table_uid
490 DELETE sysmail_query_transfer WHERE uid = @temp_table_uid
491
492 --Raise an error it the query execution fails
493 -- This will only be the case when @append_query_error is set to 0 (false)
494 IF( (@RetErrorMsg IS NOT NULL) AND (@exclude_query_output=0) )
495 BEGIN
496 RAISERROR(14661, -1, -1, @RetErrorMsg)
497 END
498
499 RETURN (@rc)
500END