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

REgardsEXEC 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'|||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?|||it seems that the allowed lenght of the @.query is 128 only so it is giving an error.|||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...|||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?|||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|||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 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'|||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'

:eek:
smart|||Thank you guys heaps...|||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 .


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 ...|||Excellent, many thanks