xp_sendmail

[sleezy]

On a daily basis the user is running queries , cuts and pastes the results into Excel and then do his calculations.l've written a strored proc that packages all the queries and then just generate the summary of the results which need to be emailed out to the managers. My problem is when l run the exec xp_sendmail the @message part is not picking up the summary results. When l do the select it works.How can l fix this ? The other thing is that the last part is truncating ? If the result is null how do l handle that. It gives back a null?

tried using @attach it gave me an ODBC error thats why l'm trying this now

DECLARE @Balance_Differences VARCHAR(60)

SELECT @BALANCE_DIFFERENCES = (Select Count(*) AS BALANCE_DIFFERENCES From tbReconBalances (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121)
Group By ReconDate
)

--Reason Code differences
DECLARE @Reason_Code_Differences VARCHAR(60)

SELECT @Reason_Code_Differences = (Select Count(*) From tbReconReasonCodes (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121)
Group By ReconDate
)

--Accounts not in Tallyman
DECLARE @Acc_Not_In_TM VARCHAR(60)

SELECT @Acc_Not_In_TM = (Select Count(*) From tbReconNotInTallyman (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121)
Group By ReconDate
)

--New accounts out of balance
DECLARE @New_Acc_Out VARCHAR(60)

SELECT @New_Acc_Out = (Select Count (*)From tbReconBalances (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121) And CustCode Not In
(Select Custcode From tbReconBalances Where ReconDate = Convert(Char(10), GetDate()-1, 121))
)

--Account were out of balance, now balance
DECLARE @Acc_Bal VARCHAR(60)

SELECT @Acc_Bal = (Select Count (*)From tbReconBalances (NoLock)
Where ReconDate = Convert(Char(10), GetDate()-1, 121) And CustCode Not In
(Select Custcode From tbReconBalances Where ReconDate = Convert(Char(10), GetDate(), 121))
)

--EMAIL CONTENT:- CHAR(13) TO INSERT CONTROL CHARACTER TO SEPERATE LINES
SELECT 'BALANCE DIFFERENCES => ' + ' ' + @BALANCE_DIFFERENCES + CHAR(13) +
'REASON CODE_DIFFERENCES => ' + ' ' + @Reason_Code_Differences + CHAR(13) +
'ACCOUNTS NOT IN TALLYMAN => ' + ' ' + @Acc_Not_In_TM + CHAR(13) +
'NEW ACCOUNTS OUT OF BALANCE => ' + ' ' + @New_Acc_Out + CHAR(13) +
'ACCOUNTS THAT WERE OUT OF BALANCE, NOW BALANCE => ' + ' ' + @Acc_Bal


---This part wont work ?????
EXEC master..XP_SENDMAIL
@Recipients = 'ttttttttt@test.co.za'
,@Message =
(SELECT 'BALANCE DIFFERENCES => ' + ' ' + @BALANCE_DIFFERENCES + CHAR(13) +
'REASON CODE_DIFFERENCES => ' + ' ' + @Reason_Code_Differences + CHAR(13) +
'ACCOUNTS NOT IN TALLYMAN => ' + ' ' + @Acc_Not_In_TM + CHAR(13) +
'NEW ACCOUNTS OUT OF BALANCE => ' + ' ' + @New_Acc_Out + CHAR(13) +
'ACCOUNTS THAT WERE OUT OF BALANCE, NOW BALANCE => ' + ' ' + @Acc_Bal
,@Subject = 'Duzi to Tallyman Load'

[MAK]

SET CONCAT_NULL_YIELDS_NULL OFF

should solve the issue.

[sleezy]

The Zeros have now been taken care of. Now l''m trying to set the ,@Message with my result set but l can't seem to get it working. tried the @query var as well. It should look like this

2005-04-11 Daily Recon
----------------------

BALANCE DIFFERENCES => 24616
REASON CODE_DIFFERENCES => 61
ACCOUNTS NOT IN TALLYMAN => 41
NEW ACCOUNTS OUT OF BALANCE => 24616
ACCOUNTS OUT OF BALANCE, NOW BALANCE => 0



SET CONCAT_NULL_YIELDS_NULL OFF

DECLARE @Balance_Differences VARCHAR(60)

SELECT @BALANCE_DIFFERENCES = (Select Count(*) AS BALANCE_DIFFERENCES From tbReconBalances (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121)
Group By ReconDate
)

--Reason Code differences
DECLARE @Reason_Code_Differences VARCHAR(60)

SELECT @Reason_Code_Differences = (Select Count(*) From tbReconReasonCodes (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121)
Group By ReconDate
)

--Accounts not in Tallyman
DECLARE @Acc_Not_In_TM VARCHAR(60)

SELECT @Acc_Not_In_TM = (Select Count(*) From tbReconNotInTallyman (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121)
Group By ReconDate
)

--New accounts out of balance
DECLARE @New_Acc_Out VARCHAR(60)

SELECT @New_Acc_Out = (Select Count (*)From tbReconBalances (NoLock)
Where ReconDate = Convert(Char(10), GetDate(), 121) And CustCode Not In
(Select Custcode From tbReconBalances Where ReconDate = Convert(Char(10), GetDate()-1, 121))
)

--Account were out of balance, now balance
DECLARE @Acc_Bal VARCHAR(60)

SELECT @Acc_Bal = (Select Count (*)From tbReconBalances (NoLock)
Where ReconDate = Convert(Char(10), GetDate()-1, 121) And CustCode Not In
(Select Custcode From tbReconBalances Where ReconDate = Convert(Char(10), GetDate(), 121))
)

--EMAIL CONTENT:- CHAR(13) TO INSERT CONTROL CHARACTER TO SEPERATE LINES
IF OBJECT_ID('tempdb..#tbEmailText') IS NOT NULL
DROP TABLE #tbEmailText

CREATE TABLE #tbEmailText(String Varchar(8000)) INSERT INTO #tbEmailText
SELECT
'BALANCE DIFFERENCES => ' + ' ' + @BALANCE_DIFFERENCES + CHAR(13) +
'REASON CODE_DIFFERENCES => ' + ' ' + @Reason_Code_Differences + CHAR(13) +
'ACCOUNTS NOT IN TALLYMAN => ' + ' ' + @Acc_Not_In_TM + CHAR(13) +
'NEW ACCOUNTS OUT OF BALANCE => ' + ' ' + @New_Acc_Out + CHAR(13) +
'ACCOUNTS OUT OF BALANCE, NOW BALANCE => ' + ' ' + @Acc_Bal


---This part wont work ?????
EXEC Master..XP_SENDMAIL
@Recipients = 'rmiller@toms.co.za'
,@Message = 'Select * from #tbEmailText'
,@Subject = 'Duzi to Tallyman Recon'