InstantForum.NET v2.9.0SQLServerCentralhttp://www.sqlservercentral.com/Forums/notifications@sqlservercentral.comTue, 21 May 2013 09:40:57 GMT20http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxUnfortunately, 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!Tue, 07 Feb 2012 13:23:04 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx:-) 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.Sat, 04 Feb 2012 02:18:33 GMTLuis Salgadohttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxDid you change the GUID (Subscription ID) in the calling procedure when you recreated the SSRS subscription?Fri, 03 Feb 2012 11:28:22 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxYes, I did try that, but still the same result.Fri, 03 Feb 2012 11:19:59 GMTLuis Salgadohttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[quote][b]Luis Salgado (2/3/2012)[/b][hr]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?[/quote]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 (?).Fri, 03 Feb 2012 11:15:59 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxHi, 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:[code]Exec [data_driven_subscription] .........[/code][b]Returned the error:1 “The subscription contains parameter values that are not valid”[/b]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:[code]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 [/code][b]Returned the error:-2 “The subscription does not exist.”[/b]I've tried everything, even new reports and new subscriptions but nothing works. Any help please?Fri, 03 Feb 2012 11:08:14 GMTLuis Salgadohttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxWe 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.Wed, 30 Nov 2011 12:26:01 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxFrom 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.Tue, 29 Nov 2011 16:41:42 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxThis 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?Tue, 29 Nov 2011 14:55:13 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxI tried using the entity references instead of the "less than" and "greater than" characters that surround an HTML tag, and [b]that also works![/b] 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.Tue, 29 Nov 2011 13:46:34 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxMatthew,Thank you! This works well!And to everyone else, thanks for your responses also.JohnTue, 29 Nov 2011 11:56:29 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxTry this:[code]DECLARE @crlf char(2) = CHAR(13) + CHAR(10)SELECT @emailBODY = 'first' + @crlf + 'second' + @crlf + 'third'[/code]Note: for paragraphs, just double the @crlf, i.e.[code]SELECT @emailBODY = 'first' + @crlf + @crlf + 'second' + @crlf + @crlf + 'third'[/code]Tue, 29 Nov 2011 11:18:01 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxJason,Your second code block is exactly what I am trying. But, in the body of the email, it renders:FirstSecondThirdWe are using 2008 if that matters.Tue, 29 Nov 2011 11:07:26 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxTrue. I was just adapting the solution to what he already posted.Tue, 29 Nov 2011 10:29:20 GMTcgrammonthttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[quote][b]cgrammont (11/29/2011)[/b][hr][code="other"]@emailBODY = 'first<br /><br /><br />second<br /><br /><br />third'[/code][/quote]Just to be clear, there is no need for the actual line breaks. So this should work fine.. [code]@emailBODY = 'first<br /><br /><br />second<br /><br /><br />third'[/code]Tue, 29 Nov 2011 10:27:10 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[code="other"]@emailBODY = 'first<br /><br /><br />second<br /><br /><br />third'[/code]Tue, 29 Nov 2011 10:18:42 GMTcgrammonthttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxThis is what we are testing:[code]@emailBODY = 'first<br /><br /><br />second<br /><br /><br />third'[/code]Tue, 29 Nov 2011 10:13:39 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[quote][b]dguthrie_2010 (11/29/2011)[/b][hr]Hi All,Thank you Jason for this article, it has been very helpful.We are having a similar problem to the one JayK described on 8/16/2011 regarding formatting the body of the email. Everything else works fine.Similar to JayK, we are passing a string to @emailBODY with HTML "line break" tags to separate paragraphs but no matter what we do, the text in the body of the email displays as one long string.Apparently JayK was able to get this to work. Can JayK or anyone else shed any light on this?Thank you.John[/quote]Paste the @emailBody value here within code .. /code tags.Tue, 29 Nov 2011 09:45:57 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx<br /><br />Was JayK's solution. Did it not work for you?Tue, 29 Nov 2011 09:41:36 GMTcgrammonthttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxHi All,Thank you Jason for this article, it has been very helpful.We are having a similar problem to the one JayK described on 8/16/2011 regarding formatting the body of the email. Everything else works fine.Similar to JayK, we are passing a string to @emailBODY with HTML "line break" tags to separate paragraphs but no matter what we do, the text in the body of the email displays as one long string.Apparently JayK was able to get this to work. Can JayK or anyone else shed any light on this?Thank you.JohnTue, 29 Nov 2011 09:38:20 GMTdguthrie_2010http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxAll Ok - used <br/><br/> and worked OK :-)Tue, 16 Aug 2011 14:00:30 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxHi,I have implemented this solution and it works great sending emils with PDF attachment but I can't seem to get the email body to include the new line character.Here is how I am calliing the sp:DECLARE @return_value int, @exitCode int, @exitMessage nvarchar(255)EXEC @return_value = [dbo].[ExecuteSubscribedReport] @scheduleName = N'As Run Errors', @emailTO = xxxxxx, @emailCC = xxxxxx, @emailReplyTO = xxxxxx, @emailBODY = ?, @parameterName = N'Channel', @parameterValue = ?, @sub = ?, @renderFormat = N'PDF', @exitCode = @exitCode OUTPUT, @exitMessage = @exitMessage OUTPUTI am passing in a string to the emailBODY parameter with <br/> tags but all my text comes out on one line in the email body - can't seem to get this to wotk :doze:Tue, 16 Aug 2011 13:26:35 GMTJayKhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxThe UID needs to be hexadecimal. As such, there can't be a "Y" in there, hence the reason for the error. Not sure how the "Y" got into the sample provided above, lol. Regardless, the SubscriptionID pulled from the SQL DB will have the correct value.Thu, 09 Dec 2010 10:10:41 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxI'm not sure why but SQL Server doesn't think that's a legitmate GUID.This works:[code="sql"]declare @aTest uniqueidentifierset @aTest = 'B85E62C3-DC56-40C0-852A-49F759AC68FB'select @aTest[/code]But this doesn't:[code="sql"]declare @aTest uniqueidentifierset @aTest = '56YB32BA-BEB2-4066-B0FC-BB7691E6ED38'select @aTest[/code]Thu, 09 Dec 2010 09:54:56 GMTcgrammonthttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[quote][b]Mark Gamble (6/3/2010)[/b][hr]Thaks Matt for the info.... I have it all setup with the subscription...the function, the proc and the new table on the report server database of the hosting website....When I go to run the sample codeI get this error messageMsg 8114, Level 16, State 5, Procedure Custom_data_driven_subscription, Line 0Error converting data type varchar to uniqueidentifier.(1 row(s) affected)Any ideas????[/quote]If you can help please let me know.. Sorry for the nasty way I presented my post prior to this one...Thx MarkThu, 03 Jun 2010 15:23:44 GMTMark Gamblehttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxThaks Matt for the info.... I have it all setup with the subscription...the function, the proc and the new table on the report server database of the hosting website....When I go to run the sample codeDECLARE @SubscriptionID uniqueidentifier, @parameterNameLIST nvarchar(4000), -- pipe delimeted @parameterValueLIST nvarchar(4000), -- pipe delimeted @ExtensionSettingNameLIST nvarchar(4000), @ExtensionSettingValueLIST nvarchar(4000), @exitCd int , @exitMsg nvarchar(255), @exitCode int , @exitMessage nvarchar(255) exec [Custom_data_driven_subscription] @SubscriptionID = '56YB32BA-BEB2-4066-B0FC-BB7691E6ED38',@parameterNameLIST = 'REQUESTID',@parameterValueLIST = '5715', -- POPULATE THIS FIELD@ExtensionSettingNameLIST = 'TO|Subject',@ExtensionSettingValueLIST = 'A016668|@ReportName was executed at @ExecutionTime', -- POPULATE THIS FIELD@exitCode = @exitCD out,@exitMessage = @exitMSG out select @exitCD, @exitMSGI get this error messageMsg 8114, Level 16, State 5, Procedure Custom_data_driven_subscription, Line 0Error converting data type varchar to uniqueidentifier.(1 row(s) affected)Any ideas????Thu, 03 Jun 2010 15:08:53 GMTMark Gamblehttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[quote][b]Mark Gamble (6/3/2010)[/b][hr]Jason, Any word as yet on if this is supported in SSRS 2008.... I like it... but having issues getting it to work. Any assistance you could render would be much appreciated.[/quote]This does work in SSRS 2008 with a few changes from the original, as discussed in prior messages in this forum. The key is to use the XML format and avoid the UPDATETEXT DDL command.Thu, 03 Jun 2010 13:30:35 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxJason, Any word as yet on if this is supported in SSRS 2008.... I like it... but having issues getting it to work. Any assistance you could render would be much appreciated.Thu, 03 Jun 2010 13:14:39 GMTMark Gamblehttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxThanks Mathew this worked for me in SQL 2008. I really appreciate you guys providing this information was a great help. Took a little time to wrap my head around it but it works.Thanks,Thu, 25 Mar 2010 14:46:40 GMTMark F-428640http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspx[quote][b]Matthew Lehn (3/25/2010)[/b][hr]Don't use the UPDATETEXT method. I recall people saying they were having trouble with that method. Refer to sample SQL given on page 5 and at [url=http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html]http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html[/url] for an alternate using the XML data type. If you still have issues, let me know and I'll see what I can do as time permits.[/quote]Thank Matthew for keeping up on this. I'm so busy spinning plates these days that I can't get back to this thread as often as I'd like. I'm alao not involved with RS that much any more so it's a little rusty in my head.....So thanks a ton for picking up my slack....:-DThu, 25 Mar 2010 11:26:43 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxDon't use the UPDATETEXT method. I recall people saying they were having trouble with that method. Refer to sample SQL given on page 5 and at [url=http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html]http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html[/url] for an alternate using the XML data type. If you still have issues, let me know and I'll see what I can do as time permits.Thu, 25 Mar 2010 11:17:57 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxSo I tried your suggestion of deleting and recreating the subscription and the first time I executed the SP it worked perfectly. The second time I executed it I get the same status of [i]root element missing[/i]. As per your request here is the code (sorry I don't know how to put the code in that neat little box I have seen others do):Create procedure [dbo].[data_driven_subscription] ( @scheduleName nvarchar(255), @emailTO nvarchar (2000) = NULL, @emailCC nvarchar (2000) = NULL, @emailBCC nvarchar (2000) = NULL, @emailReplyTO nvarchar (2000) = NULL, @emailBODY nvarchar (4000) = NULL, @parameterName nvarchar(4000) = NULL, @parameterValue nvarchar (256) = NULL, @sub nvarchar(1000) = NULL, @renderFormat nvarchar(50) = 'PDF', @exitCode int output, @exitMessage nvarchar(255) output )ASDECLARE @ptrval binary(16), @PARAMptrval binary(16), @subscriptionID uniqueidentifier, @scheduleID uniqueidentifier, @starttime datetime, @lastruntime datetime, @execTime datetime, @dVALUES nvarchar (4000), @pVALUES nvarchar (4000), @previousDVALUES nvarchar (4000), @previousPVALUES nvarchar (4000), @lerror int, @insertID int, @lretval int, @rowcount intSET @starttime = DATEADD(second, -2, getdate())SET @emailTO = rtrim(IsNull(@emailTO, ''))SET @emailCC = rtrim(IsNull(@emailCC, ''))SET @emailBCC = rtrim(IsNull(@emailBCC, ''))SET @emailReplyTO = rtrim(IsNull(@emailReplyTO, ''))SET @emailBODY = rtrim(IsNull(@emailBODY, ''))SET @parameterValue = rtrim(IsNull(@parameterValue, ''))SET @lerror = 0SET @rowcount = 0IF @emailTO = '' AND @emailCC = '' AND @emailBCC = '' BEGIN SET @exitCode = -1 SET @exitMessage = 'A recipient is required.' RETURN 0 END-- get the subscription IDSELECT @subscriptionID = rs.subscriptionID, @scheduleID = rs.ScheduleID FROM --ReportSchedule rs --Original Code Sib_ReportServer..ReportSchedule rs --Updated from original code by Mark Fyffe --INNER JOIN subscriptions s --Original Code INNER JOIN Sib_ReportServer..subscriptions s --Updated from original code by Mark Fyffe ON rs.subscriptionID = s.subscriptionID WHERE extensionSettings like '%' + @scheduleName + '%'IF @subscriptionID Is Null BEGIN SET @exitCode = -2 SET @exitMessage = 'The subscription does not exist.' RETURN 0 END /* just to be safe */SET @dVALUES = ''SET @pVALUES = ''SET @previousDVALUES = ''SET @previousPVALUES = ''/* apply the settings that are defined */IF IsNull(@emailTO, '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>TO</Name><Value>' + @emailTO + '</Value></ParameterValue>' IF IsNull(@emailCC, '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>CC</Name><Value>' + @emailCC + '</Value></ParameterValue>' IF IsNull(@emailBCC, '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>BCC</Name><Value>' + @emailBCC + '</Value></ParameterValue>' IF IsNull(@emailReplyTO, '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>ReplyTo</Name><Value>' + @emailReplyTO + '</Value></ParameterValue>' IF IsNull(@emailBODY, '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>Comment</Name><Value>' + @emailBODY + '</Value></ParameterValue>'IF IsNull(@sub, '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>Subject</Name><Value>' + @sub + '</Value></ParameterValue>' IF IsNull(@dVALUES , '') <> '' SET @dVALUES = '<ParameterValues>' + @dVALUES + '<ParameterValue><Name>IncludeReport</Name><Value>True</Value></ParameterValue>'IF IsNull(@dVALUES , '') <> '' SET @dVALUES = @dVALUES + '<ParameterValue><Name>RenderFormat</Name><Value>' + @renderFormat + '</Value></ParameterValue>' + '<ParameterValue><Name>IncludeLink</Name><Value>False</Value></ParameterValue></ParameterValues>'IF IsNull(@parameterName, '') <> '' and IsNull(@parameterValue, '') <> '' SET @pVALUES = '<ParameterValues><ParameterValue><Name>' + @parameterName + '</Name><Value>' + @parameterValue + '</Value></ParameterValue></ParameterValues>'/* verify that some delivery settings where passed in */-- @pVALUES are not checked as they may all be defaultsIF IsNull(@dVALUES , '') = '' BEGIN SET @exitCode = -3 SET @exitMessage = 'No delivery settings were supplied.' RETURN 0 END /* get the current parameter values and delivery settings */SELECT @previousDVALUES = extensionSettings --FROM Subscriptions --Original Code FROM Sib_ReportServer..Subscriptions --Updated from original code by Mark Fyffe WHERE SubscriptionID = @SubscriptionIDSELECT @previousPVALUES = parameters --FROM Subscriptions --Original Code FROM Sib_ReportServer..Subscriptions --Updated from original code by Mark Fyffe WHERE SubscriptionID = @SubscriptionID--UPDATE Subscriptions --Original CodeUPDATE Sib_ReportServer..Subscriptions --Updated from original code by Mark Fyffe SET extensionSettings = '', parameters = '' WHERE SubscriptionID = @SubscriptionID SELECT @lerror=@@error, @rowCount=@@rowCount IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0 BEGIN SET @exitcode = -5 SET @exitMessage = 'A data base error occurred clearing the previous subscription settings.' RETURN IsNull(@lerror, 0) END-- set the text point for this recordSELECT @ptrval = TEXTPTR(ExtensionSettings) --FROM Subscriptions --Original Code FROM Sib_ReportServer..Subscriptions --Updated from original code by Mark Fyffe WHERE SubscriptionID = @SubscriptionID SELECT @lerror=@@error IF @lerror <> 0 OR @ptrval Is NULL BEGIN SET @exitcode = -6 SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Delivery Values.' RETURN IsNull(@lerror, 0) ENDUPDATETEXT Subscriptions.ExtensionSettings @ptrval null null @dVALUES SELECT @lerror=@@error IF @lerror <> 0 BEGIN SET @exitcode = -7 SET @exitMessage = 'A data base error occurred updating the Delivery settings.' RETURN IsNull(@lerror, 0) END -- set the text point for this recordSELECT @PARAMptrval = TEXTPTR(Parameters) --FROM Subscriptions --Original Code FROM Sib_ReportServer..Subscriptions --Updated from original code by Mark Fyffe WHERE SubscriptionID = @SubscriptionID SELECT @lerror=@@error IF @lerror <> 0 OR @ptrval Is NULL BEGIN SET @exitcode = -8 SET @exitMessage = 'A data base error occurred retrieving the TEXT Pointer of the Parameter Values.' RETURN IsNull(@lerror, 0) ENDUPDATETEXT Subscriptions.Parameters @PARAMptrval null null @pVALUES SELECT @lerror=@@error IF @lerror <> 0 BEGIN SET @exitcode = -9 SET @exitMessage = 'A data base error occurred updating the Parameter settings.' RETURN IsNull(@lerror, 0) END /* insert a record into the history table */SET @execTime = getdate()--INSERT Subscription_History --Original CodeINSERT Sib_ReportServer..Subscription_History --Updated from original code by Mark Fyffe (subscriptionID, scheduleName, ParameterSettings, DeliverySettings, dateExecuted, executeStatus) VALUES (@subscriptionID, @scheduleName, @parameterValue, @dVALUES , @execTime, 'incomplete' ) SELECT @lerror=@@error, @insertID=@@identity IF @lerror <> 0 OR IsNull(@insertID, 0) = 0 BEGIN SET @exitcode = -4 SET @exitMessage = 'A data base error occurred inserting the subscription history record.' RETURN IsNull(@lerror, 0) END-- run the jobEXEC msdb..sp_start_job @job_name = @scheduleID-- this gives the report server time to execute the job--SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID --Original CodeSELECT @lastruntime = LastRunTime FROM Sib_ReportServer..Schedule WHERE ScheduleID = @scheduleID --Updated from original code by Mark FyffeWHILE (@starttime > @lastruntime) BEGIN WAITFOR DELAY '00:00:01' --SELECT @lastruntime = LastRunTime FROM ReportServer..Schedule WHERE ScheduleID = @scheduleID --Original Code SELECT @lastruntime = LastRunTime FROM Sib_ReportServer..Schedule WHERE ScheduleID = @scheduleID --Original Code END/* update the history table with the completion time */--UPDATE Subscription_History --Original CodeUPDATE Sib_ReportServer..Subscription_History --Updated from original code by Mark Fyffe SET dateCompleted = getdate() WHERE subscriptionID = @subscriptionID and scheduleName = @scheduleName and ParameterSettings = @parameterValue and dateExecuted = @execTime SELECT @lerror=@@error, @rowCount=@@rowCount IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0 BEGIN SET @exitcode = -10 SET @exitMessage = 'A data base error occurred updating the subscription history record.' RETURN IsNull(@lerror, 0) END/* reset the previous delivery and parameter values */ --UPDATE Subscriptions --Original codeUPDATE Sib_ReportServer..Subscriptions --Updated from original code by Mark Fyffe SET extensionSettings = @previousDVALUES , parameters = @previousPVALUES WHERE SubscriptionID = @SubscriptionID SELECT @lerror=@@error, @rowCount=@@rowCount IF @lerror <> 0 OR IsNull(@rowCount, 0) = 0 BEGIN SET @exitcode = -11 SET @exitMessage = 'A data base error occurred resetting the previous subscription settings.' RETURN IsNull(@lerror, 0) END/* return the result of the subscription */SELECT @exitMessage = LastStatus --FROM subscriptions --Original code FROM Sib_ReportServer..subscriptions --Updated from original code by Mark Fyffe WHERE subscriptionID = @subscriptionIDSET @exitCode = 1RETURN 0GOSET ANSI_NULLS OFFGOSET QUOTED_IDENTIFIER OFFGOAppreciate the help thanks,Thu, 25 Mar 2010 10:36:20 GMTMark F-428640http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxThe code I used and rewrote some posts back worked in SQL2008 when I tested it at the time. Unfortunately I am not now actively using data-driven subscriptions. Perhaps try to delete the preliminary subscription and re-create it (thus recreating the XML) and run the query again. If after running the query causes the corruption again, post your code here so I and others can take a look.Thu, 25 Mar 2010 10:14:30 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxGreat article do you have an update for 2008? I have tried to implement this with SSRS 2008 -- I tested in SQL Managemetn studio executing the SP data_driven_subscription and it says the job started successfully but when I look in the Report Manager the Status column states [i]Root element is missing[/i].Any suggestions to fix this?Thanks,Thu, 25 Mar 2010 10:01:30 GMTMark F-428640http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxDid you look at my delay mechanism for file-delivery subscriptions in this same forum?Fri, 05 Mar 2010 08:29:25 GMTMatthew Lehnhttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxHi,Im trying to automate the subscription to store the report file as PDF to a file share. My procedure works well with output to email and the PDF-File attached. Then i tried this: http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html .But it doesnt work for me. I think the problem could be timecritical because of the timeintervall the report is generated. Has anybody any hints? Do you know any other procedures to automate a data driven subscription to save the report to a file share within the standard edition. My demand is to generate multiple reports with one parameter and a file share which is different for each report. For each report i want to generate i have one line in my subscription table with the value for the parameter and the path to the file share, etc.Fri, 05 Mar 2010 06:31:33 GMTgmuhamethttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxYou should find out why the subscription is getting messed up instead of working around the problem. :-)Mon, 22 Feb 2010 06:36:23 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxI tried to create a new subscription. And indeed, it works.But in my case, I would like to generate a report several times without creating and deleting the subscription manually. Here is my project:- Users choose a CSV file on their computer- This CSV file is converted to XML and introduced in a a SQL Table with an uniqueidentifier ID- Then, I extract the XML in a table and join with an other table to get my final table on which a report is based.- This report is generated and an email is send to the user (thanks to you !)So ,your script should be run several times for several users.I need to create a subsciption one time only manually.Maybe it's possible to create and delete a subscription with a script or a stored procedure ?ThxKevinMon, 22 Feb 2010 01:26:11 GMTkesiea17http://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxKevin,When that situation occurs it means that something went wrong during the execution. You will need to delete that subscription and create a new one.Fri, 19 Feb 2010 09:03:22 GMTJason Selburghttp://www.sqlservercentral.com/Forums/Topic338297-304-1.aspxHello,Thanks for your script, it works very well.Unfortunately, I can’t execute the same ScheduleName twice.The subscription indicates “Root element is missing” and I have the error -7,-2 or -10 when I try a second time.I don't understand how to fix itKevin ALBURQUERQUEBusiness Intelligence ConsultantFri, 19 Feb 2010 09:00:33 GMTkesiea17