Showing posts with label pending. Show all posts
Showing posts with label pending. Show all posts

Tuesday, March 20, 2012

re-run snapshot agent for each new subscription?

If one day SubscriberA gets a push subscription, run snapshot agent, and
SubscriberA goes from pending to active one the snapshot agent is done. The
next day SubscriberB gets a push subscription from the same publication. It
doesn't seem to go from pending to active until I re-run the snapshot agent
(it has status of waiting for snapshot). Is this by design or am I doing
something wrong? Do I need to run the snapshot agent immediately everytime
I add a subscriber if I want the subscriber to be active immediately?
Thanks
Mike Jansen
Why is it that when I add a new transactional subscription I must re-run the
snapshot agent before the subscription becomes active, even if the snapshot
agent has been run recently? Do changes to the database "invalidate" the
snapshot or am I doing something wrong?
Thanks,
Mike
"Mike Jansen" <mjansen_nntp@.mail.com> wrote in message
news:uk8qPdNVFHA.3184@.TK2MSFTNGP15.phx.gbl...
> If one day SubscriberA gets a push subscription, run snapshot agent, and
> SubscriberA goes from pending to active one the snapshot agent is done.
The
> next day SubscriberB gets a push subscription from the same publication.
It
> doesn't seem to go from pending to active until I re-run the snapshot
agent
> (it has status of waiting for snapshot). Is this by design or am I doing
> something wrong? Do I need to run the snapshot agent immediately
everytime
> I add a subscriber if I want the subscriber to be active immediately?
> Thanks
> Mike Jansen
>
|||Mike,
the snapshot files will be deleted by the distribution cleanup agent once
all the subscribers have read them. If you want this not to occur so
frequently, you can schedule the distribution cleanup agent to run less
frequently eg at 12pm and have the snapshot agent run once the cleanup has
finished.
Rgds,
Paul Ibison
|||OK, that explains it. Thanks you very much.
Since you had such a good answer, I have another question
Is there a way to detect via T-SQL whether a valid snapshot exists?
Obviously I can't base it on job statuses because even if jobs complete, the
distribution cleanup will cleanup the snapshot. Is there another status
table I can check?
I'd rather check before running the snapshot agent _every_ time a new
subscription is created (the whole process is done in T-SQL scripts).
Thanks again,
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:e0To7bXVFHA.3272@.TK2MSFTNGP10.phx.gbl...
> Mike,
> the snapshot files will be deleted by the distribution cleanup agent once
> all the subscribers have read them. If you want this not to occur so
> frequently, you can schedule the distribution cleanup agent to run less
> frequently eg at 12pm and have the snapshot agent run once the cleanup has
> finished.
> Rgds,
> Paul Ibison
>
|||Mike,
take a look at sp_browsesnapshotfolder (sp_browsesnapshotfolder
'testrepxxx'). It'll return a row if there is a publication snapshot
available. If it's been cleaned up there will be an empty recordset.
Rgds,
Paul Ibison SQL Server MVP, www.replicationanswers.com
(recommended sql server 2000 replication book:
http://www.nwsu.com/0974973602p.html)
|||Excellent! I'll hopefully be testing/incorporating this at some point this
week. That was the last piece (for now!) in the replication part of my
database script framework. Thanks again.
Mike
"Paul Ibison" <Paul.Ibison@.Pygmalion.Com> wrote in message
news:ORAfK0hVFHA.3540@.TK2MSFTNGP15.phx.gbl...
> Mike,
> take a look at sp_browsesnapshotfolder (sp_browsesnapshotfolder
> 'testrepxxx'). It'll return a row if there is a publication snapshot
> available. If it's been cleaned up there will be an empty recordset.
> Rgds,
> Paul Ibison SQL Server MVP, www.replicationanswers.com
> (recommended sql server 2000 replication book:
> http://www.nwsu.com/0974973602p.html)
>

Monday, March 12, 2012

Request for info - detecting DB changes and batching for e-mail

I have a .NET app recently ported from 1.1 to 2.0 with a pending feature request. I'd first like to state that I'm not asking anyone for the programmatic answer, unless of course, you'd like to provide it. I'm simply asking the best way to accomplish this task with the tools I have available to me (VS 2005, SQL 2000 and 2005). I do not consider myself a professional .NET or T-SQL programmer but I do have a good understanding of the technologies so that I can find what I need to know once I know I'm going about something the right way.

The application I wrote, from scratch, manages News, Featured Connections, and FAQs for a web site. It was designed in a multi-layer approach where there are:

items -> assigned to categories -> assigned to users

So if user Fred is granted rights to category A, when he authenticates to the app he can Add/Change/Delete any item in category A. Simple enough. But also what Fred can do is see all the other items other users have created and *schedule* any relevant item related to his web page to appear within his own items even tho he has no rights to change that item in the admin interface.

The feature request is a notification service, so that Fred gets an e-mail when someone has added a new item or edited an existing one. I don't want Fred to get an e-mail *every* time an item is created or edited, that would be big bother and would generate lots of e-mails. What I'd like to do is send Fred an e-mail sometime in the middle of the night that informs him of the items added or changed since his last login, so that at his choosing he can go into the admin interface and schedule one or more of those items for his page.

Should I just write a new console app that does this, compile it and schedule the .EXE to run at midnight? Or is there a better way to leverage the new features in SQL 2005 (extended strored procs, triggers) and .NET to do this a better way? Currently, the DB is in SQL 2000 but I have no problems moving it to SQL 2005 if that helps me in some way. Thanks in advance for your comments.

...Bill

Table UserLastNotify

ID (useridtype - int/uid/whatever)

LastChangeSeen int

Create trigger on your categories table, recording a changeid, time,categoryid that changed to an auditCategories table.

Create a stored procedure (or cursor) that checks the last changeid in the auditCategories table to their entry in the UserLastNotify table, and then filter based on whatever criteria you want them to be notified about. Then generate the email, and update the UserLastNotify table with the current max of auditCategories (alternatively you can also use the max of the auditCategories id that you generated an email for them - since any higher you either haven't seen, or don't care about).

Create an sql job that runs at midnight every night that calls your stored procedure.