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.
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.idleft join ST$IstekSatirlari IST on I.istekid=IST.IstekNo where istek_durumu 1 and K.email is not nullDeclare @email varchar(70)OPEN cr_EmailFETCH next from cr_Email into @emailWHILE @@FETCH_STATUS =0beginselect 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.idleft join ST$IstekSatirlari IST on I.istekid=IST.IstekNo where istek_durumu 1 and K.email=@emailFETCH next from cr_Email into @emailendClose cr_EmailDEALLOCATE cr_Emailwhen 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 setso first result set will be sent to first email adress of the first querythe 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 setwhat 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. |
 |
|
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.idleft join ST$IstekSatirlari IST on I.istekid=IST.IstekNo where istek_durumu 1 and K.email is not nullwhen 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? |
 |
|
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. |
 |
|
elephantman
Starting Member
6 Posts |
Posted - 2008-10-22 : 06:33:31
|
ok thanks i wiil start to learn writing .net function |
 |
|
|
|
|