Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 SQL Server 2005 Forums
 Analysis Server and Reporting Services (2005)
 Sent seperate emails to each employee

Author  Topic 

elephantman
Starting Member

6 Posts

Posted - 2008-10-20 : 11:20:48
this is my stored procedure :

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

when i execute it in sql server 2005 management studio the result is :



but when i execute this stored procedure in reporting services the result covers only the first result set!!!


i want to take my filtered employees emails and start a loop and execute another query for every one of them. And send email to each of them.

in the first picture you can see 3 result set
so first result set will be sent to first email adress of the first query
the second result set will be sent to second email adress of the first query...

but when i execute this stored procedure in reporting services there is 1 result set

what is my problem? how can i send emails to each employees?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-20 : 12:39:00
SSRS will take only first resultset. so to get whole resultset in report put partial reports to temporary table and finally select from it.
Go to Top of Page

elephantman
Starting Member

6 Posts

Posted - 2008-10-22 : 03:31:08
ok assume that i put partial reports to temporary table BUT then how can i send that report to its owner which i select its email in the first query :

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

when first loop is finished it should be pass to the next one and then the next one?

Where can i arrange this? in subscription sections? can i put any variable name to the recipients sections?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-10-22 : 04:11:27
i dont think you can use variables in subscription sections through report manager. However, you could try writing a .NET function to do this by implementing webservice of reporting services.
Go to Top of Page

elephantman
Starting Member

6 Posts

Posted - 2008-10-22 : 06:33:31
ok thanks i wiil start to learn writing .net function
Go to Top of Page
   

- Advertisement -