Trying to perform a query using XP Sendmail

13/12/2007, 18h40

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

13/12/2007, 20h40

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

13/12/2007, 21h16

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.

13/12/2007, 21h50

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

13/12/2007, 22h48

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 !!

13/12/2007, 23h23

(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 !!

13/12/2007, 23h24

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?

13/12/2007, 23h57

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

14/12/2007, 15h10

"Mike K" <vwttracer@hotmail.com> wrote in message
news:8af1fbb5-a087-4e8a-a4e3-fe425e198ce1@e25g2000prg.googlegroups.com...
> 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

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

14/12/2007, 15h28

On Dec 14, 7:10 am, "Greg D. Moore \(Strider\)"
<mooregr_deletet...@greenms.com> wrote:
> "Mike K" <vwttra...@hotmail.com> wrote in message
>
> news:8af1fbb5-a087-4e8a-a4e3-fe425e198ce1@e25g2000prg.googlegroups.com...
>
>
>
> > 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
>
> 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

HAHA it was one of those double kinda day!

13/12/2007, 18h40	#1
Mike K Messages: n/a Hébergeur:	Trying to perform a query using XP Sendmail 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

13/12/2007, 20h40	#2
Plamen Ratchev Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail 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

13/12/2007, 21h16	#3
Mike K Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail 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.

13/12/2007, 21h50	#4
Plamen Ratchev Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail 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

13/12/2007, 22h48	#5
Mike K Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail 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 !!

13/12/2007, 23h23	#6
Greg D. Moore \(Strider\) Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail (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 !!

13/12/2007, 23h24	#7
Mike K Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail 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?

13/12/2007, 23h57	#8
Mike K Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail 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

14/12/2007, 15h10	#9
Greg D. Moore \(Strider\) Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail "Mike K" <vwttracer@hotmail.com> wrote in message news:8af1fbb5-a087-4e8a-a4e3-fe425e198ce1@e25g2000prg.googlegroups.com... > 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 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

14/12/2007, 15h28	#10
Mike K Messages: n/a Hébergeur:	Re: Trying to perform a query using XP Sendmail On Dec 14, 7:10 am, "Greg D. Moore \(Strider\)" <mooregr_deletet...@greenms.com> wrote: > "Mike K" <vwttra...@hotmail.com> wrote in message > > news:8af1fbb5-a087-4e8a-a4e3-fe425e198ce1@e25g2000prg.googlegroups.com... > > > > > 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 > > 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 HAHA it was one of those double kinda day!

Outils de la discussion
Afficher une version imprimable Envoyer un lien vers cette page par email