Send Different Data to Different Users
-
this is my stored procedure for sending data of employees' requests to their email.
i want to send not responded requests to them seperately. So i think i should select the email addresses of employees first. then execute another query for every email address.
DECLARE cr_Email CURSOR FOR
select distinct K.email from ST$Istek I
Left Join [dbo].[ST$Kullanicilar] B ON I.istek_sahibi=B.id
left join ST$Kullanicilar K on I.Istek_Sorumlusu= K.id
left join ST$IstekSatirlari IST on I.istekid=IST.IstekNo
where istek_durumu 1 and K.email is not null
Declare @email varchar(70)
OPEN cr_Email
FETCH next from cr_Email into @email
WHILE @@FETCH_STATUS =0
begin
select distinct istekid,istek1+istek2 as Istek,B.isim as isteksahibi
from ST$Istek I
Left Join [dbo].[ST$Kullanicilar] B ON I.istek_sahibi=B.id
left join ST$Kullanicilar K on I.Istek_Sorumlusu= K.id
left join ST$IstekSatirlari IST on I.istekid=IST.IstekNo
where istek_durumu 1 and K.email=@email
FETCH next from cr_Email into @email
end
Close cr_Email
DEALLOCATE cr_Email
You can see the result of executed stored procedure in sql server management studio
So as you can see this stored procedure has 3 result set. it must be execute for every result set and send 3 mail to 3 different people
but when i execute this stored procedure on REPORTING SERVICES there is only 1 result set.
another problem is how could i sent this report to my selected email address (with subscriptions? or database mail code? can i write @email varible to the To: section in subscription?)
how could i do this in reporting services? also how can i arrange the email subscriptions ?
in the first execution the first result set will be sent to the first email address second set will be sent to second address...
how could i done it?
-
hi,
U can easily do this via data driven subscription. In fact it gives you option to write query to select your email address according to your condition. The article below will give you basic idea about subscription.
Believe me, it is very easy to set up. U need to give more time and learn it. refer to following link.
http://www.sqlservercentral.com/articles/Development/datadrivensubscriptions/2432/
regards,
vijay
-
first thanks for the link
i study on it and write some extra code for myself but it gives error and it didnt loop again!
here is my code (i made bold italic and underlined my cursor code)
ALTER procedure [dbo].[data_driven_subscription]
( @scheduleName nvarchar(255)='Prod Olmayan Istekleriniz',
@emailTO varchar (2000) = NULL,
@emailCC varchar (2000) = 'ercaneraslan@st-erp.com',
@emailReplyTO nvarchar (2000) = 'info@st-erp.com',
@emailBODY nvarchar (4000) = NULL,
@parameterName nvarchar(4000) = 'email',
@parameterValue nvarchar (256) = NULL,
@sub nvarchar(1000) = 'SBO dan Sevgilerle',
@renderFormat nvarchar(50) = 'PDF',
@exitCode int output,
@exitMessage nvarchar(255) output
)
AS
DECLARE
@ptrval binary(16),
@PARAMptrval binary(16),
@subscriptionID uniqueidentifier,
@scheduleID uniqueidentifier,
@starttime datetime,
@lastruntime datetime,
@execTime datetime,
@dVALUES nvarchar (4000),
@pVALUES nvarchar (4000),
@previousDVALUES nvarchar (4000),
@previousPVALUES nvarchar (4000),
@lerror int,
@insertID int,
@lretval int,
@rowcount int,
SET @starttime = DATEADD(second, -2, getdate())
SET @emailTO = rtrim(IsNull(@emailTO, ''))
SET @emailCC = rtrim(IsNull(@emailCC, ''))
SET @emailReplyTO = rtrim(IsNull(@emailReplyTO, ''))
SET @emailBODY = rtrim(IsNull(@emailBODY, ''))
SET @parameterValue = rtrim(IsNull(@parameterValue, ''))
SET @lerror = 0
SET @rowcount = 0
IF @emailTO = '' AND @emailCC = ''
BEGIN
SET @exitCode = -1
SET @exitMessage = 'A recipient is required.'
RETURN 0
END
DECLARE cr_Email CURSOR FOR
select distinct K.email from ST$Istek I
Left Join [dbo].[ST$Kullanicilar] B ON I.istek_sahibi=B.id
left join ST$Kullanicilar K on I.Istek_Sorumlusu= K.id
left join ST$IstekSatirlari IST on I.istekid=IST.IstekNo
where istek_durumu 1 and K.email is not null
Declare @emailValue varchar(100)
OPEN cr_Email
FETCH next from cr_Email into @emailValue
WHILE @@FETCH_STATUS =0
begin
set @emailTO= @emailValue
set @parameterValue=@emailValue
-- get the subscription ID
SELECT
@subscriptionID = rs.subscriptionID,
@scheduleID = rs.ScheduleID
FROM
[ReportServer].dbo.ReportSchedule rs
INNER JOIN [ReportServer].dbo.subscriptions s
ON rs.subscriptionID = s.subscriptionID
WHERE
extensionSettings like '%' + @scheduleName + '%'
IF @subscriptionID Is Null
BEGIN
SET @exitCode = -2
SET @exitMessage = 'The subscription does not exist.'
RETURN 0
END
/* just to be safe */
SET @dVALUES = ''
SET @pVALUES = ''
SET @previousDVALUES = ''
SET @previousPVALUES = ''
/* apply the settings that are defined */
IF IsNull(@emailTO, '') <> ''
SET @dVALUES = @dVALUES + ' '
+ @emailTO + ' '
IF IsNull(@emailCC, '') <> ''
SET @dVALUES = @dVALUES + ' '
+ @emailCC + ' '
IF IsNull(@emailReplyTO, '') <> ''
SET @dVALUES = @dVALUES + ' '
+ @emailReplyTO + ' '
IF IsNull(@emailBODY, '') <> ''
SET @dVALUES = @dVALUES + ' '
+ @emailBODY + ' '
IF IsNull(@sub, '') <> ''
SET @dVALUES = @dVALUES + ' '
+ @sub + ' '
IF IsNull(@dVALUES , '') <> ''
SET @dVALUES = ' ' + @dVALUES
+ ' '
IF IsNull(@dVALUES , '') <> ''
SET @dVALUES = @dVALUES +' ' +
@renderFormat + ' ' +
' '
IF IsNull(@parameterName, '') <> '' and IsNull(@parameterValue, '') <> ''
SET @pVALUES = ' ' +
@parameterName +
' ' +
@parameterValue +
' '
/* verify that some delivery settings where passed in */
-- @pVALUES are not checked as they may all be defaults
IF IsNull(@dVALUES , '') = ''
BEGIN
SET @exitCode = -3
SET @exitMessage = 'No delivery settings were supplied.'
RETURN 0
END
/* get the current parameter values and delivery settings */
SELECT @previousDVALUES = extensionSettings
FROM [ReportServer].dbo.Subscriptions
WHERE SubscriptionID = @SubscriptionID
SELECT @previousPVALUES = parameters
FROM [ReportServer].dbo.Subscriptions
WHERE SubscriptionID = @SubscriptionID
UPDATE [ReportServer].dbo.Subscriptions
SET extensionSettings = '', parameters = ''
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -5
SET @exitMessage = 'A data base error occurred clearing the previous subscription settings.'
RETURN IsNull(@lerror, 0)
END
-- set the text point for this record
SELECT @ptrval = TEXTPTR(ExtensionSettings)
FROM [ReportServer].dbo.Subscriptions
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error
IF @lerror <> 0 OR @ptrval Is NULL
BEGIN
SET @exitcode = -6
SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.'
RETURN IsNull(@lerror, 0)
END
UPDATETEXT [ReportServer].dbo.Subscriptions.ExtensionSettings
@ptrval
null
null
@dVALUES
SELECT @lerror=@@error
IF @lerror <> 0
BEGIN
SET @exitcode = -7
SET @exitMessage = 'A data base error occurred updating the Delivery settings.'
RETURN IsNull(@lerror, 0)
END
-- set the text point for this record
SELECT @PARAMptrval = TEXTPTR(Parameters)
FROM [ReportServer].dbo.Subscriptions
WHERE SubscriptionID = @SubscriptionID
SELECT @lerror=@@error
IF @lerror <> 0 OR @ptrval Is NULL
BEGIN
SET @exitcode = -8
SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.'
RETURN IsNull(@lerror, 0)
END
UPDATETEXT [ReportServer].dbo.Subscriptions.Parameters
@PARAMptrval
null
null
@pVALUES
SELECT @lerror=@@error
IF @lerror <> 0
BEGIN
SET @exitcode = -9
SET @exitMessage = 'A data base error occurred updating the Parameter settings.'
RETURN IsNull(@lerror, 0)
END
/* insert a record into the history table */
SET @execTime = getdate()
INSERT [ReportServer].dbo.Subscription_History
(subscriptionID, scheduleName, ParameterSettings, DeliverySettings, dateExecuted, executeStatus)
VALUES
(@subscriptionID, @scheduleName, @parameterValue, @dVALUES , @execTime, 'incomplete' )
SELECT @lerror=@@error, @insertID=@@identity
IF @lerror <> 0 OR IsNull(@insertID, 0) = 0
BEGIN
SET @exitcode = -4
SET @exitMessage = 'A data base error occurred inserting the subscription history record.'
RETURN IsNull(@lerror, 0)
END
-- run the job
EXEC msdb..sp_start_job @job_name = @scheduleID
-- this gives the report server time to execute the job
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
WHILE (@starttime > @lastruntime)
BEGIN
WAITFOR DELAY '00:00:02'
SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID
END
/* update the history table with the completion time */
UPDATE [ReportServer].dbo.Subscription_History
SET dateCompleted = getdate()
WHERE subscriptionID = @subscriptionID
and scheduleName = @scheduleName
and ParameterSettings = @parameterValue
and dateExecuted = @execTime
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -10
SET @exitMessage = 'A data base error occurred updating the subscription history record.'
RETURN IsNull(@lerror, 0)
END
/* reset the previous delivery and parameter values */
UPDATE [ReportServer].dbo.Subscriptions
SET extensionSettings = @previousDVALUES
, parameters = @previousPVALUES
WHERE SubscriptionID = @SubscriptionID
FETCH next from cr_Email into @emailValue
end
Close cr_Email
DEALLOCATE cr_Email
SELECT @lerror=@@error, @rowCount=@@rowCount
IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0
BEGIN
SET @exitcode = -11
SET @exitMessage = 'A data base error occurred resetting the previous subscription settings.'
RETURN IsNull(@lerror, 0)
END
/* return the result of the subscription */
SELECT @exitMessage = LastStatus
FROM [ReportServer].dbo.subscriptions
WHERE subscriptionID = @subscriptionID
SET @exitCode = 1
RETURN 0
when i execute the code in management studio it returns me this :
(1 row(s) affected)
(1 row(s) affected)
Job 'B53A0B81-1E07-42C3-8116-FED710E2FFE1' started successfully.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Job 'B53A0B81-1E07-42C3-8116-FED710E2FFE1' started successfully.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
Msg 22022, Level 16, State 1, Line 0
SQLServerAgent Error: Request to run job B53A0B81-1E07-42C3-8116-FED710E2FFE1 (from User sa) refused because the job already has a pending request from User sa.
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
(1 row(s) affected)
why it gives me that error what is my mistake and why it didnt get in the loop again after executes 1 time?
Could anyone can help me?
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply