Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
 Результаты запроса в тело почты  [new]
Edemer75
Member

Откуда:
Сообщений: 8
Добрый день. Появилось желание отправлять результаты запроса через Database Mail. Выбивает такую ошибку:

Сообщение 116, уровень 16, состояние 1, строка 41
В списке выбора можно указать только одно выражение, если вложенный запрос не предварен EXISTS.

Отдельно запрос работает замечательно.
Как заставить это Database Mail отправить результаты этого запроса? В связи с тем, что с БД почти не работаю, даже понять не могу о чем речь идет в тексте ошибки. Он не поддерживает LEFT JOIN?


DECLARE @tableHTML  NVARCHAR(MAX) ;  
  
SET @tableHTML =  
       CAST ( (SELECT
      d.name
    , rec_model = d.recovery_model_desc
    , f.full_time
    , f.full_last_date
    , f.full_size
    , f.log_time
    , f.log_last_date
    , f.log_size
FROM sys.databases d
LEFT JOIN (
    SELECT
          database_name
        , full_time = MAX(CASE WHEN [type] = 'D' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
        , full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
        , full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
        , log_time = MAX(CASE WHEN [type] = 'L' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
        , log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
        , log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
    FROM (
        SELECT
              s.database_name
            , s.[type]
            , s.backup_start_date
            , s.backup_finish_date
            , backup_size =
                        CASE WHEN s.backup_size = s.compressed_backup_size
                                    THEN s.backup_size
                                    ELSE s.compressed_backup_size
                        END / 1048576.0
            , RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
        FROM msdb.dbo.backupset s
        WHERE s.[type] IN ('D', 'L')
    ) f
    WHERE f.RowNum = 1
    GROUP BY f.database_name
) f ON f.database_name = d.name
    ) AS NVARCHAR(MAX) );  
  
EXEC msdb.dbo.sp_send_dbmail @recipients='qwe@qwe.qwe',  
    @subject = 'ШумШум',  
    @body = @tableHTML,  
    @body_format = 'HTML' ;


Сообщение было отредактировано: 3 май 17, 15:39
3 май 17, 15:28    [20453494]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
iiyama
Member

Откуда:
Сообщений: 642
Edemer75,
Вы в скалярную переменную пытаетесь засунуть набор данных, о чем сервер Вам и намекает.
как вариант =>
DECLARE @tableHTML NVARCHAR(MAX) ; 

SET @tableHTML = 
CAST ( 
(
SELECT
d.name
, rec_model = d.recovery_model_desc
, f.full_time
, f.full_last_date
, f.full_size
, f.log_time
, f.log_last_date
, f.log_size
FROM sys.databases d
LEFT JOIN (
SELECT
database_name
, full_time = MAX(CASE WHEN [type] = 'D' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_time = MAX(CASE WHEN [type] = 'L' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_start_date
, s.backup_finish_date
, backup_size =
CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) f ON f.database_name = d.name
[color=red]for xml path('') [/color]
) 
AS NVARCHAR(MAX) ); 
3 май 17, 15:41    [20453569]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
iiyama
Member

Откуда:
Сообщений: 642
Edemer75,
прошу прощения, я Вам неправильно хотел подсветить for xml path('') , теги color уберите
3 май 17, 15:43    [20453579]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
iap
Member

Откуда: Москва
Сообщений: 46979
В msdb.dbo.sp_send_dbmail есть параметр @query.
Не подходит?
3 май 17, 15:44    [20453583]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
Edemer75
Member

Откуда:
Сообщений: 8
iiyama, спасибо. Действительно заработало. Правда встал вопрос форматирования результата (строка без разделителей, а хотелось бы табличку), но тут уже другая тема.

iap, с query такая беда, что запрос, обернутый в ' ' некорректно воспринимает типы full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END). Побороть это я не смог, так что взялся за HTML
3 май 17, 16:27    [20453806]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
iap
Member

Откуда: Москва
Сообщений: 46979
Edemer75
iiyama, спасибо. Действительно заработало. Правда встал вопрос форматирования результата (строка без разделителей, а хотелось бы табличку), но тут уже другая тема.

iap, с query такая беда, что запрос, обернутый в ' ' некорректно воспринимает типы full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END). Побороть это я не смог, так что взялся за HTML
Удвоить кавычки не получается?

'full_size = MAX(CASE WHEN [type] = ''D'' THEN backup_size END)'
3 май 17, 16:32    [20453840]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
iiyama
Member

Откуда:
Сообщений: 642
Edemer75,
+1 к iap, но если Вы хотите как-то самостоятельно отформатировать вывод, то можно сделать типа этого
DECLARE @tableHTML NVARCHAR(MAX) ; 

SET @tableHTML = 
N'<table cellspacing="0" cellpadding="0"><thead><tr><td>Имя</td><td>recovery_model</td><td>full_time</td><td>full_last_date</td><td>full_size</td><td>log_time</td><td>log_last_date</td><td>log_size</td></thead></tr>'+
CAST ( 
(
SELECT
  'left' as 'td/@align'
  ,d.name as [td]
, ''
,  'center' as 'td/@align'
, d.recovery_model_desc as [td]
, ''
, f.full_time as [td]
, ''
, f.full_last_date as [td]
, ''
, f.full_size as [td]
, ''
, f.log_time as [td]
, ''
, f.log_last_date as [td]
, ''
, f.log_size as [td]
FROM sys.databases d
LEFT JOIN (
SELECT
database_name
, full_time = MAX(CASE WHEN [type] = 'D' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
, full_last_date = MAX(CASE WHEN [type] = 'D' THEN backup_finish_date END)
, full_size = MAX(CASE WHEN [type] = 'D' THEN backup_size END)
, log_time = MAX(CASE WHEN [type] = 'L' THEN CONVERT(CHAR(10), backup_finish_date - backup_start_date, 108) END)
, log_last_date = MAX(CASE WHEN [type] = 'L' THEN backup_finish_date END)
, log_size = MAX(CASE WHEN [type] = 'L' THEN backup_size END)
FROM (
SELECT
s.database_name
, s.[type]
, s.backup_start_date
, s.backup_finish_date
, backup_size =
CASE WHEN s.backup_size = s.compressed_backup_size
THEN s.backup_size
ELSE s.compressed_backup_size
END / 1048576.0
, RowNum = ROW_NUMBER() OVER (PARTITION BY s.database_name, s.[type] ORDER BY s.backup_finish_date DESC)
FROM msdb.dbo.backupset s
WHERE s.[type] IN ('D', 'L')
) f
WHERE f.RowNum = 1
GROUP BY f.database_name
) f ON f.database_name = d.name
for xml path('tr') 
) 
AS NVARCHAR(MAX) ) +'</table>'; 

select @tableHTML

но лучше сделать отчет в RS и настроить доставку (ИМХО)
3 май 17, 16:46    [20453897]     Ответить | Цитировать Сообщить модератору
 Re: Результаты запроса в тело почты  [new]
Edemer75
Member

Откуда:
Сообщений: 8
iiyama, я Вас всей свой сущностью благодарю. Сэкономили мне кучу времени.
4 май 17, 09:56    [20455527]     Ответить | Цитировать Сообщить модератору
Все форумы / Microsoft SQL Server Ответить