Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Microsoft SQL Server Новый топик    Ответить
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
 Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
Уважаемый форум!

К сожалению не нашел ответа. Исходное положение: есть триггер, который срабатывает на изменение значения в поле db5_Index и должен отсылать мыло с результатами запроса. Код:
ALTER TRIGGER [dbo].[Trigger_DB5_Index] 
   ON  [dbo].[tbl_DB5]
   AFTER UPDATE
AS
IF UPDATE(db5_Index)
BEGIN
	IF (SELECT db5_Index FROM Inserted)='2' or (SELECT db5_Index FROM Inserted)='3'
	BEGIN
		SET NOCOUNT ON;
		EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'TEST',
				@recipients = 'test@test.com;',
				@importance ='High',
				@subject = 'Index has been changed',
				@query_result_header=1,
				@query='SELECT * FROM View_DB5_FL WHERE(fin_MOPI_No in (SELECT fin_MOPI_No FROM Inserted))';
	END
END

При изменении значения в поле получаю ошибку "Error formatting query, probably invalid parameters". Подскажите, куда смотреть и копать? Спасибо заранее!
9 янв 13, 19:00    [13743388]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
Боюсь, процедуре sp_send_dbmail неизвестны значения из таблицы inserted.
9 янв 13, 19:14    [13743436]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
Jovanny
Member

Откуда:
Сообщений: 1195
В смысле, сама таблица ей неизвестна.
9 янв 13, 19:16    [13743445]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
Jovanny
Боюсь, процедуре sp_send_dbmail неизвестны значения из таблицы inserted.


Да, уже прочитал примечание мелким шрифтом на МСДН. :(
Следующий вопрос: как можно обойти?
9 янв 13, 19:16    [13743449]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
Во-первых, триггер в корне неверен.
Во-вторых, такие вещи в триггере не делают.
9 янв 13, 19:25    [13743474]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
invm
Во-первых, триггер в корне неверен.
Во-вторых, такие вещи в триггере не делают.


А можно более развернутое объяснение? А то как-то ничего не понятно: почему триггер в корне неверен? Почему такие вещи в триггере не делают? А где делают? Буду благодарен. :)
9 янв 13, 19:37    [13743510]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
invm
Member

Откуда: Москва
Сообщений: 9413
IFK
почему триггер в корне неверен?
Потому что таблица inserted может содержать более одной строки, а у вас это не учитывается.
IFK
Почему такие вещи в триггере не делают? А где делают? Буду благодарен. :)
Триггер не должен содержать потенциально долгоиграющих операций, ибо таковые приводят к увеличению времени удержания блокировок, что отрицательно сказывается на конкурентности доступа к данным.
В вашем случае нужно письма отсылать асинхронно. Например, через буферную таблицу + job, либо Service Broker.
9 янв 13, 19:52    [13743561]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
invm
IFK
почему триггер в корне неверен?
Потому что таблица inserted может содержать более одной строки, а у вас это не учитывается.
IFK
Почему такие вещи в триггере не делают? А где делают? Буду благодарен. :)
Триггер не должен содержать потенциально долгоиграющих операций, ибо таковые приводят к увеличению времени удержания блокировок, что отрицательно сказывается на конкурентности доступа к данным.
В вашем случае нужно письма отсылать асинхронно. Например, через буферную таблицу + job, либо Service Broker.


Хорошо, так лучше?
ALTER TRIGGER [dbo].[Trigger_DB5_Index] 
   ON  [dbo].[tbl_DB5]
   AFTER UPDATE
AS
	DECLARE @res varchar(max)
IF UPDATE(db5_Index)
BEGIN
	IF (SELECT db5_Index FROM Inserted)='2' or (SELECT db5_Index FROM Inserted)='3'
	BEGIN
		SET NOCOUNT ON;
		Set @res=(SELECT DBMID FROM Inserted)
		SELECT [fin_BAP],[fin_Crop],[mate_Origin],[mate_Type],[fin_Supplier],[mate_Description],[db5_Sample],[fin_MOPI_No],[db5_Case_No],[db5_Date_Eval_PD],[db5_Category],[db5_Position_PD],[db5_Index],[db5_Comment_PD],[db5_Remarks],[db5_Evaluated_By] INTO tbl_Temp_Email FROM View_DB5_FL WHERE(DBMID=@res)
		Set @res=(SELECT * FROM tbl_Temp_Email)
		EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'TEST',
				@recipients = 'test@test.com;',
				@importance ='High',
				@subject = 'Index has been changed',
				@query=@res;
		DROP TABLE tbl_Temp_Email
	END
END

Проблема все равно осталась, теперь ошибка: Only one expression can be specified in the select list when subquery is not introduces with EXISTS. Какого, собсна, хрена? Есть идеи?
9 янв 13, 19:57    [13743585]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
IFK,

И что непонятно из сообщения об ошибке?
9 янв 13, 21:16    [13743792]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
IFK
теперь ошибка: Only one expression can be specified in the select list when subquery is not introduces with EXISTS. Какого, собсна, хрена? Есть идеи?
Синтаксис неправильный.

Ппи вызове процедуры в @res должен быть текст запроса.
9 янв 13, 23:09    [13744191]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
NIIIK
Member

Откуда: Россия, Ростовская область, г. Таганрог
Сообщений: 1295
формируем строкой HTML код и потом отправляем.

Можно даже без курсора :)
10 янв 13, 00:08    [13744319]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
user89
Member

Откуда:
Сообщений: 2083
IFK,

на всякий случай Database mail
10 янв 13, 09:19    [13744905]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
gang
Member

Откуда:
Сообщений: 1394
IFK,

Не знаю насколько близка моя задача вашей, но я схожим способом делал мониторинг изменений в джобах (чтобы смежники не безобразничали).
Схема такая:
1) При изменении на контролируемой таблице (ах) триггер кладет строчки в таблицу-лог.
2) На таблице-логе еще 1 триггер который постит через master..xp_logevent в errorlog запись
с пользовательским кодом евента (>50000). Строка из таблицы inserted при этом разворачивается в текст.
Примерно так:
+
ALTER TRIGGER [dbo].[tr_jobs_changes]
   ON [dbo].[jobs_changes] 
   AFTER INSERT
AS 
BEGIN
	-- SET NOCOUNT ON added to prevent extra result sets from
	-- interfering with SELECT statements.
	SET NOCOUNT ON;
	declare @message varchar(255)
	set @message='Job Modification Detected'
	select @message=@message+'
Modification: '+replace(replace(replace(replace(subsystem,'del','Delete Job'),'Create','Create Job'),'Alter','Alter Job'),'TSQL','Executable Code Change') +'
EventSeqID: '+convert (varchar(10),event_id)+'
Time: '+convert (varchar(30),Cur_TIME,121)+'
Host: '+host+'
Login: '+loginname+'
Job: '+job_name+'
Step: '+step_name 
	from inserted
	exec master..xp_logevent 51111 , @message , error
END

3) На этот код евента настроен алерт с отправкой мыла.
Приходит письмо вида:
+
DATE/TIME: 1/10/2013 9:01:24 AM

DESCRIPTION: Error: 51111 Severity: 16 State: 1 Job Modification Detected
Modification: Executable Code Change
EventSeqID: 1085
Time: 2013-01-10 09:01:24.977
Host: oparator_host_name
Login: Domain\Login
Job: some_job_name
Step: some_job_step_name


COMMENT: Investigate details on log table by EventSeqID

JOB RUN: (None)
10 янв 13, 09:49    [13745081]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
gang
Member

Откуда:
Сообщений: 1394
В общем основная идея по большому счету такая же как у NIIIK - передавать на отправку не запрос, а его отформатированный нужным образом результат.
10 янв 13, 09:51    [13745106]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
user89
IFK,

на всякий случай Database mail


Спасибо всем, но можно как-нибудь без HTML - я в нем нифига не разбираюсь.

Насчет ошибки: чисто технически я понимаю, о чем речь, я не понимаю, какого хрена и в где именно? Вроде бы в query передается тупая команда на выборку, что ему еще не нравится?
10 янв 13, 12:41    [13746261]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
Glory
Member

Откуда:
Сообщений: 104760
IFK
я не понимаю, какого хрена и в где именно?

Перед сообщение об ошибке выводится номер строки, где она произошла
10 янв 13, 12:46    [13746295]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
Glory
IFK
я не понимаю, какого хрена и в где именно?

Перед сообщение об ошибке выводится номер строки, где она произошла


Да уже допер методом тыка, что не нравится, когда пытаюсь вывести все поля из временной таблицы tbl_Temp_Email.
SELECT @res=(SELECT * FROM tbl_Temp_Email)
С одним полем проходит на ура. Отсюда вопрос: как передать результаты запроса по всем полям в тело письма?
10 янв 13, 12:49    [13746318]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
alexeyvg
Member

Откуда: Moscow
Сообщений: 31446
IFK
Да уже допер методом тыка, что не нравится, когда пытаюсь вывести все поля из временной таблицы tbl_Temp_Email.
SELECT @res=(SELECT * FROM tbl_Temp_Email)

С одним полем проходит на ура. Отсюда вопрос: как передать результаты запроса по всем полям в тело письма?
Я же вам написал, как передать.

Ещё можно посмотреть в справке по sp_send_dbmail, пример (Б)
10 янв 13, 12:52    [13746338]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
Glory
Member

Откуда:
Сообщений: 104760
IFK
Отсюда вопрос: как передать результаты запроса по всем полям в тело письма?

Изучить синтаксис sp_send_dbmail
Особенно параметров @query

Также изучить область видимости виртуальных таблиц inserted/deleted
10 янв 13, 13:00    [13746390]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
alexeyvg
IFK
теперь ошибка: Only one expression can be specified in the select list when subquery is not introduces with EXISTS. Какого, собсна, хрена? Есть идеи?
Синтаксис неправильный.

Ппи вызове процедуры в @res должен быть текст запроса.


Что значит, при вызове процедуры в @res должен быть текст запроса? Сделал так:
ALTER TRIGGER [dbo].[Trigger_DB5_Index] 
   ON  [dbo].[tbl_DB5]
   AFTER UPDATE
AS
	DECLARE @res varchar(max)
IF UPDATE(db5_Index)
BEGIN
	IF (SELECT db5_Index FROM Inserted)='2' or (SELECT db5_Index FROM Inserted)='3'
	BEGIN
		SET NOCOUNT ON;
		Set @res=(SELECT DBMID FROM Inserted)
		SELECT [fin_BAP],[fin_Crop],[mate_Origin],[mate_Type],[fin_Supplier],[mate_Description],[db5_Sample],[fin_MOPI_No],[db5_Case_No],[db5_Date_Eval_PD],[db5_Category],[db5_Position_PD],[db5_Index],[db5_Comment_PD],[db5_Remarks],[db5_Evaluated_By] INTO tbl_Temp_Email FROM View_DB5_FL WHERE(DBMID=@res)
		--SELECT @res=(SELECT * FROM tbl_Temp_Email)
		EXEC msdb.dbo.sp_send_dbmail
				@profile_name = 'test',
				@recipients = 'test@test.com;',
				@importance ='High',
				@subject = 'Index has been changed',
				@query='SELECT * FROM tbl_Temp_Email';
		DROP TABLE tbl_Temp_Email
	END
END

Все равно не работает, пишет, что Error formating query, probably invalid parameters. Какие нахрен параметры, если запрос без параметров? :(
10 янв 13, 13:01    [13746399]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
Glory
IFK
Отсюда вопрос: как передать результаты запроса по всем полям в тело письма?

Изучить синтаксис sp_send_dbmail
Особенно параметров @query

Также изучить область видимости виртуальных таблиц inserted/deleted


Да уже исключил локальные таблицы, видно же. :( Особенность параметра @query по мануалу - любой действительный SQL запрос. В чем проблема-то? :(
10 янв 13, 13:03    [13746407]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
Glory
Member

Откуда:
Сообщений: 104760
IFK
Да уже исключил локальные таблицы, видно же

Пока мне лично видно, что каждый предоставляемый вами код содержит ошибки
10 янв 13, 13:06    [13746432]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
Glory
IFK
Да уже исключил локальные таблицы, видно же

Пока мне лично видно, что каждый предоставляемый вами код содержит ошибки


Товарищ Glory, а можно попредметнее? Неужели трудно исправить ошибку, вместо того, чтобы глубокомысленно сообщать, что каждый код содержит ошибку? СПАСИБО ЗАРАНЕЕ!

Вопрос простой: как из таблицы выбрать ВСЕ поля и отправить результат выборки в теле письма, БЕЗ использования предварительного форматирования HTML. Спасибо!
10 янв 13, 13:09    [13746452]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
pkarklin
Member

Откуда: Москва (Муром)
Сообщений: 74925
IFK,

Вот здесь

@query='SELECT * FROM tbl_Temp_Email';


попробуйте указать полное имя объекта: database_name.schema_name.object_name.
10 янв 13, 13:27    [13746603]     Ответить | Цитировать Сообщить модератору
 Re: Отправка мыла с сервера с результатами запроса в теле письма  [new]
IFK
Member

Откуда: Гондурас
Сообщений: 1025
pkarklin
IFK,

Вот здесь

@query='SELECT * FROM tbl_Temp_Email';


попробуйте указать полное имя объекта: database_name.schema_name.object_name.


Тоже самое: Error formating query, probably invalid parameters. :(:(
10 янв 13, 13:34    [13746677]     Ответить | Цитировать Сообщить модератору
Топик располагается на нескольких страницах: [1] 2 3 4   вперед  Ctrl      все
Все форумы / Microsoft SQL Server Ответить