SQL Job Failing but not the stored procedure

EDIT 4/18: I want to thank everyone who has answered so far. As a test I have set up a new job which has just one step in it,

EXECUTE p_CallLog_GetAbandonedCallsForCallList

This procedure itself runs just fine and reports no errors or warnings but will not run when executed as part of a job. The error I receive when running the job is:

Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399)   [SQLSTATE 01000] (Error 7312)  OLE DB error trace 
[OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005:   ]. 
[SQLSTATE 01000] (Error 7300).  The step failed.

I have tried changing the run as user selection and every other selection I try results in an error of:

Executed as user: Db2WebCal. Remote access not allowed for Windows NT user activated 
by SETUSER. [SQLSTATE 42000] (Error 7410).  The step failed.

All of the users I have tried do have a local login setup on the linked server.

The p_CallLog_GetAbandonedCallsForCallList procedure is as follows:

CREATE       PROCEDURE [dbo].[p_CallLog_GetAbandonedCallsForCallList] 

AS
BEGIN
    DECLARE @SrvrName varchar(255)
    DECLARE @HoursOld int --only get abandoned call that are fresher than @HoursOld

    SET @HoursOld  =  2 /*was normal default     */
    SET @SrvrName = CAST(ServerProperty('MachineName') as varchar(255))

    CREATE TABLE #tmpAbandonedCalls 
    (
        [ID] INT NULL, 
        StartTime DateTime NULL,
        CallerIDNumber varchar(255) NULL,
        CallerIDCount INT NULL,
        Holdtime INT NULL,
        DIDNumber varchar(20) NULL,
        CustomData varchar(255) NULL,
        FromFirstName varchar(100) NULL,
        FromLastName varchar(100) NULL,
        CallType int NULL
    )

    IF @SrvrName <> 'ROME' BEGIN
        INSERT INTO #tmpAbandonedCalls
        SELECT 
            cl.[ID], 
            cl.StartTime, 
            cl.CallerIDNumber, 
            LastAbandonedCallID.cnt as CallerIDCount,
            cl.HoldTime, 
            right(cl.DIDNumber,10) as DIDNumber, 
            REPLACE(right(left(cl.CustomData,charindex(';',cl.CustomData) - 1), len(left(cl.CustomData,charindex(';',cl.CustomData) - 1)) - charindex('=',cl.CustomData)) , ' NAME','') as CustomData,
            cl.FromFirstName,
            cl.FromLastName,
            2 AS CallType                  -- T_L_CallType obctAbandoned
        FROM 
            [StrataCS.Perceptionist.local].TVDB.dbo.CallLog CL LEFT OUTER JOIN

            /*=============================================
            This derived table lists the CallerID's and the most
            recent Call Log ID for candidate calls
            =============================================*/
            (
                SELECT 
                    CallerIDNumber, Max(ID) as ID, count(*) as cnt
                FROM 
                    [StrataCS.Perceptionist.local].TVDB.dbo.CallLog CL 
                WHERE
                    --Last n days
                    --StartTime >= DATEADD(dd,-@DaysBack,CAST(CONVERT(VARCHAR(10),GETDATE(),112) as DATETIME))

                    --Get calls only from within the last two hours.
                    StartTime >= DATEADD(hh,-2,GETDATE())
                    AND
                    LEFT(Cust开发者_JAVA百科omData,11) = 'CompanyName'
                    AND
                    CHARINDEX('Db2ID', CustomData) > 0
                    AND
                    CallerIDNumber <> ''
                    AND
                    Len(CallerIDNumber) = 10
                    AND 
                    (cl.HoldTime > 0) --0 holdtime is generally an automated call that we will not want to call again
                GROUP BY
                    CallerIDNumber
            ) as LastAbandonedCallID
            ON
            CL.CallerIDNumber = LastAbandonedCallID.CallerIDNumber
        WHERE
            --Last n days
            --StartTime >= DATEADD(dd,-@DaysBack,CAST(CONVERT(VARCHAR(10),GETDATE(),112) as DATETIME))

            --Get calls only from within the last two hours.
            cl.StartTime >= DATEADD(hh,(-1 * @HoursOld),GETDATE())
            AND
            --determine abandoned calls
            CASE
                    WHEN CL.Result IN (0, 3, 11)    THEN    0
                    WHEN CL.Result IN (1, 2)        THEN    1
                    WHEN CL.Result IN (4, 9)        THEN    2
                    WHEN CL.Result = 5              THEN    3
                    WHEN CL.Result = 6              THEN    4
                    WHEN CL.Result = 8              THEN    5
                    WHEN CL.Result = 10             THEN    6
                    WHEN CL.Result = 12             THEN    7
                    WHEN CL.Result = 13             THEN    8
                    WHEN CL.Result = 14             THEN    9

                    ELSE    -CL.Result
                END = 0
            --Calls which have hit the call queue will have both a CompanyName and Db2ID in custom data.
            AND
            LEFT(CustomData,11) = 'CompanyName'
            AND
            CHARINDEX('Db2ID', CustomData) > 0
            AND
            --omit calls with no caller id -- or from IGC 6143847400
            (
                CL.CallerIDNumber <> ''
                --OR CL.CallerIDNumber = '6143847400'
            )
            AND
            --make sure the caller id has 10 digits
            Len(CL.CallerIDNumber) = 10
            AND
            --The abandoned call must be the most recent call from this caller id
            --CL.ID >= isnull(LastAbandonedCallID.ID,-@DaysBack)
            CL.ID >= isnull(LastAbandonedCallID.ID,-1)
            AND 
            (CL.HoldTime > 0) --0 holdtime is generally an automated call that we will not want to call again
        ORDER BY 
            --sort by call time, most recent first.  
            StartTime DESC

        -- Company has opted out of the Abandoned Callback program
        DELETE #tmpAbandonedCalls
        FROM  
            #tmpAbandonedCalls tmp
            INNER JOIN dbo.T_CompanyPhoneSetup cps
                on tmp.DIDNumber = cps.DID
            INNER JOIN T_CompanyAbandonedCallbackOptOut aco
                ON cps.CompanyID = aco.CompanyID
                    AND
                    aco.OptOutIsActive = 1

        --Delete calls that have had a terminating outcome or have been returned within the last 20 minutes
        DELETE #tmpAbandonedCalls 
        FROM
            #tmpAbandonedCalls 
            INNER JOIN 
            (
                SELECT 
                    c.CallLogID
                FROM
                    T_Call c (nolock)
                    INNER JOIN #tmpAbandonedCalls tmp
                        on tmp.ID = c.CallLogID
                    LEFT OUTER JOIN T_L_Need n
                        ON c.NeedID = n.NeedID
                    LEFT OUTER JOIN T_L_Outcome o
                        ON c.OutcomeID = o.OutcomeID
                    LEFT OUTER JOIN dbo.T_L_CallCampaignDetailStatus ccds
                        ON o.CCDetailStatusID = ccds.CCDetailStatusID
                    LEFT OUTER JOIN dbo.T_Company co
                        ON c.CompanyID = co.CompanyID
                    LEFT OUTER JOIN dbo.T_L_ProductLine pl
                        ON co.ProductLineID = pl.ProductLineID
                    LEFT OUTER JOIN T_CompanyAbandonedCallbackOptOut aco
                        ON (c.CompanyID = aco.CompanyID) and (aco.OptOutIsActive = 1)
                GROUP BY
                    c.CallLogID
                HAVING 
                    --Calls that have an outcome that include at least one terminating outcome
                    (SUM(CAST(isnull(ccds.IsTerminal,0) as INT)) > 0)
                    OR
                    (
                        --Calls that have been returned less than 20 minutes ago
                        (SUM(CAST(isnull(ccds.IsTerminal,0) as INT)) = 0)
                        AND
                        GETDATE() <= DATEADD(mi,20,Max(c.EnteredOn))
                    )
                    OR
                    (
                        --Calls for Perceptionist Lite product line
                        (MAX(co.ProductLineID) = 2) -- Perceptionist Lite
                    )

            ) LastCall
            ON
                #tmpAbandonedCalls.[ID] = LastCall.CallLogID

    END

    INSERT INTO T_OutboundCallList
    (TrackingID, Company, CompanyDID, Phone, CallType)
    SELECT
        #tmpAbandonedCalls.[ID], 
        #tmpAbandonedCalls.CustomData,
        #tmpAbandonedCalls.DIDNumber,
        #tmpAbandonedCalls.CallerIDNumber,
        #tmpAbandonedCalls.CallType
    FROM  
        #tmpAbandonedCalls 
    ORDER BY
        StartTime


END
GO

ORIGINAL: I have the following stored procedure that is used to fill a table with values.

PROCEDURE [dbo].[p_OutboundCallList_Create] 

AS

BEGIN

  TRUNCATE TABLE T_OutboundCallList

  EXECUTE p_LeadVendor_GetCallsForCallList  
  EXECUTE p_CallCampaign_GetCallsForCallList    
  EXECUTE p_CallLog_GetAbandonedCallsForCallList    
  EXECUTE p_NoSaleFollowUp_GetCallsForCallList

END

Running this works fine and the table is filled. After creating a job and adding the following step:

EXEC p_OutboundCallList_Create

The job fails with the following error message:

Executed as user: NT AUTHORITY\SYSTEM. Warning: Null value is eliminated 
by an aggregate or other SET operation. [SQLSTATE 01003] (Message 8153)  
Warning: Null value is eliminated by an aggregate or other SET operation. 
[SQLSTATE 01003] (Message 8153)  OLE DB provider 'SQLOLEDB' reported an error. 
[SQLSTATE 42000] (Error 7399)  [SQLSTATE 01000] (Error 7312)  OLE DB error trace 
[OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005:   ]. 
[SQLSTATE 01000] (Error 7300).  The step failed.

If I comment out the line

EXECUTE p_CallLog_GetAbandonedCallsForCallList

..the job runs fine. This stored procedure (p_CallLog_GetAbandonedCallsForCallList) does rely on a linked server and runs fine by itself and also runs fine when I run p_OutboundCallList_Create. It only fails when I run it as part of a job. I have tried running as a different user (sa, benderle, etc.) and always get the same result (failed).

"Null value is eliminated by an aggregate or other SET operation" is a SQL Server warning, which means you won't see it in the output if you run your query and this warning is raised (switch to the "Messages" tab in SQL Server Management Studio to see this).

Although it's a warning, presumably any warnings in a SQL job cause the job to error out. Fix the p_CallLog_GetAbandonedCallsForCallList procedure so it doesn't raise that warning and you job will work as expected.

I woulf be interested in seeing the code in p_CallLog_GetAbandonedCallsForCallList, as it may explain more as to why this is happening for the user NT AUTHORITY\SYSTEM.

The message occurs when you perform an aggregation (e.g. sum(), max(), count() on a data set that has a null in it).

To fix this, you may need to put an ISNULL() around the field in question, or use an INNER JOIN instead of a LEFT or RIGHT JOIN.

To disable warning that throw error in Sql Server Agent tasks. The tasks that are launched from the SQL SERVER AGENT (scheduled), give an error, if there is a warning of the style:

"Null value is eliminated by an aggregate or other SET operation"

This message can be disabled by SET ANSI_WARNING OFF But in some cases, there are sql statements that require this value to be on to avoid errors like:

Heterogeneous queries require the ANSI_NULLS and ANSI_WARNINGS options to be set for the connection. This ensures consistent query semantics. Enable these options and then reissue your query

Therefore, there is no other solution than to prevent SQL SERVER AGENT from considering it an error.

This can be achieved by modifying the values ​​in the REGISTRY (REGEDIT)

Modify the entries, depending on your version of SQL SERVER:

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.MSSQLSERVER\SQLServerAgent

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL11.SQL2012\SQLServerAgent

Add as 'no error' the message id you want to disable: 7405