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

    Unhappy Unanswered: procedure runs with an error???

    This is my procedure and the error is incorrect syntax near '01'

    DECLARE @returnDay int

    --Looking at current date,
    SELECT @returnDay = DatePart(day,GetDate())
    --If is the 7th of the current moth then
    If @returnDay = 24

    EXEC master.dbo.xp_sendmail
    @query = 'SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
    b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)
    where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID',
    @recipients=test@hotmail.com', @message='Submitting Results for the previous month',
    @subject=' results for previous month', @attach_results = 'true', @separator = '/s'

    SELECT @@ERROR As ErrorNumber


    What am I missing here now, I am quite new to stored procedures


    REgards

  2. #2
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    EXEC master.dbo.xp_sendmail
    @query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
    b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)
    where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
    @recipients=test@hotmail.com', @message='Submitting Results for the previous month',
    @subject=' results for previous month', @attach_results = 'true', @separator = '/s'
    Get yourself a copy of the The Holy Book

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

  3. #3
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thank you for your help.
    Now I have this error message

    The identifier that starts with 'SELECT a.HospitalName,c.ProductName,b.UnitsDiscarded,d.Fa teOfProducts,b.DateEntered,
    b.DateCompleted,b.CompiledBy
    FROM Test.database' is too long. Maximum length is 128.

    I have seen this error but not a solution for it....
    Could you help me with that?
    Last edited by zobernjik; 05-26-04 at 01:43.

  4. #4
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    it seems that the allowed lenght of the @query is 128 only so it is giving an error.

  5. #5
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Yeah I got that too,but I need all the data from the query so how I am suppose to go around it, any suggestions....

  6. #6
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by zobernjik
    Yeah I got that too,but I need all the data from the query so how I am suppose to go around it, any suggestions....
    how about using a staging table (some temp table) to get the results and while sending the mail just select the records from this staging table?

  7. #7
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    I hope I am not annoying everybody,but how do I use the temp table,do I create a replica of a table that has all the data or....I see what you mean expect I don't quite know what is my next step...

    Any help appreciated

  8. #8
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    set quoted_identifier off
    EXEC master.dbo.xp_sendmail
    @query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
    b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)
    where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
    @recipients='test@hotmail.com', @message='Submitting Results for the previous month',
    @subject=' results for previous month', @attach_results = 'true', @separator = '/s'
    Get yourself a copy of the The Holy Book

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

  9. #9
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by zobernjik
    I hope I am not annoying everybody,but how do I use the temp table,do I create a replica of a table that has all the data or....I see what you mean expect I don't quite know what is my next step...

    Any help appreciated
    SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
    b.DateCompleted,b.CompiledBy into #temp FROM master.dbo.units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)
    where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID



    EXEC master.dbo.xp_sendmail
    @query = "SELECT * from #temp",
    @recipients=test@hotmail.com', @message='Submitting Results for the previous month',
    @subject=' results for previous month', @attach_results = 'true', @separator = '/s'

  10. #10
    Join Date
    Jan 2003
    Location
    India
    Posts
    523
    Quote Originally Posted by Enigma
    set quoted_identifier off
    EXEC master.dbo.xp_sendmail
    @query = "SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b. DateEntered,
    b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID)
    inner join FateOfProducts d ON (d.FateID = b.FateID)
    where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + '01') order by a.HospitalID",
    @recipients='test@hotmail.com', @message='Submitting Results for the previous month',
    @subject=' results for previous month', @attach_results = 'true', @separator = '/s'


    smart

  11. #11
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Thank you guys heaps....

  12. #12
    Join Date
    Jul 2003
    Location
    The Dark Planet
    Posts
    1,401
    By default in SQL Server 2000 ... the entries inside "" (double quotes) are taken as identifiers ... you need to turn them off while using them as a way to avoid this happening.

    alternatively you can use the single quote two times to use them within a string

    eg .

    Code:
    set quoted_identifier off
    EXEC master.dbo.xp_sendmail 
    @query = 'SELECT a.HospitalName,a.HospitalCode, c.ProductName,b.UnitsDiscarded,d.FateOfProducts,b.DateEntered,
    b.DateCompleted,b.CompiledBy FROM test.dbo.Units b inner join Hospitals a ON (a.HospitalID = b.HospitalID)
    inner join Products c ON (b.ProductID = c.ProductID) 
    inner join FateOfProducts d ON (d.FateID = b.FateID) 
    where b. DateEntered = DateAdd(month, -1, Convert(CHAR(8), GetDate(), 121) + ''01'') order by a.HospitalID', 
    @recipients='test@hotmail.com', @message='Submitting Results for the previous month',
    @subject=' results for previous month', @attach_results = 'true', @separator = '/s'
    I hope I have been able to explain it clearly ...
    Get yourself a copy of the The Holy Book

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

  13. #13
    Join Date
    Feb 2004
    Location
    Australia
    Posts
    183
    Excellent, many thanks

Posting Permissions

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