#title SSRS-수동으로 메일보내기
[[TableOfContents]]

==== 수동으로 메일보내기 ====
ssrs에서 subscription 세팅을 하면 SQL Job에 등록된다. 
아래의 Query로 수동으로 메일을 보낼 ScheduleID를 선택하고, job을 실행시키면 된다. 

{{{
select
    b.ScheduleID
,   c.SubscriptionID
,   c.Description
,   c.DeliveryExtension
,   d.Name ReportName
,   d.path ReportPath
from ReportServer.dbo.ReportSchedule a
    inner join ReportServer.dbo.Schedule b
        on a.ScheduleID = b.ScheduleID
    inner join ReportServer.dbo.Subscriptions c
        on a.SubscriptionID = c.SubscriptionID
    inner join ReportServer.dbo.[Catalog] d 
        on a.ReportID = d.ItemID 
        and c.Report_OID = d.ItemID
}}}

SQL Job 시작
{{{
EXEC msdb..sp_start_job @job_name = '66893378-17AF-4D4B-934C-CCE1E82C182E' --b.ScheduleID
}}}


==== 받는 사람 수정하기 ====
{{{
declare 
	@subscription_id nvarchar(100)
,	@to nvarchar(max)

set @subscription_id = '904F7561-0F69-495D-8BA7-83A753A33401' --edit

if object_id('tempdb.dbo.#x') is not null
	drop table #x

select convert(xml, ExtensionSettings) x
into #x
from Subscriptions
where SubscriptionID = @subscription_id

--set @to = N'a@databaser.net'
set @to = N'a@databaser.net;b@databaser.net' --edit

update #x
set x.modify('replace value of (/ParameterValues/ParameterValue[Name="TO"]/Value/text())[1] with (sql:variable("@to"))')

update a
set ExtensionSettings = (select convert(ntext, convert(nvarchar(max), x)) from #x)
from Subscriptions a
where a.SubscriptionID = @subscription_id


select ExtensionSettings
from Subscriptions
where SubscriptionID = @subscription_id
}}}

==== 구독 세팅을 바꾸려면.. ====
{{{dbo.AddEvent}}} 프로시저를 수정하라.
예를 들면..

원본
{{{
ALTER PROCEDURE [dbo].[AddEvent] 
@EventType nvarchar (260),
@EventData nvarchar (260)
AS
insert into [Event] 
    ([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID]) 
values
    (NewID(), @EventType, @EventData, GETUTCDATE(), NULL, NULL)
}}}

수정
{{{
ALTER PROCEDURE [dbo].[AddEvent] 
@EventType nvarchar (260),
@EventData nvarchar (260)
AS

--Subject Update
declare 
	@title nvarchar(200)

if object_id('tempdb.dbo.#x') is not null
	drop table #x

select convert(xml, ExtensionSettings) x
into #x
from Subscriptions
where SubscriptionID = @EventData

select
    @title = [yyyy-mm-dd_format] + '(' + weekday_name_jp + ')'
from dm.common.dim_date
where date_key = convert(date, getdate()-1)

select @title = substring(Value, 1, charindex(':', Value)) + ' ' + @title
from (
    select
        y.item.value('(Name)[1]', 'nvarchar(50)') Name
    ,   y.item.value('(Value)[1]', 'nvarchar(50)') Value
    from #x x
        cross apply x.x.nodes('/ParameterValues/ParameterValue') as y(item)
) t
where Name = 'Subject'
--print @title

update #x
set x.modify('replace value of (/ParameterValues/ParameterValue[Name="Subject"]/Value/text())[1] with (sql:variable("@title"))')

update a
set ExtensionSettings = (select convert(ntext, convert(nvarchar(max), x)) from #x)
from Subscriptions a
where a.SubscriptionID = @EventData


--Add Event
insert into [Event] 
    ([EventID], [EventType], [EventData], [TimeEntered], [ProcessStart], [BatchID]) 
values
    (NewID(), @EventType, @EventData, GETUTCDATE(), NULL, NULL)
}}}