J I am getting an error on creation:

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]))

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

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

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?