January 15, 2009 at 3:57 pm
I have a query I run in query analyzer, and I get the correct results.
When I embed this query within the SP_SEND_COMMAND, I get different/incorrect results.
Why would the results of these two scripts be different? Thanks!
Incorrect results
------------------
EXEC [orecal_sql\sql2005].msdb.dbo.sp_send_dbmail
@profile_name = 'DBMailProfile',
@recipients = 'davidr@ore-cal.com',
@body = 'If the customer is flagged with a chep id, the order must indicate chep pallets. If the order is updated with chep pallets, then the customer shipping record must have a chep id!',
@subject = 'The following orders have missing or inconsistent CHEP data',
@query=
'declare @result int
set @result=
(select count(*)
from ocdb.asctraclive.dbo.ordrhdr ordrhdr
join [orecal_sql\sql2005].ocgp.dbo.rm00102 rm00102 on rm00102.adrscode=ordrhdr.host_shiptocustid
where (pickstatus="C" and shipdate>="1-14-2009")
and ((ordrhdr.numchepsshipped=0 and rm00102.userdef1<>'' and rm00102.userdef1 is not null) or (ordrhdr.numchepsshipped<>0 and rm00102.userdef1='')))
if @result<>0
begin
select ordrhdr.ordernumber,rm00102.adrscode,ordrhdr.numchepsshipped,rm00102.userdef1
from ocdb.asctraclive.dbo.ordrhdr ordrhdr
join [orecal_sql\sql2005].ocgp.dbo.rm00102 rm00102 on rm00102.adrscode=ordrhdr.host_shiptocustid
where (pickstatus="C" and shipdate>="1-14-2009")
and ((ordrhdr.numchepsshipped=0 and rm00102.userdef1<>''and rm00102.userdef1 is not null) or (ordrhdr.numchepsshipped<>0 and rm00102.userdef1=''))
end
else
print "there is no data"',
@attach_query_result_as_file=1,
@query_attachment_filename='Chep inconsistencies.txt'
Correct results
----------------
declare @result int
set @result=
(select count(*)
from ocdb.asctraclive.dbo.ordrhdr ordrhdr
join [orecal_sql\sql2005].ocgp.dbo.rm00102 rm00102 on rm00102.adrscode=ordrhdr.host_shiptocustid
where (pickstatus='C' and shipdate>='1-14-2009')
and ((ordrhdr.numchepsshipped=0 and rm00102.userdef1<>'' and rm00102.userdef1 is not null) or (ordrhdr.numchepsshipped<>0 and rm00102.userdef1='')))
if @result<>0
begin
select ordrhdr.ordernumber,rm00102.adrscode,ordrhdr.numchepsshipped,rm00102.userdef1
from ocdb.asctraclive.dbo.ordrhdr ordrhdr
join [orecal_sql\sql2005].ocgp.dbo.rm00102 rm00102 on rm00102.adrscode=ordrhdr.host_shiptocustid
where (pickstatus='C' and shipdate>='1-14-2009')
and ((ordrhdr.numchepsshipped=0 and rm00102.userdef1<>''and rm00102.userdef1 is not null) or (ordrhdr.numchepsshipped<>0 and rm00102.userdef1=''))
end
else
print 'there is no data'
January 15, 2009 at 4:40 pm
A "query" probably has to be a single statement.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
January 16, 2009 at 8:19 am
I tried a single statement, and no difference 🙁
January 16, 2009 at 9:02 am
Solution found!
I had to replace rm00102='' with len(rm00102)=0
Not sure why I didnt have to do this is the stand alone sql query,but making this change in the SP_send_dbmail query resolved my issue!
January 16, 2009 at 10:04 am
Hmmm, that makes it sound like it is a quote doubling problem ...
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply