May 15, 2009 at 12:57 pm
The query works fine on it's own, and when I run it inside the @query function it errors out.
The following returns an error
Msg 102, Level 15, State 1, Line 8
Incorrect syntax near 'SQ'.
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NTSVR1 Mail',
@recipients = 'joe@web.com',
@query = 'SELECT COUNT(*)
from JDE_Production.proddta.f4211
join JDE_Production.proddta.f4101
on JDE_Production.proddta.f4211.sdlitm=JDE_Production.proddta.f4101.imlitm
where JDE_Production.proddta.f4211.sddcto <> 'SQ' and JDE_Production.proddta.f4211.sdglc<>JDE_Production.proddta.f4101.imglpt
and JDE_Production.proddta.f4211.sdupmj>109000'
@subject = 'Mismatch',
@body = 'The stored procedure finished successfully.',
@attach_query_result_as_file = 1 ;
Are there any special formatting rules for this?
May 15, 2009 at 2:04 pm
Because that is in the string you have to use double single quotes around text strings such as SQ. So, you would actually have ''SQ'' (those are two single quotes, not double quotes).
HTH.
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 15, 2009 at 2:14 pm
I thought that might be it, so I simplified it to:
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NTSVR1 Mail',
@recipients = 'joe@web.com',
@query = 'SELECT count(*) from JDE_Production.proddta.f4211'
@subject = 'Mismatch',
@body = 'The stored procedure finished successfully.',
@attach_query_result_as_file = 1 ;
This gets an error:
Incorrect syntax near '@subject'.
May 15, 2009 at 2:32 pm
Try this;
EXEC msdb.dbo.sp_send_dbmail
@profile_name = 'NTSVR1 Mail',
@recipients = 'joe@web.com',
@query = 'SELECT count(*) from JDE_Production.proddta.f4211',
@subject = 'Mismatch',
@body = 'The stored procedure finished successfully.',
@attach_query_result_as_file = 1 ;
You forgot the comma after the @query line. 😉
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
May 15, 2009 at 2:46 pm
thx... hence the "syntax error" clue...
May 15, 2009 at 2:54 pm
robert_daniel (5/15/2009)
thx... hence the "syntax error" clue...
Glad to help. Always nice to have another set of eyes to see stuff like that. I have many dents on my forehead from things like that. 😛
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply