0

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

EDIT

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
3
  • 5
    The subquery returns more than one value....What is the query? – Mark Sinkinson Dec 8 '15 at 13:51
  • 3
    If you are running this procedure locally with no issue, then you are running it against different data because when it is run on the server more data is being returned. – user507 Dec 8 '15 at 14:02
  • @ShawnMelton - when I run this locally the warning message is produced, but it sill will return the relevant result set. When I run it as a SQL Job the message is never sent. – user2676140 Dec 8 '15 at 14:16
1

As per the error message, more than a single row is being returned in a context where that is not permitted.

Looking at your query, the following line could easily be the culprit:

SET @FQN = (SELECT fqn FROM fulldatabasemapping WHERE rciaem = @name);

You can eliminate that as the cause of the problem by modifying that line to:

SET @FQN = (SELECT TOP(1) fqn FROM fulldatabasemapping WHERE rciaem = @name)
2
  • While the top(1) would resolve the issue there is no way of guaranteeing that the value of fqn in fulldatabase mapping is the same for all values of @name so you could end up with different results on multiple runs. You could add an order by clause to at least attempt to ensure the same value of fqn is returned. Would it no be better to understand why multiple rows are returned when only one was expected ? To me this seems like a misunder standing as to the data stored in fulldatabase mapping or a data problem. Would it not be better to fix the root cause not the issue ? – armitage Dec 8 '15 at 21:55
  • 1
    Agreed, @armitage - my answer is designed only to point out where the problem lies. It should be used solely for debugging purposes. – Hannah Vernon Dec 8 '15 at 22:08
0

It looks like the sendmail is being executed as NT AUTHORITY\SYSTEM. Seeing as this is a local system account, you might be encountering issues, because the BUILTIN\Administrators have not been granted access to the SQL Server in question.

There are two solutions to this issue:

  1. Try running the job step with a different account
  2. Try adding the BUILTIN\Administrators group to your SQL Server instance and provide them with enough privileges to run the sendmail.
1
  • Using this same account works fine for sending other messages using dbmail. I will update my post to include that. – user2676140 Dec 8 '15 at 13:57

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.