Link to home
Start Free TrialLog in
Avatar of GPSPOW
GPSPOWFlag for United States of America

asked on

Query execution failure when running sp_send_dbmail

i am getting the following error when I try to send an email via the sp_send_dbmail procedure:

Msg 22050, Level 16, State 1, Line 0
Error formatting query, probably invalid parameters
Msg 14661, Level 16, State 1, Procedure sp_send_dbmail, Line 504
Query execution failed: Msg 137, Level 15, State 2, Server PTM-DR01, Line 1
Must declare the scalar variable "@SRT".


Here is the code snippet:

declare @SRT as numeric(15,2);
	declare @SOPS as numeric(15,2);
	declare @SNPA as numeric(15,2);

	select @SRT = SUM(CAmt) 
	   from livedb.dbo.tbl_DAILY_ATB_CMBAL
	Select @SOPS = [Tot]
	FROM [livedb].[dbo].[vw_DAILY_ATB_OPS_BALANCE]
	Select @SNPA = [NPABal]
	  FROM [livedb].[dbo].[vw_DALY_ATB_NPA_BALANCE]
	Select @SRT as RTBal, @SOPS-@SNPA as OPSBAL

IF @SRT <> @SOPS-@SNPA
		Begin
		   exec msdb.dbo.sp_send_dbmail
		   @profile_name='GlenMail',
		   @recipients ='gpspow55@gmail.com',
		   @subject ='Daily ATB SQL table Update Status',
		   @body = 'The processing of the DAILY_ATB_BALANCE table was unsuccessful.  
		   The calculated End of Day balance does not equal the Operations Summary 
		   less the NPA Accounts',
		   @query = 'SELECT	@SRT as CalcBalance, @SOPS as OperSumBal, @SNPA as NPABal, 
		   @SRT-(@SOPS-@SNPA) as Variance'
		End

Open in new window


Thanks

Glen
Avatar of ste5an
ste5an
Flag of Germany image

Yup, it tries to execute

@query = 'SELECT	@SRT as CalcBalance, @SOPS as OperSumBal, @SNPA as NPABal,  @SRT-(@SOPS-@SNPA) as Variance';

Open in new window


which is non-sense, cause it's executed in a different scope. @query must contain a runnable T-SQL query or batch.
ASKER CERTIFIED SOLUTION
Avatar of EugeneZ
EugeneZ
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of GPSPOW

ASKER

if OBJECT_ID(N'tempdb..#Bal') is not null
                 Begin
                         drop table #Bal
                 End
 create table #Bal(
                 BalAmt numeric(15,2) );
 insert into #Bal
         Select convert(numeric(15,2),coalesce(SUM(CAmt),0)) as BalAmt
            from livedb.dbo.tbl_DAILY_ATB_CMBAL
 insert into #Bal
         select   CONVERT(numeric(15,2),coalesce(-[Tot],0))  as BalAmt
         FROM [livedb].[dbo].[vw_DAILY_ATB_OPS_BALANCE]
 insert into #Bal
         select  CONVERT(numeric(15,2),coalesce(-[NPABal],0)) as BalAmt
           FROM [livedb].[dbo].[vw_DALY_ATB_NPA_BALANCE]
 select sum(BalAmt) as BA from #Bal
 truncate table  livedb.dbo.tbl_DAILY_ATB_CHECL_BALANCE
 insert into  livedb.dbo.tbl_DAILY_ATB_CHECL_BALANCE
           exec msdb.dbo.sp_send_dbmail
            @profile_name='GlenMail',
                    @recipients ='gpspow55@gmail.com',
                    @subject ='Daily ATB SQL table Update Status',
                    @body = 'The processing of the DAILY_ATB_BALANCE table was unsuccessful.
                    The calculated End of Day balance does not equal the Operations Summary
                    less the NPA Accounts',
                    @query = 'Select SUM(CAmt) as STot
            from livedb.dbo.tbl_DAILY_ATB_CMBAL;
 select     [Tot]  as OTot
         FROM [livedb].[dbo].[vw_DAILY_ATB_OPS_BALANCE] ;
          select  [NPABal] as NTot
           FROM [livedb].[dbo].[vw_DALY_ATB_NPA_BALANCE] ;
          Select SUM(BalAmt)  as ChkBal from livedb.dbo.tbl_DAILY_ATB_CHECL_BALANCE;
 '

Open in new window


 I was able to rewrite my code.  The embedded code save the variables to a temp table which is later inserted into a SQL table that can be accessed by my email.

Glen
--typo
must be    Declare @query1 Nvarchar(max);
Avatar of GPSPOW

ASKER

I've requested that this question be closed as follows:

Accepted answer: 0 points for GPSPOW's comment #a40865795

for the following reason:

I had to figure out what was wrong on my own.  This works, but maybe there is a more efficient way of performing the same task.

Glen
Avatar of GPSPOW

ASKER

This does look better.

I will try it out.

Thanks

Glen