Hi, I am new to SQL and I am trying to email my group results of a
query as part of a message body. I created a SP and I am using
XPSendmail. Everything works fine except for the fact that the body of
the email doesn't have my query results. I am hoping somebody can
check what I have here and maybe give me some insights. I really
appreciate all your help. Thanks! Here is the sp:

CREATE PROCEDURE [dbo].[spEmailVariance]
(
@SubjectLine as varchar(500),
@EmailRecipient VARCHAR(100)
)
AS

DECLARE @strBody varchar(1000)
set @SubjectLine = 'ZZZZZZZ'
SET @strBody =
'SELECT FSA_Weekly_Flash.dbo.Current_Period_Week_Flash.Fis cal_Year AS
Accounting_Year,

FSA_Weekly_Flash.dbo.Current_Period_Week_Flash.WK_ IN_FYEAR,
FSA_Weekly_Flash.dbo.Branch.Description AS Location,
FSA_Weekly_Flash.dbo.GL_Account.GL_Account,
SUM(FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Ba lance_Flash.Amount)
AS Flash,

SUM(FSA_Findata.dbo.Weekly_Account_Balance.Amount) AS FinData,
SUM(FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Ba lance_Flash.Amount)
-
SUM(FSA_Findata.dbo.Weekly_Account_Balance.Amount) AS Difference
FROM FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash
INNER JOIN
FSA_Weekly_Flash.dbo.Current_Period_Week_Flash
ON

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.Accounting_Year
= FSA_Weekly_Flash.dbo.Current_Period_Week_Flash.Fis cal_Year
AND

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.Week_Number
= FSA_Weekly_Flash.dbo.Current_Period_Week_Flash.WK_ IN_FYEAR
INNER JOIN
FSA_Weekly_Flash.dbo.Branch ON

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.Branch =
FSA_Weekly_Flash.dbo.Branch.Branch_Number INNER JOIN
dbo.GL_Account ON
SUBSTRING(FSA_Weekly_Flash.dbo.Staging_Weekly_Acco unt_Balance_Flash.GL_Account,
1, 7)
= dbo.GL_Account.GL_Account INNER JOIN
FSA_Findata.dbo.Weekly_Account_Balance ON

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.GL_Account =
FSA_Findata.dbo.Weekly_Account_Balance.GL_Account AND

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.Accounting_Year
= FSA_Findata.dbo.Weekly_Account_Balance.Accounting_ Year AND

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.Week_Number
= FSA_Findata.dbo.Weekly_Account_Balance.Week_Number AND

FSA_Weekly_Flash.dbo.Staging_Weekly_Account_Balanc e_Flash.Branch =
FSA_Findata.dbo.Weekly_Account_Balance.Branch
GROUP BY FSA_Weekly_Flash.dbo.Current_Period_Week_Flash.Fis cal_Year,
FSA_Weekly_Flash.dbo.Branch.Description, dbo.GL_Account.GL_Account,

FSA_Weekly_Flash.dbo.Current_Period_Week_Flash.WK_ IN_FYEAR
HAVING (dbo.GL_Account.GL_Account = "500-001")
ORDER BY FSA_Weekly_Flash.dbo.Branch.Description'


exec master.dbo.xp_sendmail
@recipients= 'xxxx (AT) xxxxx (DOT) com',
@subject= @SubjectLine


RETURN


GO

Hi Mike,

Seems you are missing the @query parameter of xp_sendmail. Try changing you
code to:

exec master.dbo.xp_sendmail
@recipients = 'xxxx (AT) xxxxx (DOT) com',
@subject = @SubjectLine,
@query = @strBody

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Dec 13, 12:40 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Thank you! I added that but now i am getting the following error:
"ODBC error 170 (42000) Line 10: Incorrect syntax near
'FSA_Weekly_Fla'."

This query runs fine when i run it by itself i.e without embedding it
part of this sp. I am going to troubleshoot and see what the syntax
error is.

You declared @strBody as varchar(1000) and in fact your SQL statement is
around 2339 characters (according to a quick copy/paste to a text editor).
Try declaring as varchar(3000).

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Dec 13, 1:50 pm, "Plamen Ratchev" <Pla... (AT) SQLStudio (DOT) com> wrote:
Thanks that really helped. I glanced over it and yet totally missed
it. My other question is how do I specify the having clause if I put
the single quotes like this: HAVING
(FSA_Weekly_Flash.dbo.GL_Account.GL_Account) = '500-001' I get an
error. I can again make it work fine in query analyzer but I cant seem
to figure out how to make it work in this stored procedure. Thanks for
all your help!!

(FSA_Weekly_Flash.dbo.GL_Account.GL_Account) = ''500-001''

--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

"Mike K" <vwttracer (AT) hotmail (DOT) com> wrote

On Dec 13, 3:23 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:
I have tried that... i get an invalid column name. Is this a
conversion issue?

On Dec 13, 3:24 pm, Mike K <vwttra... (AT) hotmail (DOT) com> wrote:
I just realized what you were trying to say Gregg. Its two single
quotes '' not a double " . That worked. Thanks

"Mike K" <vwttracer (AT) hotmail (DOT) com> wrote

Not a problem.

BTW, that's Greg with single G, not double G :-)



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

On Dec 14, 7:10 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet... (AT) greenms (DOT) com> wrote:
HAHA it was one of those double kinda day!