Wednesday, March 28, 2012

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 dateUse SQL Agent (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/architec/8_ar_cs_6x0l.asp) to schedule it ?

-PatP|||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
endsql

No comments:

Post a Comment