Data Driven Subscriptions Updated
-
Comments posted here are about the content posted at http://www.sqlservercentral.com/columnists/jselburg/2824.asp
______________________________________________________________________
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 -
J I am getting an error on creation:
Msg 102, Level 15, State 1, Procedure data_driven_subscription, Line 259
Incorrect syntax near ','.
Msg 102, Level 15, State 1, Procedure data_driven_subscription, Line 288
Incorrect syntax near ','.
I am off to a meeting so I don't have time to TS but will later this afternoon.
Thanks for the code though I am looking forward to testing it in on our dev server.
cjb-
-
The code in the article has been updated, sorry about that.
______________________________________________________________________
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 -
I'm getting an error trying to create the Subscription_History table:
Server: Msg 170, Level 15, State 1, Line 14
Line 14: Incorrect syntax near '('.
This is line 14 of the script as my Query Analyzer counts it:
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
I'm on SQL Server 2000 Standard Edition. Any thoughts on what's up here? Thanks.
John Casey
-
Just use this script and add the primary key through Enterprie manager...
CREATE TABLE [dbo].[Subscription_History](
[nDex] [int] IDENTITY(1,1) NOT NULL,
[SubscriptionID] [uniqueidentifier] NULL,
[ScheduleName] [nvarchar](260) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[parameterSettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[deliverySettings] [varchar](8000) COLLATE Latin1_General_CI_AS_KS_WS NULL,
[dateExecuted] [datetime] NULL,
[executeStatus] [nvarchar] (260) NULL,
[dateCompleted] [datetime] NULL,
[executionTime] AS (datediff(second,[datecompleted],[dateexecuted]))
______________________________________________________________________
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 -
Thats a great piece of code and I have managed to get it to work with a few of my data driven subscriptions. However, I have a data driven subscription in RS 2000 enterprise that uses the follwing parameter;
SELECT * FROM [Reports Ledger]
WHERE [Report ID] = 'AR002'
AND
DATEDIFF(n, CONVERT ( datetime, CAST(DATEPART(yyyy,[DATE POSTED]) AS varchar(4))+'-'+CAST(DATEPART(dd,[DATE POSTED]) AS varchar(2))+'-'+CAST(DATEPART(mm,[DATE POSTED]) AS varchar(2))+' '+
CAST(DATEPART(hh, [Time Posted] ) AS varchar(2)) +':'+ CAST(DATEPART(mi, [Time Posted] ) AS varchar(2))+':'+ CAST(DATEPART(s, [Time Posted] ) AS varchar(2)), 103),
GETDATE()) <= 3000
AND STATUS = 0
I have implemented an RS 2005 Standard environment which I am currently testing. So far I have been unable to pass this parameter into the data_driven_subscription stored proc. Firstly am I actually able to pass this kind of code using RS 2005 Standard and the modified data_driven_subscription stored proc? If yes then what am I doing wrong?
Thanks
-
David,
I am unclear on what you are asking. What columns are returned from [Reports Ledger]
And the procedure should work the same in 2005 as it does in 2000.
Jason
______________________________________________________________________
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 -
Hi Jason,
Basically I am trying to re-create a set of subscriptions I have set up in RS 2000 Enterprise in a RS 2005 Standard. In 2000 when creating a new data driven subscription I think it is step 3 you are able to type in code, I have the following code for a subscription;
SELECT * FROM [Reports Ledger]
WHERE [Report ID] = 'AR001'
AND
DATEDIFF(n, CONVERT ( datetime, CAST(DATEPART(yyyy,[DATE POSTED]) AS varchar(4))+'-'+CAST(DATEPART(dd,[DATE POSTED]) AS varchar(2))+'-'+CAST(DATEPART(mm,[DATE POSTED]) AS varchar(2))+' '+
CAST(DATEPART(hh, [Time Posted] ) AS varchar(2)) +':'+ CAST(DATEPART(mi, [Time Posted] ) AS varchar(2))+':'+ CAST(DATEPART(s, [Time Posted] ) AS varchar(2)), 103),
GETDATE()) <= 3000
AND STATUS = 0
Which returns my data and in step 4 I am able to specify multiple values I want to get from the database.
My question is how do I pass the above code and specify which values to get from the database using your new stored proc?
Thanks
-
David,
Are you saying that you have subscriptions set up in RS 2000 Enterprise using MS's interface, and you are attempting to re-create them in RS 2005 Standard without the interface (using my method)?
If so, then you have a bit more work than just copying the code. My process is less user-friendly when it comes to an interface. You'll need to code the parameters in a procedure and pass them into my proc. Simply using the "SELECT * ..." isn't going to work. I appologize for bweing vague, but what you're going to need to do is going to require a bit more TSQL programming.
Thanks,
Jason
______________________________________________________________________
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 -
Yes that is what I am attempting to do, I had a feeling it wouldn't be as simple as that.....
I have been looking at the table structures and stored procs between RS 2000 Enterprise/RS 2005 Standard. The subscription table structures are practically identical and the data_driven_subscription stored proc only calls this table.
I am thinking of creating the 'original' RS 2000 Enterprise stored proc in RS 2005 Standard and transferring the contents of each XML field that contains parameters (Parameters, ExtensionSettings and DataSettings) from the RS 2000 subscription table to the RS 2005 subscription table into the corresponding field.
Could you see this working?
-
Yes and NO! There are all sorts of unique identifiers in the RS database and I really don't think you can just copy them over.
______________________________________________________________________
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 -
I happened upon this article while attempting to create a method of creating data driven subscriptions in 2005 Standard. You can find the parameters list for your report in the dbo.Catalog table in the ReportServer database. The list is in XML format, so you'll have to do your own parsing. Also, I recommend using the stored procedure "dbo.GetReportParametersForExecution" as it will save you time recreating the wheel. The table dbo.Subscriptions stores the parameters in xml format and contains a name value pair that will be passed to the reporting engine.
-
Jason: Thanks for this very interesting article. I thought you might be interested in some improvements that I've made to this process. Using your SQL as inspiration, I've ran with this idea and created a fairly generic stored procedure that will run data driven subscriptions for delivery to file share as well as email. In addition, this code will handle a variable number of parameters to be customized.
I put my sql and a writeup in a blog post here:
http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html
feel free to let me know if you find this useful.
Regards,
M Spilich
-
Hi Jason,
If you want to send an email to multiple "TO" recipeints what is the separator I should be using?
Thanks,
Parag
-
I believe it is a semi colon ;
______________________________________________________________________
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
Viewing 15 posts - 1 through 15 (of 90 total)
You must be logged in to reply to this topic. Login to reply