Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Unhappy Unanswered: Running a proc. on a certain date help?

    DECLARE @returnDay int
    DECLARE @query varchar(8000)
    --Looking at current date,
    SELECT @returnDay = DatePart(day,GetDate())
    If @returnDay = 3

    SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
    EXEC master.dbo.xp_cmdshell @query

    EXEC master.dbo.xp_sendmail @recipients='test@hotmail.com',
    @copy_recipients = 'test@hotmail.com',
    @message='Submitting Results for the previous month.',
    @subject='BloodBank results for the previous month',@attachments = '\\cen\c$\test.txt'

    SELECT @@ERROR As ErrorNumber

    I am trying to get this procedure to execute every month on the 4th of the month but if I run it today, or tomorrow it or any day it still runs,therefore the not looking at the date.
    Is this correct,can this be done in this way,how can I get it to run when it recognizes the date number in the current date

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Use SQL Agent to schedule it ?

    -PatP

  3. #3
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    DECLARE @returnDay int
    DECLARE @query varchar(8000)
    --Looking at current date,
    SELECT @returnDay = day(GetDate())
    If @returnDay = 3
    begin
    SELECT @query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,b.DateEntered,b.DateCompleted,b.Compile dBy FROM Ivana_test.dbo.Units b INNER JOIN Ivana_test.dbo.Hospitals a ON (a.HospitalID = b.HospitalID)INNER JOIN Ivana_test.dbo.Products c ON (b.ProductID = c.ProductID)INNER JOIN Ivana_test.dbo.FateOfProducts d ON (d.FateID = b.FateID)ORDER BY a.HospitalID" queryout c:\test.txt -c -Sserver -Usa -Ptest
    EXEC master.dbo.xp_cmdshell @query

    EXEC master.dbo.xp_sendmail @recipients='test@hotmail.com',
    @copy_recipients = 'test@hotmail.com',
    @message='Submitting Results for the previous month.',
    @subject='BloodBank results for the previous month',@attachments = '\\cen\c$\test.txt'

    SELECT @@ERROR As ErrorNumber
    end
    Get yourself a copy of the The Holy Book

    order has no physical Brett in The meaning of a Kaiser . -database data

Posting Permissions

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