SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Data Driven Subscriptions Updated


Data Driven Subscriptions Updated

Author
Message
Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6874 Visits: 4115
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
Christopher Bellizzi
Christopher Bellizzi
Old Hand
Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)Old Hand (325 reputation)

Group: General Forum Members
Points: 325 Visits: 86

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-


Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6874 Visits: 4115
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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
John Casey
John Casey
Forum Newbie
Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)Forum Newbie (7 reputation)

Group: General Forum Members
Points: 7 Visits: 3

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


Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6874 Visits: 4115

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
dt293
dt293
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 698

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


Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6874 Visits: 4115

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
dt293
dt293
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 698

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


Jason Selburg
Jason Selburg
SSCertifiable
SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)SSCertifiable (6.9K reputation)

Group: General Forum Members
Points: 6874 Visits: 4115

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 you'll likely increase the number and quality of responses you get to your question.

Jason L. Selburg
dt293
dt293
Old Hand
Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)Old Hand (348 reputation)

Group: General Forum Members
Points: 348 Visits: 698

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?


Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search