Data Driven Subscriptions Updated

  • cgrammont (11/29/2011)


    @emailBODY = 'first

    <br /><br /><br />

    second

    <br /><br /><br />

    third'

    Just to be clear, there is no need for the actual line breaks. So this should work fine..

    @emailBODY = 'first<br /><br /><br />second<br /><br /><br />third'

    ______________________________________________________________________

    Personal Motto: Why push the envelope when you can just open it?

    If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.

    Jason L. Selburg
  • True. I was just adapting the solution to what he already posted.

  • Jason,

    Your second code block is exactly what I am trying. But, in the body of the email, it renders:

    FirstSecondThird

    We are using 2008 if that matters.

  • Try this:

    DECLARE @crlf char(2) = CHAR(13) + CHAR(10)

    SELECT @emailBODY = 'first' + @crlf + 'second' + @crlf + 'third'

    Note: for paragraphs, just double the @crlf, i.e.

    SELECT @emailBODY = 'first' + @crlf + @crlf + 'second' + @crlf + @crlf + 'third'

    [font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

  • Matthew,

    Thank you! This works well!

    And to everyone else, thanks for your responses also.

    John

  • I tried using the entity references instead of the "less than" and "greater than" characters that surround an HTML tag, and that also works! See my post above where I changed the the original message to show the entity references instead of the "<" and ">" characters.

    Does this mean maybe that our server uses a character setting that's different from others?

    If anyone knows, I would appreciate hearing back. Thanks.

  • This sounds like you are attempting to send HTML in a plain text email, hence why the discrepancies. What are you using to send the physical email once the report is generated?

    [font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

  • From within the SSRS Configuration Tool we specified an SMTP server and an email account to send the email.

    This probably is a plain text email; and we're trying to use html tags to create the paragraph spacing. So that may be the problem.

  • We just moved the entire report/email process from a development/test server to a production server.

    Now, neither the embedded html line break tag(s) nor the CHAR[13]+CHAR[10] method creates the desired crlf result.

    But, using the entity references does work, so the problem we were having may have something to do with the character set settings on the server itself.

  • Hi, I can't get to work any of the 3 versions of this procedure.

    With the first presented here (http://www.sqlservercentral.com/articles/Development/2824/) by Jason Selburg:

    Exec [data_driven_subscription] .........Returned the error:

    1 “The subscription contains parameter values that are not valid”

    The first version from Matt (http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html) and his second version on page 5 from this forum both return the same error:

    exec [Custom_data_driven_subscription] --(or exec [CustomDataDrivenSubscription] )

    @SubscriptionID = '83A4FDAB-A41B-440F-89DA-C4E9DFE6FEB6',

    @parameterNameLIST = 'STARTDATE|ENDDATE|PARAM_INT',

    @parameterValueLIST = '1/2/2012|2/2/2012|100',

    @ExtensionSettingNameLIST = 'TO|Subject',

    @ExtensionSettingValueLIST = 'blablabla@blablabla.com|@ReportName was executed at @ExecutionTime',

    @exitCode = @ResultID out,

    @exitMessage = @ResultMsg out Returned the error:

    -2 “The subscription does not exist.”

    I've tried everything, even new reports and new subscriptions but nothing works. Any help please?

  • Luis Salgado (2/3/2012)


    Returned the error:

    1 “The subscription contains parameter values that are not valid”

    Returned the error:

    -2 “The subscription does not exist.”

    I've tried everything, even new reports and new subscriptions but nothing works. Any help please?

    Usually when I had this problem, the solution was to create a new subscription from within SSRS, but you said you've already tried this (?).

    [font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

  • Yes, I did try that, but still the same result.

  • Did you change the GUID (Subscription ID) in the calling procedure when you recreated the SSRS subscription?

    [font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

  • 🙂 Yes, I've changed it every time. I'm pretty sure the GUID it's correct and that it exists, since I copy directly from the job name for the subscription.

  • Unfortunately, I cannot figure out what may be causing your issues. Are you getting the "subscription" not found error, or "invalid parameter values" error?

    I am currently not using DD subscriptions actively. In fact, I no longer had the SP, function, and table in the SQLDB. To try and track down the problem, I created the SP (from page 5 in this forum), including the fn_split UDF and CustomSubscriptionHistory table. I went to my development Report Server and then created a subscription. I then ran some sample code to change the subscription parameters and save the customized report. Everything worked flawlessly. I changed the parameters and ran it a second time. There were no errors.

    Keep in mind I ran this as a DBA (in SQL 2008 Standard Edition). It is possible you have a permissions issue in your DB, although I would expect a different error in that case. It is also possible the Subscription is being automatically deleted after it's run. When you create the new subscription, schedule it for the past and to not run automatically in the future. Hope this helps!

    [font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

Viewing 15 posts - 76 through 90 (of 90 total)

You must be logged in to reply to this topic. Login to reply