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
GPSPOWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

ste5anSenior DeveloperCommented:
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.
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
--try to use for  @query  ( you may create an another Var e.g. @query1 and use with @query  ..@query =@query 1

'SELECT      ' + Cast  (@SRT  as NVarchar(10))  + '  as CalcBalance,
                    + Cast (@SOPS as NVarchar(10))  + ' as OperSumBal,
                     + Cast (@SNPA as NVarchar(10))  + ' as NPABal,
               + Cast (  @SRT-(@SOPS-@SNPA) as NVarchar(10))  + ' as Variance'

something like this one
declare @SRT as numeric(15,2);
	declare @SOPS as numeric(15,2);
	declare @SNPA as numeric(15,2);
       Decalare @query1 Nvarchar(max);


	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

                     set @query1= 'SELECT	' + Cast  (@SRT  as NVarchar(10))  + '  as CalcBalance, 
                                                                 + Cast (@SOPS as NVarchar(10))  + ' as OperSumBal,
                                                                  + Cast (@SNPA as NVarchar(10))  + ' as NPABal, 
		                                                  + Cast (  @SRT-(@SOPS-@SNPA) as NVarchar(10))  + ' as Variance'


		   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 = @query1
		End

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GPSPOWAuthor Commented:
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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
--typo
must be    Declare @query1 Nvarchar(max);
GPSPOWAuthor Commented:
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
GPSPOWAuthor Commented:
This does look better.

I will try it out.

Thanks

Glen
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.