Reporting Services :: Data-Driven Subscription Is Not Working

Oct 1, 2015

We are facing an issue related to SSRS 2012. We have Reporting Services 2012 configured on a couple of servers. On one server, we have created Datasources and Data-driven subscriptions for our reports and selected email as the report delivery option. But the subscriptions are not working. The LastRunTime column is NULL in the Subscriptions table in the ReportServer database and no email is being triggered. Also, no error message is displayed. The same reporting solution is working on another server that also uses Reporting Services 2012.

View 14 Replies


ADVERTISEMENT

Reporting Services :: Last Successful Run Of Data Driven Subscription

Aug 26, 2015

I have a Data Driven Subscription which runs every day for the previous day. If for any reason the report subscription fails, i would like to get the last time it ran successfully. I know in the subscription table we have lastRunTime, but it will show the recent one which failed but i would like to know when it ran successfully last. 

Example:
Saturday 5:00 AM - Ran Successfully
Sunday   5:00 AM - Ran Successfully
Monday   5:00 AM - Ran successfully
Tuesday  5:00 AM - Failed
LastRunTime column will have Tuesday 5:00 AM. 

Is there a way to get Monday 5:00 AM which is the last ran successfully ?? How can i get the history of a subscription?

View 8 Replies View Related

Reporting Services :: Change File Extension Of CSV Data Driven Subscription?

Jun 16, 2015

Using SSRS 2008R2 is it possible to change the file extension of a CSV data driven subscription?  I'm outputting a text file with a .csv extension, but users have access to these files and I don;t want them opened with Excel and then saved back in an incorrect format. 

These are the options I have from the report "manage" option.  

View 2 Replies View Related

Reporting Services :: SSRS Error With Data Driven Subscription Execution

Jun 1, 2015

I have an issue with a data driven subscription under SSRS 2008R2 in which users are emailed a message based on a condition. For the last two months the majority of the users were mailed the message successfully but this month two users failed to get their message. I checked the selected email address in our mail system and they seemed to be correct. But running these two address (unlike the others which ran sucssfully) caused an error and they did not receive their message.
 
The error in the SSRS log read:
 
Line 96485: library!ReportServer_0-81!b9c!05/29/2015-13:25:49:: Call to DeleteItemAction(|eju4fz45zsbn3sykjtpuegec|@|/XX/XXYYZZ/ Report Name123|).
Line 96486: library!ReportServer_0-81!b9c!05/29/2015-13:25:49:: e ERROR: Throwing Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException: , Microsoft.ReportingServices.Diagnostics.Utilities.ItemNotFoundException:
The item '|eju4fz45zsbn3sykjtpuegec|@|/XX/YYZZAA/ReportName123 |' cannot be found.;

View 3 Replies View Related

Reporting Services :: Format File Name In EMAIL Data Driven Subscription

Mar 12, 2014

how can i dynamically specify the name of a report before sending in an email (example @reportName@datetime@parameter1@ServerName)

I have read most of the related articles and i am still curious about this topic - the technology is there so why wouldn't microsoft just add a REPORTNAME field to the email data driven subscription?

View 4 Replies View Related

Reporting Services :: Data Driven Subscription Button Is Missing - SSRS 2012

Jun 23, 2015

The 'Data Driven Subscription Button' is missing from the Subscription option for any report developed in Microsoft SQL Server 2012 SSDT...

Following version is running on SQL Server : Microsoft SQL Server 2012 (SP1) - 11.0.3000.0 (X64)

 Oct 19 2012 13:38:57
 Copyright (c) Microsoft Corporation
 Standard Edition (64-bit) on Windows NT 6.1 <X64> (Build 7601: Service Pack 1)

And Report Server Log is saying: resourceutilities!WindowsService_15!a64!06/23/2015-10:05:08:: i INFO: Reporting Services starting SKU: Enterprise

I am having all SSRS roles including Content Manager,  etc..

View 3 Replies View Related

Reporting Services :: Data Driven Subscription With Dynamic Email Attachment File Name

Feb 11, 2010

I have report which accepts a card number and fromdate and todate as parameters to the report. This report needs to be sent on a quarterly basis to each of the customer mail id to which their card number is linked. I am getting all this information from a database and sending as an attachment to the customer. Now I would need making these filename's which are attached to be dynamic based on the input parameters.

In datadriven subscriptions, the option of include report has only true or false values and another option was to take from a database. I tried putting the dynamic file name in the database and getting the value from the database but no success, the subscription itself is failing here. I guess I am doing something wrong here by binding the report name from the value which I am getting from DB to the actual report name.

If report name = Mytransactions, and the parameters passed are Card = 123,fromdate = 1/1/2010,todate = 31/3/2010.

Now in the attachment the file name should be something like "Mytransactions_123_January1st2010_March31st2010".
How to make the filename dynamic.

View 9 Replies View Related

Reporting Services :: Data Driven Subscription Notification Via Email For SSRS Report

Aug 26, 2015

I want to create data driven subscription via Email for SSRS report.

To do so i can add my self either in To or CC.

I don't want multiple mails in my inbox as sent mails have excel as attachment, but I want notification for all the mails sent.

View 2 Replies View Related

Reporting Services :: Identify Failed Subscriptions In Data Driven Subscription Batch

Apr 20, 2015

I have a single data driven subscription that is a batch of 250 subscriptions running on SQL Server 2008 R2. All the parameter values for the report are sourced from a database table. During the execution of the data driven subscription 5 of those subscriptions failed due to timeout error.

Final Status as shown on Subscription screen "Done: 250 processed of 250 total; 5 errors."

The SSRS log file has the subscription GUID which is a single record in the reporting server database tables. How can i identify the individual failed subscriptions? Is there an easy way or a work around that i should implement to identify the failed subscriptions?

View 5 Replies View Related

Reporting Services :: Output A Report As Text File (TXT) In Data Driven Subscription?

Apr 22, 2015

Is it possible to create a Data Driven Subscription report as a text file output to a shared folder?

View 6 Replies View Related

Data-driven Subscription Not Working

May 30, 2007

I created a data-driven subscription for my report with "Null Delivery Provider" set in report manager. I set the processing to "On a schedule created for this subscription" and scheduled the report to "run once" in the next 1 minute so I could test it. The subscription added successfully, but I don't see the "last run information" even after 5 minutes. Am I missing something?



Thanks in advance!!!

View 1 Replies View Related

Reporting Services :: Data Driven Email Subscription With Different Email Per Report Page

Jul 6, 2015

I have a report that gets sends out through a subscription and sometimes the report has multiple pages and all those pages appear within one email.Is it possible to set the subscription in such a way that an email is sent per page when the subscription executes.

View 2 Replies View Related

Reporting Services :: SSRS Email Subscription Not Working?

May 20, 2015

sql server edition-2008 enterprise evaluation edition

Operating System- Window Server 2008 R2

I've Created Local Smtp to use gmailid for Subscription of SSRS Report by Email as we don't have an Exchange Server.

User this link to Create local SMTP

Studied few blogs that related it to agent service running under local system account, on my machine it was running on NT/NetworkService change it to Administrator of local Machine. Did same change for Database Engine of Service of SQL Instance.

FYI- My Machine is not in Domain, so used local administrator for giving rights to service mentioned above.

Below is the Screen Shot of rsreportserver.Config File

ERROR : on Report Manager

Below is report log on after time report was executed.

library!WindowsService_16!2b30!05/20/2015-13:19:05:: i INFO: Schedule 3374e590-8374-4360-b5da-d487eacb0417 executed at 05/20/2015 13:19:05.
schedule!WindowsService_16!2b30!05/20/2015-13:19:05:: Creating Time based subscription notification for subscription: ea7fc6ce-00df-4f6a-9050-e713986307ca
library!WindowsService_16!2b30!05/20/2015-13:19:05:: i INFO: Schedule 3374e590-8374-4360-b5da-d487eacb0417 execution completed at 05/20/2015 13:19:05.

[code]....

PS: Local SMTP is working, i.e. i am able to receive email if i place an email.text file in C:inetpubmailrootpickup

View 7 Replies View Related

Reporting Services :: Table Data Types For Data Driven Subscriptions

Jun 11, 2015

I am trying to find a reference for a client that lists the fields available to be substituted into a data driven subscription from the query, along with the expected data types.  For example, the field on whether or not to include a link to the report seems to be expecting a bit data type.I have searched and can't seem to find anything.  I guess I could walk through the interface and try different data types, but if  a list exists, that would be better. 

View 4 Replies View Related

Reporting Services :: How To Read Data (email) From Report In Data Driven SSRS Subscriptions

Jul 28, 2015

get the data from report to create data driven SSRS Subscriptions,

write query :

Running Report:

View 5 Replies View Related

Reporting Services :: SSRS Data Driven Subscriptions In SharePoint

Sep 30, 2015

I am looking for a good tutorial on how to set up Data Driven subscriptions through the SharePoint 2012 version of SSRS. I am needing to set up a Data Driven subscription that uses one report, but sends out the specific portion of the report to the appropriate recipients. I have used the information located at URL.... but it doesn't seem to provide all the steps needed to schedule the reports needed. It focuses around setting up the reports to go to shared folder locations. I need something around setting it up to go to email recipients. 

View 5 Replies View Related

Reporting Services :: Using Multi-Valued Parameters With Data-Driven Subscriptions

Jun 27, 2009

I have researched this question and so far have found very different opinions as to how or if it is possible.I have created 2 Data-driven subscriptions in SSRS. 1 for our sales persons and another for our managers reports. For the salespersons report, the solution works brilliantly as expected and I can appreciated the increased simplicitly for having 1 subscription to drive reports to a sales staff of over 100 folks.One the other hand, I can't seem to grasp the significance of this type of solution for the Sales managers when they have multiple salespersons that report to them.

Apparently, SSRS 2008 has provided no practical solution for this scenario that would be easily implemented in a Data driven subscription.Since I can't find a way to pass in muliple integer values that represent each of the salesreps for a single manager, I am stuck with potentially sending multiple reports for each of the Sales Reps residing under a single manager. Not very elegant or useful as I'd hoped for using Data driven subscriptions. I have even changed the parameter datatype to string and used something like:

paramSalesPeople = substring((SELECT ( ', ' + CAST(territoryid AS VARCHAR(2000)))
FROM Reports..SalesForce t2
WHERE t1.SalesManagerID = t2.SalesManagerID
ORDER BY SalesManagerID
FOR XML PATH( '' )), 3, 1000 )

To create a comma-delimited list of values for the parameters in effort of generating 1 (ONE) record per Manager. But the Data Driven Subscription fails miserably.So the question, Is it or is it not possible to have multiple values passed as a single parameter to a data driven subscription to consolidate the number of required reports into one. If not, it would seem that it should be possible since it can be done from the reports parameters drop-down menu.I have read someones recommendation to script the multi-valued parameter which seems to defeat the intent of the term "Data Driven' Subscription if I have to hard-code this logic into a script.

View 8 Replies View Related

Enterprise Editon Of Sql Reporting Services Not Displaying Data-driven Subscriptions Option.

Mar 27, 2008

I am running the following ENTERPRISE version of SQL Server, I am admin on the box and do not have the New data-driven Subscription showing up on the Subscription tab.

Does anyone have any ideas why?

Edition Product Level Product Version
-----------------------------------------------------------------------
Enterprise Edition SP4 8.00.2039




View 5 Replies View Related

Data Driven Subscription

Dec 21, 2006

I have create a data driven subscription that should email to to different people. It is sent to one but not the other. if I set up a normal subscription to the email it works. I can not see anywhere in the log the problem. Any Ideas on how to find out what is causing it.

View 8 Replies View Related

Data-driven Subscription

May 15, 2008

Hi
I created a new data-driven subscription which successfully created a guid job on the server under jobs. So, I run the job and I get no report emailed to me. On the report manager all it says is I have 1 error. I cant figure out what the error is and where do I go to see what the error is? I know it is in some log file under MSSQL, but I dont know which log file and what I should be looking for in that log file. Any help is appreciated.

I guess, in summary, how do I debug a data-driven subscription?

View 1 Replies View Related

What Could Cause This Timeout In Data-Driven Subscription ? ...

Dec 10, 2007

Hello,

Any help is greatly appreciated...

SQL Server Reporting Services 2005. I have a data-driven subscription that executes weekly and had been executing just fine until this past execution. In the Subscriptions tab of the Report Manager web interface the Status says:

Error: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Now, the stored procedure that returns data for the subscription has never taken more than about half a second to run (it does not return much data; only 55 records, and it is efficient and optimized). The timeout interval was 30 seconds, but I increased it to 300 seconds just in case...same result when I reschedule to subscription.

None of the procedures which return data for the actual report (there are six of them) takes more than 1 second to execute either, and I can generate the report from the RS web interface just fine; this only fails and gives a Timeout when I try to schedule the report on the data-driven subscription.


Is there any way to tell WHERE the timeout occurred, what step or process? I ran a Profiler trace of all activity on the ReportServer database after re-seheduling the subscription, and saw nothing that seemed unusual. I see the timeout error in the log file "ReportServerService__12_10_2007_00_06_03.log", but no useful details.


Has anyone else encountered this issue, or similar? Please let me know if there's anywhere else I might check.

Thank you,
Ethan
ewiers@heritageacademies.com

View 2 Replies View Related

Cannot Create Data-driven Subscription

Nov 17, 2006

Hi,

I'm using SQL Server 2005 Developer Edition, which I understand supports Data-driven subscriptions (DDS).

When I try and click on on the DDS button for a report I get the following message:

"Data-driven subscriptions cannot be created because the credentials used to run the report are not stored, the report is using user-defined parameter values, or if a linked report, the link is no longer valid."

1. I have set the data source to use stored credentials.

2. I'm using one simple string parameter

3. Its not a linked report

Any ideas?

regards,

avner

View 15 Replies View Related

Problem With Data-driven Subscription

Nov 16, 2006

I have a data-driven subscription that launches once a day and delivers
emails to subscribers. The problem is that it delivers only about half
the emails. It runs ok and then it produces error. I ran it once and it
ran ok and completed without errors. 20 minutes later it produced
errors. Log looks like this:



ReportingServicesService!library!c!11/16/2006-10:27:45::
i INFO: Call to RenderFirst( '/Subscribed notifications/Subscribed
notifications' )

ReportingServicesService!library!11!11/16/2006-10:27:45:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information., AuthzInitializeContextFromSid:
Win32 error: 58;

Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information.

ReportingServicesService!library!c!11/16/2006-10:27:45:: e ERROR:
Throwing
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information., AuthzInitializeContextFromSid:
Win32 error: 58;

Info:
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information.

ReportingServicesService!library!11!11/16/2006-10:27:45:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in
Server system properties.

ReportingServicesService!library!c!11/16/2006-10:27:45:: i INFO:
Initializing EnableExecutionLogging to 'True' as specified in
Server system properties.

ReportingServicesService!emailextension!11!11/16/2006-10:27:45:: Error
sending email.
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The
report server has encountered a configuration error. See the report
server log files for more information. --->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information.

at Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)

at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String userName)

at Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String userName, IntPtr userToken)

at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String
userName, IntPtr userToken, Byte[] secDesc, ReportOperation
requiredOperation)

at
Microsoft.ReportingServices.Library.RSService.GetReportAndDataSourcesForExecution(ClientRequest
session, CatalogItemContext itemContext, String queryParametersXml,
Boolean& hasData, DataSourceInfoCollection& dataSources,
String& reportProperties, ReportSnapshot& intermediateSnapshot,
Guid& reportId, ReportSnapshot& snapshotData, String&
description, Boolean& cachingRequested, DateTime&
executionDateTime, DateTime& expirationDateTime, Int32&
execOptions, Boolean& foundInCache)

at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)

at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)

at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()

at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()

--- End of inner exception stack trace ---

at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()

at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobType
type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)

at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)

at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage
message, Notification notification, SubscriptionData data)

at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)

at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)

ReportingServicesService!emailextension!c!11/16/2006-10:27:45:: Error
sending email.
Microsoft.ReportingServices.Diagnostics.Utilities.RSException: The
report server has encountered a configuration error. See the report
server log files for more information. --->
Microsoft.ReportingServices.Diagnostics.Utilities.ServerConfigurationErrorException:
The report server has encountered a configuration error. See the report
server log files for more information.

at Microsoft.ReportingServices.Authorization.Native.GetAuthzContextForUser(IntPtr userSid)

at Microsoft.ReportingServices.Authorization.Native.IsAdmin(String userName)

at Microsoft.ReportingServices.Authorization.WindowsAuthorization.IsAdmin(String userName, IntPtr userToken)

at
Microsoft.ReportingServices.Authorization.WindowsAuthorization.CheckAccess(String
userName, IntPtr userToken, Byte[] secDesc, ReportOperation
requiredOperation)

at
Microsoft.ReportingServices.Library.RSService.GetReportAndDataSourcesForExecution(ClientRequest
session, CatalogItemContext itemContext, String queryParametersXml,
Boolean& hasData, DataSourceInfoCollection& dataSources,
String& reportProperties, ReportSnapshot& intermediateSnapshot,
Guid& reportId, ReportSnapshot& snapshotData, String&
description, Boolean& cachingRequested, DateTime&
executionDateTime, DateTime& expirationDateTime, Int32&
execOptions, Boolean& foundInCache)

at
Microsoft.ReportingServices.Library.RSService.RenderAsLiveOrSnapshot(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters)

at
Microsoft.ReportingServices.Library.RSService.RenderFirst(CatalogItemContext
reportContext, ClientRequest session, Warning[]& warnings,
ParameterInfoCollection& effectiveParameters, String[]&
secondaryStreamNames)

at Microsoft.ReportingServices.Library.RenderFirstCancelableStep.Execute()

at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()

--- End of inner exception stack trace ---

at Microsoft.ReportingServices.Diagnostics.CancelablePhaseBase.ExecuteWrapper()

at
Microsoft.ReportingServices.Library.RenderFirstCancelableStep.RenderFirst(RSService
rs, CatalogItemContext reportContext, ClientRequest session, JobType
type, Warning[]& warnings, ParameterInfoCollection&
effectiveParameters, String[]& secondaryStreamNames)

at Microsoft.ReportingServices.Library.ReportImpl.Render(String renderFormat, String deviceInfo)

at
Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.ConstructMessageBody(IMessage
message, Notification notification, SubscriptionData data)

at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.CreateMessage(Notification notification)

at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification



Any ideas? Am running SQL Server 2005 with SP1.

View 5 Replies View Related

No Data-Driven Subscription Button

Jul 20, 2007

I have created a report and published it on the reporting server. My connection is working, and I have followed the steps in the BOL tutorial for "Defining a Data-Driven Subscription" i.e. Credentials stored securely on report server, using windows credentials when connecting to the data source. When I click on "Subscriptions", I only see "New Subscriptions". However, when I look at other reports (not even created by me), I see the option to create data driven subscriptions. Can somebody tell me what I need to do to see this?

View 1 Replies View Related

Reply-To On Data Driven Subscription

Jan 11, 2008

I have set up a data driven subscription for a report, and against "Reply-To" I have selected "Specify a Static Value".
I have then I have entered a valid email address.

When the report is received however, and the recipient clicks reply, the reply address is filled in as the Email Address from which the report was sent.

Is this a bug with the "Reply-To" functionality? Or do I have to do this a different way?

Thanks :-)
Kate

View 2 Replies View Related

No Data-driven Subscription Link In The RS

Nov 6, 2007



please help, when I access my report and click on the subscription tab, on the top page i only see the "new subscription" button. I also tried creating a data driven sub in sql manger but it was disbaled. please help how to correct this one.

thanks

View 3 Replies View Related

Data Driven Subscription - Open Connections

Jul 25, 2007

Hi,

I'm trying to run a data driven subscription on a report in the rs2005,

and using a data source to oracle,

I found out that when the subscription runs it opens a connection to oracle for each report and it means that i have more than fifteen open connections every time which causes a performance problems

did anyone encounter this kind of problem

pls help

View 3 Replies View Related

Data-Driven Subscription With Stored Procedure

Feb 12, 2008

I don't seem to be able to get a data-driven subscription to accept a stored procedure to return a list of reciepients and other subscription parameters. The message I get is:




The dataset cannot be generated. An error occurred while connecting to a data source, or the query is not valid for the data source. (rsCannotPrepareQuery) Get Online Help




For more information about this error navigate to the report server on the local server machine, or enable remote errors




A valid query must be specified.


Oddly, the stored procedure works fine in BIDS. The stored procedure is as follows:


ALTER PROCEDURE [etl].[spSubscription_OPs_Store_Scorecard]

AS

DECLARE @LfCr VARCHAR(5)

DECLARE @WeekNbr AS VARCHAR(4)

DECLARE @Comment AS VARCHAR(600)

/* Copyright © Leslies 2007, All Rights Reserved */

/* Created Feb/2007 by Jim Treadway, Leslies Poolmart, Inc. */

SET NOCOUNT ON

SET @LfCr = CHAR(10) + CHAR(13)

SET @WeekNbr = etl.GetFiscalWeek(GETDATE()-7)

SET @Comment =

'<html>

<body>

<br>Store Manager,' + @LfCr + @LfCr + '</br>

<br>Attached are your weekly scorecards and store reports. Please complete the following:' + @LfCr + '</br>

<br>1. Print the scorecards and reports' + @LfCr + '</br>

<br>2. Develop your action plans for meeting any goals where you have fallen short' + @LfCr + '</br>

<br>3. Review the results and action plans with your team, sharing both successes and opportunities' + @LfCr + '</br>

<br>4. Post the scorecards in the office' + @LfCr + '</br>

<br>5. File the reports in the Manager Productivity Book</br>

</body>

</html>'

SELECT To_email

,Reply_email

,Include_Report

,Format

,Priority

,Link

,'ScorecardsD' + RIGHT('0' + CAST([District_Code] AS varchar(2)),2) + '@lesl.com' AS CC_email

,'Store ' + [Parm1] + ' Weekly Scorecard ' + @WeekNbr AS Email_Subject

,@Comment AS Email_Comment

,Parm1 AS StoreNbr

FROM [DW_StagingDB].[Config].[ReportDistributionSettings] rept

INNER JOIN [DW_DatamartDB].[dbo].[DimLocation] loc

ON loc.Location_Code = rept.parm1

WHERE [ReportID] = 'OPs_Store_Scorecard'

AND District_Code < 50

AND ReportEnabled = 'True'


Finally, I have tried the following syntax options to execute the sp in the subscription setup:

"EXEC [etl].[spSubscription_OPs_Store_Scorecard]"
"SELECT [etl].[spSubscription_OPs_Store_Scorecard]"
"[etl].[spSubscription_OPs_Store_Scorecard]"

The data source is OK -- I use it for many reports. And, the SQL would seem to be OK. It I insert the SQL directly into the subscription, it works fine. This is my first attempt to use a sp as an data source in this context. What am I missing?

View 5 Replies View Related

Data-driven Subscription - Invalid Parameter?

Aug 23, 2007

Hi,

I've set up a data-driven subscription on a report but it's not sending and the error log says it's because:

Default value or value provided for the report parameter 'CSRFaxID' is not a valid value.

For the particular dataset record causing the error I'm passing the value Null to the report parameter. The report parameter CSRFaxID is set up with "Allow Null" and with a default value of Null. I can run the report in my browser with CSRFaxID set to null, but the scheduled subscription is not able to produce the report with the same parameter value. Does anyone have any idea what the problem is and how I can fix it?

Regards,

Greg McNamara

View 3 Replies View Related

How To Change Query In Data-driven Subscription

Nov 13, 2007



Hi All,

I have some problems when changing and running report subscription in the existing data-driven Email subscription using console app.

arg[0] is the ReportID
and arg[1] is the permissionkey

I passed these parameters in command_line when I call the executable. (e.g RunSubscription.exe 1 2)

After I run my application, the query changed successfully,
however, I didn't get the email from the subscription So I checked the subscription status,
Error: Cannot create a connection to data source ''.

If I remove the get & set subscription property codes, then application runs fine but then I can't change the query on the fly. Please help!!! I will be very appreciate if someone can give me some helps or suggestion.




static void Main(string[] args)

{

ReportService.ExtensionSettings ExtensionSetting;

ReportService.DataRetrievalPlan DataRetrievalPlan;

String Description;

ReportService.ActiveState ActiveState;

String Status;

String EventType;

String MatchData;

ReportService.ParameterValueOrFieldReference[] Parameter;



ReportService.ReportingService2005 MyRS = new ReportService.ReportingService2005();

MyRS.Credentials = System.Net.CredentialCache.DefaultCredentials;





ReportService.Subscription[] subs = MyRS.ListSubscriptions("/MyReports/" + args[0], null);



if (subs != null)

{

int i=0;

while (i < subs.Length)

{


// Get Subscription Property

MyRS.GetDataDrivenSubscriptionProperties(subs.SubscriptionID, out ExtensionSetting, out DataRetrievalPlan, out Description, out ActiveState, out Status, out EventType, out MatchData, out Parameter);

//set the query based on parameters

DataRetrievalPlan.DataSet.Query.CommandText = "exec dbo.GetReportPermission @ReportID=" + arg[0]+ ", @RegionPermission=" + arg[1];

MyRS.SetDataDrivenSubscriptionProperties(subs.SubscriptionID, ExtensionSetting, DataRetrievalPlan, Description, EventType, MatchData, Parameter);



MyRS.FireEvent(sets.EventType, subs.SubscriptionID);

Console.WriteLine("Event Fired: "+ subs.SubscriptionID);

i++;

}

}


} //main

View 6 Replies View Related

Failed Email Data Driven Subscription

Dec 26, 2007



I have several email data driven subscriptions setup. They have been working fine for the past couple of weeks. We have recently gone through some layoffs. Now the subscriptions are starting to fail.

I am assuming the smtp server is not allowing invalid email address to go. But if I have one invalid email address why would the entire job fail? How can SSRS see if each email address is valid at the time of processing?

Is there a setting to allow invalid email address to not fail the job? They only solution that I could think of is to create distribution email groups. This would be rendered on the smtp server and not in Reporting Service Service.

Does anyone have any good solutions they would like to share?


Log file:

at ReportingServicesCDOInterop.MessageClass.Send()
at Microsoft.ReportingServices.EmailDeliveryProvider.EmailProvider.Deliver(Notification notification)
ReportingServicesService!library!10!12/26/2007-10:43:15:: Data Driven Notification for activation id ac381bd5-7a04-4e8e-b5cf-510a3833c4f0 was saved.
ReportingServicesService!library!10!12/26/2007-10:43:15:: Status: The e-mail address of one or more recipients is not valid.
ReportingServicesService!notification!10!12/26/2007-10:43:15:: Notification 65af159a-b2c6-4667-905b-b2342898a28f completed. Success: False, Status: The e-mail address of one or more recipients is not valid., DeliveryExtension: Report Server Email, Report: Tech Lead Program - District Summary, Attempt 0
ReportingServicesService!dbpolling!10!12/26/2007-10:43:15:: i INFO: NotificationPolling finished processing item 65af159a-b2c6-4667-905b-b2342898a28f
ReportingServicesService!library!4!12/26/2007-10:43:16:: i INFO: Call to RenderFirst( '/Marketing/TTL/Tech Lead/Tech Lead Program - YTD Technician Summary' )
ReportingServicesService!dbpolling!c!12/26/2007-10:43:18:: i INFO: NotificationPolling processing 1 more items. 2 Total items in internal queue.
ReportingServicesService!dbpolling!11!12/26/2007-10:43:18:: i INFO: NotificationPolling processing item a6731001-092e-4a48-841e-bd22640956d7
ReportingServicesService!processing!12!12/26/2007-10:43:18:: e ERROR: Throwing Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 1., ;
Info: Microsoft.ReportingServices.ReportProcessing.ReportProcessingException: There is no data for the field at position 1.
ReportingServicesService!library!11!12/26/2007-10:43:19:: i INFO: Call to RenderFirst( '/Marketing/TTL/Tech Lead/Tech Lead Program - Customer Detail Report' )
ReportingServicesService!library!11!12/26/2007-10:43:22:: i INFO: Initializing EnableExecutionLogging to 'True' as specified in Server system properties.
ReportingServicesService!emailextension!11!12/26/2007-10:43:22:: Error sending mail, CDO error -2147220977, will not resend
ReportingServicesService!emailextension!11!12/26/2007-10:43:22:: Error sending email. System.Runtime.InteropServices.COMException (0x8004020F): The server rejected one or more recipient addresses. The server response was: 550 5.7.1 Unable to relay for xxxxx@searshc.com

View 4 Replies View Related

Data-Driven Subscription On A Report Containing Sub-Reports?

Mar 5, 2008



Hi,

Is it possible to have a data-driven subscription over a report containing sub-reports? I have been trying but without sucess...

I was unable to create the data-driven over the report... only over a linked report to that same report, but even that way when i execute the subscription i get error...

If i create a the data-driven subs to every single sub-report it works fine! So why can't i create the data-driven over the report with those sub-reports? What can be causing the problem?

Regards

View 1 Replies View Related

Problem Creating Data-Driven Subscription

Jul 2, 2007

Using SS2005, I am trying to create a Data-Driven Subscription to query off a value. I have the value in my stored procedure like:



Procedure dbo.sp_ReportMaster @myid int=null



When I run the report in the Reporting Services, it prompts for myid, which is fine. I then deployed the report to the Reports Manager and tried to create a Data-Driven Subscription. Where it says specify a command, I put in exec sp_ReportMaster. I put in the pertinent information, then it says, "The 'ReportMaster' has no parameters," even though I have the parameter in my stored procedure and I put myid as a parameter in my report in Reporting Services.



I don't know why it shows parameters in Reporting Services when I run the report from there, but when I try to create the subscription, it shows that I don't have any parameters.



Thanks, Iris

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved