Data Driven Subscriptions Updated

Question

Post reply

Data Driven Subscriptions Updated

Viewing 15 posts - 31 through 45 (of 90 total)

You must be logged in to reply to this topic. Login to reply

melancholy.katowice SSC Rookie Points: 31 More actions · Answer 1

Hi there,

I'm used your solusion and it's worked great. But now I have error message:

Procedure sp_verify_job_identifiers

The specified @job_name ('B0B26990-953D-362802DC6150') does not exist

I have data in table dbo.Schedule (with scheduleId 'B0B26990-953D-362802DC6150') and dbo.Subscriptions.

Please help me if you can.

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 2

I could only suggest that you re-create the initial subscription by hand and schedule it for the past. Then just update the query to use the newly created Subscription and ScheduleID.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

melancholy.katowice SSC Rookie Points: 31 More actions · Answer 3

I alredy know what was a problem. I didnt have enough priviliges. Thank you for answer.

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 4

Yep, privileges would definitely cause a problem too, lol. Glad you were able to fix the problem!

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

melancholy.katowice SSC Rookie Points: 31 More actions · Answer 5

I have new problem. I execute my reports with parameters (StartTime, EndTime(DateTime)). I Specify the EndTime parameter value to 2009-05-21 23:59:59.997 but in subscription i have 2009-05-21 23:59:00. It's problem for me becouse i have some data with EndTime 23:59:57. What should i do ? I take my dates from function.

CREATE FUNCTION [dbo].[get_yesterday_end] (@today DATETIME) RETURNS DATETIME

AS

BEGIN

RETURN DATEADD(ms, -3, DATEDIFF(d,0,@today))

END

DECLARE @getdate-2 datetime

SET @getdate-2 = Getdate()

SELECT dbo.get_yesterday_end(@getDate)

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 6

You could change the query to say something along the lines of:

EndDate < DATEADD(d,1,@enddate).

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 7

Actually, nevermind that last post. Assuming you want the date range to be from one day to the next (without regards to any time constraints), you may be better off trying something like:

enddate < DATEADD(d, 1, CAST(CONVERT(varchar(10), @endDate, 101) AS datetime))

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

robibobi Grasshopper Points: 20 More actions · Answer 8

Basically it works quite good .... with one paramter.

Now I'd like to schedule a report with 4 parameters. 3 of them have a standard value, the forth one has to be set manually.

Does anyone know what I can do ?

Thanks in advance and best regards

Gawan

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 9

Refer to the article at http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html to use more than one parameter.

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

jdubious Valued Member Points: 72 More actions · Answer 10

We had the original sproc running.

Updated to the new sproc, installed history table, but the job doesn't complete. Is listed in history table with status incomplete, following message in jobs log:

Message

[162] Internal request (from SetJobNextRunDate [reason: schedule will not run again]) to deactivate schedule 115

Job is scheduled as 1 time, run dates in the past.

Any suggestions? Thanks!

J.

jdubious Valued Member Points: 72 More actions · Answer 11

bill2059 (5/12/2008)
I am running the Data Driven Subscription in a Cursor - for sending out unique instances of the report to individual locations. The problem I am running into is that I have to hard code in
WAITFOR DELAY '00:00:05'
Before the next fetch or it returns the default results (does not pass the new set of parameters).
Any thoughts on a delay solution that is not so arbitrary? I will eventually have a couple of hundred instances going out and if this "reset time" is variable I will have to make it a really long interval if there is no standard.
Thanks,
Bill

Can you post a sample running the dd subscription with a cursor?

We're trying to do the same thing, but so far not having any luck - thanks!

Randy-574768 Ten Centuries Points: 1385 More actions · Answer 12

Hello,

I wrote something similar as your procedure and ran into the same problem you were having with the username and password encryption for the createSubscription procedure.

Has anyone figured out a way around this? I tried following the one guy's link that said he had it, but I couldn't connect to his page.

Randy

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 13

Are you using the code from http://www.sqlservercentral.com/Forums/FindPost683529.aspx?

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]

Randy-574768 Ten Centuries Points: 1385 More actions · Answer 14

Are you referring to the following: http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html?

This is the one that I cannot open. I imagine its just the one I need, so I was hoping someone could give me a clue or another place to look for the same info.

The other two links did not seem to have what I needed.

Randy

Matthew Lehn Ten Centuries Points: 1263 More actions · Answer 15

Randy-574768 (11/2/2009)
Are you referring to the following: http://spilich.blogspot.com/2007/11/using-data-driven-subscriptions-in.html?
This is the one that I cannot open. I imagine its just the one I need, so I was hoping someone could give me a clue or another place to look for the same info.
The other two links did not seem to have what I needed.

That website opens fine over here. There are two links on that website that also open fine. I am using Firefox 3.0.15. Are you getting some kind of error?

[font="Tahoma"]Zycon - The Search Engine for Manufacturers[/url]. Helping Engineers and Technical Buyers Locate Aluminum Extrusions, Metal Stampers, Plastic Fabricators, Valves, and More.[/font]