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 . 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@xxxxx.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@xxxxx.com',
@subject = @SubjectLine,
@query = @strBody

HTH,

Plamen Ratchev
http://www.SQLStudio.com

On Dec 13, 12:40 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> Hi Mike,
>
> Seems you are missing the @query parameter of xp_sendmail. Try changing you
> code to:
>
> exec master.dbo.xp_sendmail
> @recipients = 'x...@xxxxx.com',
> @subject = @SubjectLine,
> @query = @strBody
>
> HTH,
>
> Plamen Ratchevhttp://www.SQLStudio.com

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...@SQLStudio.com> wrote:
> 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 Ratchevhttp://www.SQLStudio.com

Thanks that really ed. 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 !!

(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@hotmail.com> wrote in message
news:2ee535d8-660f-47d9-ba37-cae5734ad537@d4g2000prg.googlegroups.com...
> On Dec 13, 1:50 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
>> 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 Ratchevhttp://www.SQLStudio.com
>
> Thanks that really ed. 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 !!

On Dec 13, 3:23 pm, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@greenms.com> wrote:
> (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" <vwttra...@hotmail.com> wrote in message
>
> news:2ee535d8-660f-47d9-ba37-cae5734ad537@d4g2000prg.googlegroups.com...
>
> > On Dec 13, 1:50 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> >> 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 Ratchevhttp://www.SQLStudio.com
>
> > Thanks that really ed. 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 !!

I have tried that... i get an invalid column name. Is this a
conversion issue?

On Dec 13, 3:24 pm, Mike K <vwttra...@hotmail.com> wrote:
> On Dec 13, 3:23 pm, "Greg D. Moore \(Strider\)"
>
>
>
> <mooregr_deletet...@greenms.com> wrote:
> > (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" <vwttra...@hotmail.com> wrote in message
>
> >news:2ee535d8-660f-47d9-ba37-cae5734ad537@d4g2000prg.googlegroups.com...
>
> > > On Dec 13, 1:50 pm, "Plamen Ratchev" <Pla...@SQLStudio.com> wrote:
> > >> 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 Ratchevhttp://www.SQLStudio.com
>
> > > Thanks that really ed. 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 !!
>
> I have tried that... i get an invalid column name. Is this a
> conversion issue?

I just realized what you were trying to say Gregg. Its two single
quotes '' not a double " . That worked. Thanks