This is my log - if I run the procedure locally it will still perform as it should and generate the message, but when it is run as a SQL Job it fails. (For security I have changed the server name and database names)
12/07/2015 14:30:00,Send DBMail,Error,1,Hellfire,Send DBMail,Run Stored Procedure,,Executed as user: NT AUTHORITY\SYSTEM. Mail queued. [SQLSTATE 01000] (Message 0) Mail queued. [SQLSTATE 01000] (Message 0) Mail queued. [SQLSTATE 01000] (Message 0) Mail queued. [SQLSTATE 01000] (Message 0) 1234 [SQLSTATE 01000] (Message 0) 5678 [SQLSTATE 01000] (Message 0) 11111 [SQLSTATE 01000] (Message 0) erqe [SQLSTATE 01000] (Message 0) ssssss [SQLSTATE 01000] (Message 0) ffff [SQLSTATE 01000] (Message 0) Subquery returned more than 1 value. This is not permitted when the subquery follows =<c/> !=<c/> <<c/> <= <c/> ><c/> >= or when the subquery is used as an expression. The step failed.,00:05:10,16,0,,,,0
The user account NT Authority\System does not seem to be the issue as this account has no problem sending other messages using the same settings. It is just this one procedure that errors out (will provide procedure code shortly)
EDIT # 2
This is the sql syntax I am using (may not make much sense as again I changed sensative data to random garbage details).
Set @fromDate = '12/01/2015' Set @toDate = '12/05/2015' Declare @query varchar(MAX), @name varchar(50), @toDate datetime, @fromDate datetime, @FQN varchar(500) Create Table #problemtable (name varchar(50)) Create Table #PD (name varchar(50), LQ int) INSERT INTO #Datttta (nameName) SELECT DISTINCT(emp) FROM server.database.dbo.one ORDER BY emp ASC DECLARE C1 CURSOR FOR SELECT name FROM #Datttta OPEN C1 FETCH NEXT FROM C1 INTO @name WHILE @@FETCH_STATUS = 0 BEGIN SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name) SET @Query = 'update #Datttta ' + 'set onetwothree = ' + '(select count(*) from '+@FQN+' ' + 'where terminationcategory in (select ' + 'termcat from ' + 'server.database.dbo.twotwo' + 'where emp in ' + '(''' +@name+ ''')) ' + 'and CAST(termdate As Date) between ''' + CONVERT(VARCHAR(10),@fromDate,101) + ''' ' + 'and ''' + CONVERT(VARCHAR(10),@toDate,101) + ''' ' + 'and managersignoff is null) ' Begin Try exec (@query) End Try Begin Catch insert into #problemtable (name) select @name End Catch FETCH NEXT FROM C1 INTO @name END CLOSE C1 DEALLOCATE C1 --Send mail piece is here