locked
Cannot create data-driven subscription RRS feed

  • Question

  • 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

     

    Friday, November 17, 2006 1:09 AM

Answers

All replies

  • Make sure your report does not contain expressions depending on user like User!UserID.

    See http://msdn2.microsoft.com/en-us/library/ms156012.aspx.

    Friday, November 17, 2006 6:36 AM
    Answerer
  • thanks! that resolved the problem.

     

     

    Sunday, November 19, 2006 11:11 PM
  • Hi,

     

    I'm having the same problem. I'm using the browser interface for SSRS and when I click on a report and go to Subscriptions, the buttons for New Subscription and Data-driven Subscription have warning icons, and when I click on either I get the same message as above.

     

    The report already has a data-driven subscription which was working fine. I haven't made any changes to the report. This problem is affecting all reports. What has changed is the data source (now pointing to a different database), which is using "Credentials stored securely on the server" and "Use as Windows credentials..." and "Impersonate the authenticated user..." both checked.

     

    Is there some security setting being cached in the reports? Do I need to update the reports in some way to make them work with the updated data source?

     

    Regards,

     

    Greg

    Wednesday, September 5, 2007 10:32 PM
  • Answering my own question, the problem was the "Impersonate the authenticated user..." option. When I unchecked this for the data source I was able to create subscriptions.

     

    Regards,

     

    Greg

     

    Wednesday, September 5, 2007 11:04 PM
  • Hi All,

    I am new to this technology, i'm still facing the error. can anyone advice. Still the same error,

    IN my reports

    properties - > it is a shared data source.
    Monday, October 26, 2009 8:43 AM
  • Lawand,
    If nothing of the above scenarios gave hand, try creating a user with high privillege as like an admin and try running/creating  the subscriptions with this new user. lets see whether it runs and then we will go for next steps of action.
    Regards, KarthikShanth.
    Monday, October 26, 2009 11:47 AM
  • I have a SSRS report which displays daily status report for the team members. The dataset of the report depends on User!UserID as a parameter which is passed to the SQL Query to fetch the data. I want to create a data driven subscription on this report so that every user gets his/her daily status report each morning. But when I click on the "New Data-Driven Subscription" I get a 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."

    If I remove User!UserID from the expression how can I fetch the data on individual user basis. Please suggest.

    Tuesday, February 16, 2010 6:21 PM
  • User!UserID presents an interactive user and makes most sense for the interactive report execution. For the background execution (subscriptions) there is no interactive user. If you want your subscription to execute the report with different ‘User’ parameter, populate it with the data-driven subscription query.

     

    Igor


    This posting is provided "AS IS" with no warranties, and confers no rights.
    Wednesday, February 17, 2010 4:17 AM
    Answerer
  • So is there a way to even create the data-driven subscription populating the parameter with the users that you want, but still keeping the User!UserID as the default for the interactive report so that I do not have to create a seperate report for subscriptions?  

    Im using an IIF statement: 

    =IIF(INSTR(User!UserID,

    "\") > 0, RIGHT(User!UserID, LEN(User!UserID) - INSTR(User!UserID, "\")) , User!UserID)

     to check and strip the domain name first to match the database columns, but just having this function as the default for my UserName parameter does not allow me to even create the data-driven subscription.  My query was going to pass in the usernames that I wanted to use for each report, but I can't even get that far.

    Randy
    Wednesday, March 3, 2010 10:21 PM
  • What doesnt make since is that SSRS lets you create  normal subscriptions when using User!UserID.  The report will be delivered as if the person who scheduled it ran it.....  so why doesnt data-driven subscription work the same way???  I just tested this in SSRS 2008 R2 .. .and still has the same issue.

    Seems like this is something that can be fixed.  (Patch.. Hotfix...?? )

    Bill

     

    Thursday, July 29, 2010 2:34 AM
  • Hi,

    We resolved placing the UserID in the table we query for the data driven report.

    In particular in our case we have in our warehouse a table named W_DATA_DRIVEN_SCHEDULES_G that includes all columns needed by the schedule (TO, CC, BCC, INCLUDE REPORT, RENDERING TYPE...) plus 10 columns named PARAM1...PARAM10 where we include the values of up to 10 reports parameters. We added the UserID of the user that needs to receive the schedule with his filter in column PARAM1 and it works fine.

    Tommy

    Friday, August 20, 2010 11:46 AM
  • Take a look at your data source.  When I changed

    From:

    (radio button) Integrated security

    To:

    (radio button) Credentials are not required

    then it started to work for me.

    Thursday, September 27, 2012 3:43 PM
  • this worked for me...sql server 2014
    Wednesday, July 22, 2015 8:07 PM
  • Make sure your report does not contain expressions depending on user like User!UserID.

    See http://msdn2.microsoft.com/en-us/library/ms156012.aspx.

    Bingo!
    Thursday, November 12, 2015 1:34 AM
  • What doesnt make since is that SSRS lets you create  normal subscriptions when using User!UserID.  The report will be delivered as if the person who scheduled it ran it.....  so why doesnt data-driven subscription work the same way???  I just tested this in SSRS 2008 R2 .. .and still has the same issue.

    Seems like this is something that can be fixed.  (Patch.. Hotfix...?? )

    Bill

     

    Do you if there was any patch or hotfix provided for this...? For SQL2012.

    Thanks


    Mahesh


    Thursday, June 30, 2016 7:47 PM
  • Make sure your report does not contain expressions depending on user like User!UserID.

    See http://msdn2.microsoft.com/en-us/library/ms156012.aspx.

    Bingo!
    Is there a workaround solutions for this..?

    Mahesh

    Thursday, June 30, 2016 7:51 PM
  • There is no work around for this situation we need to write a custom code in .net

    I used this code:

    Public Function GetUserName() As String
            Return System.Threading.Thread.CurrentPrincipal.Identity.Name.Replace("(Domainname)\", "").ToString()
        End Function

    you need to pass code.GetUserName() in report param where you use this user!userid

    Tuesday, December 20, 2016 5:37 PM