Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12345»»»

Data Driven Subscriptions Updated Expand / Collapse
Author
Message
Posted Friday, January 19, 2007 10:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 5, 2014 10:21 AM
Points: 2,717, Visits: 3,855
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
Post #338297
Posted Wednesday, February 14, 2007 7:09 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Wednesday, June 25, 2014 12:34 PM
Points: 295, Visits: 72

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-

Post #344813
Posted Wednesday, February 14, 2007 11:36 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 5, 2014 10:21 AM
Points: 2,717, Visits: 3,855
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
Post #344925
Posted Friday, March 23, 2007 10:20 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Friday, August 29, 2008 3:48 PM
Points: 1, 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

Post #353576
Posted Friday, March 23, 2007 11:04 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 5, 2014 10:21 AM
Points: 2,717, Visits: 3,855

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
Post #353587
Posted Thursday, June 28, 2007 4:36 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:50 AM
Points: 68, Visits: 627

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

Post #377332
Posted Thursday, June 28, 2007 7:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 5, 2014 10:21 AM
Points: 2,717, Visits: 3,855

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
Post #377400
Posted Thursday, June 28, 2007 8:30 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:50 AM
Points: 68, Visits: 627

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

Post #377424
Posted Thursday, June 28, 2007 8:54 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Sunday, October 5, 2014 10:21 AM
Points: 2,717, Visits: 3,855

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
Post #377437
Posted Thursday, June 28, 2007 9:02 AM
Valued Member

Valued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued MemberValued Member

Group: General Forum Members
Last Login: Tuesday, October 21, 2014 8:50 AM
Points: 68, Visits: 627

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?

Post #377442
« Prev Topic | Next Topic »

Add to briefcase 12345»»»

Permissions Expand / Collapse