Error formatting query?

Question

Post reply

Error formatting query?

SQL Galaxy

One Orange Chip

Points: 29472
More actions
September 13, 2013 at 4:39 am

#395474

Hi
Error Messages, Could you help me. how to resolve this error? purpose of script email sending when rows selected..
Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.
select MACHINE, COUNT(*) as Instance
into #tableA
from LIMSPROD.dbo.CM_INSTANCE
group by MACHINE
Select COUNT(*)/4 as InstanceA
into #tableB
from LIMSPROD.dbo.CM_INSTANCE
Select A.MACHINE, A.Instance, B.InstanceA,
(B.InstanceA-A.Instance) as Diff
into #results
from #tableA A,
#tableB B
where (B.InstanceA-A.Instance) < -3 or (B.InstanceA-A.Instance) > 3
--number of records to not be zero
If ((select count(*) from #results) <> 0)
Begin
EXEC msdb.dbo.sp_send_dbmail
@profile_name='db_mail',
@recipients='ananda.murugesan@xyz.com',
@subject='Alert!-Verify Instance',
@query='select * from #results'
End
drop table #tableA
drop table #tableB
drop table #results

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Kurt W. Zimmerman SSCrazy Eights Points: 8676 More actions · Answer 1

Is there a more specific error?

Kurt

Kurt W. Zimmerman
SR DBA
Lefrak Organization
New York, NY

http://www.linkedin.com/in/kurtwzimmerman

SQL Galaxy One Orange Chip Points: 29472 More actions · Answer 2

GRANT EXECUTE ON [xp_sysmail_format_query] to [service account]

I have done permission of SQL Agent service account.. again same issues,

what could be worng above script.. Temp Table #Result rows not display in email body...

Error formatting query, probably invalid parameters [SQLSTATE 42000] (Error 22050). The step failed.

Thanks

ananda

Khamer Valued Member Points: 57 More actions · Answer 3

did you figure out the cause/solution? I am encountering something very similar.

Chris Harshman SSC-Forever Points: 42192 More actions · Answer 4

I believe the problem here is in this call:

EXEC msdb.dbo.sp_send_dbmail

@profile_name='db_mail',

@recipients='ananda.murugesan@xyz.com',

@subject='Alert!-Verify Instance',

@query='select * from #results'

The query in the msdb.dbo.sp_send_dbmail will be in a different scope than the rest of your script, so it will not be able to see the temporary table #results. If you need to do something like this, maybe try a global temporary table using the name ##results?