I have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."
Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy 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) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @.query
EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'
SELECT @.@.ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy.....here...
ThanksI have finally created (with your help) a stored procedure that is working and giving me the correct results,but when I have included the last part of my select statement where I am always trying to grab dates for the previous month it gives me the following syntax error "Incorrect syntax near '01'."
Here comes the precudere:
DECLARE @.returnDay int
DECLARE @.query varchar(8000)
--Looking at current date,
SELECT @.returnDay = DatePart(day,GetDate())
If @.returnDay = 8
BEGIN
SELECT @.query = 'bcp "SELECT a.HospitalName,a.HospitalCode,c.ProductName,b.Unit sDiscarded,d.FateOfProducts,b.DateEntered,b.DateCo mpleted,b.CompiledBy 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) where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01')ORDER BY a.HospitalID" queryout c:\test.txt -c -test -Usa -Ptest'
EXEC master.dbo.xp_cmdshell @.query
EXEC master.dbo.xp_sendmail @.recipients=test@.test.com',
@.copy_recipients = test@.test.com',
@.message='Submitting BloodBank Results for the previous month.
@.subject='BloodBank results for the previous month',@.attachments = '\\test\c$\test.txt'
SELECT @.@.ERROR As ErrorNumber
END
Could somebody help me and suggest something as I am going crazy.....here...
Thanks
I have realized how to go around this thanks to all
没有评论:
发表评论