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

    Unhappy Unanswered: Don't worry about this post....

    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...
    Thanks
    Last edited by zobernjik; 06-06-04 at 22:17.

  2. #2
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183

    Don't worry about this post,fixed

    Quote Originally Posted by zobernjik
    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...
    Thanks


    I have realized how to go around this thanks to all

Posting Permissions

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