Results 1 to 4 of 4
  1. #1
    Join Date
    Jun 2010
    Posts
    2

    Unanswered: New to forum, requesting help with a date function

    I have been searching for the solution for a while, but unsure even what I'm looking for/I don't know even what question to ask.

    I appreciate any help and I thank you in advance.

    I wish to make a SQL Server 2005 Express DB trigger on dates to alert when the current date approaches within 60 days, 90 days, 180 days of a Due Date field to advise that an event is coming due.

    I know that I cannot use mail services in 2005 Express to send an email, and I am also running the initial working effort on this DB on a Windows XP Pro laptop such that I can work on this at home nights and weekends.

    It may be that I need to schedule a stored procedure to run every day at a certain time and check DB field dates with current date, and use the difference to trigger an alert perhaps?

    I suspect someone might ask what kind of alert do I want it to provide, and my answer is a question--what CAN I make it do? Can I use a VB Script to make it send an email to a configured mailing list that would only work when my laptop is connected to the domain? Or does my instance of 2005 Express have to be running on a server in the domain?

    I am more than happy to read and experiment and tinker if someone can point me in the right direction, but I have a deadline to produce this DB, and I've already spent a lot of time basically poking around in the dark.

    Thank you!

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    There's roughly 1.68 gazallion solutions to your question. I don't have time to enumerate more than a half bazillion or so, and I'm going to start with just one and a very high level view of that.

    1) Install SSRS (SQL Server Reporting Services)
    2) Design a report showing rows the meet your 60, 90, 180 day criteria.
    3) Pretty it up to suit yourself
    4) Set output to an emailed PDF file
    5) Schedule the resulting report to run

    This should take somewhere between a day and a week depending on your programming skills and available time. I think you'll enjoy it though.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

  3. #3
    Join Date
    Jun 2010
    Posts
    2

    I was also looking at Reporting Services...

    I actually downloaded it yesterday, but I hadn't installed it yet.

    I will proceed down that path and see how that works for me.

    Thank you!

  4. #4
    Join Date
    Jun 2010
    Posts
    2

    Hi Pls try this

    Hi Pls try this steps


    What Has to Be Done

    Create a batch file and write the SQLCMD related commands in that.
    (like give ur trigger/storedprocedure that verifies that current date against Target Date within 60/90 dates)


    When Has To Be Done

    create Task(Run->tasks) to run this batch file and schedule accordingly as Per Ur need

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •