SQL作业失败，但不是存储过程

| 编辑4/18：我要感谢到目前为止已经回答的每个人。作为测试，我建立了一个仅一步之遥的新工作，

EXECUTE p_CallLog_GetAbandonedCallsForCallList

此过程本身运行良好，不会报告任何错误或警告，但是在作为作业的一部分执行时将不会运行。运行作业时收到的错误是：

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.

我尝试将运行方式更改为用户选择，而我尝试的所有其他选择均导致以下错误：

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

我尝试过的所有用户在链接服务器上都具有本地登录设置。 p_CallLog_GetAbandonedCallsForCallList过程如下：

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(CustomData,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

原版的：我有以下存储过程，用于用值填充表。

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

运行此工作正常，表已满。创建作业并添加以下步骤后：

EXEC p_OutboundCallList_Create

作业失败，并显示以下错误消息：

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.

如果我注释掉

EXECUTE p_CallLog_GetAbandonedCallsForCallList

..工作顺利。此存储过程（p_CallLog_GetAbandonedCallsForCallList）确实依赖于链接服务器，并且本身运行良好，并且在我运行p_OutboundCallList_Create时也运行良好。仅当我将其作为作业的一部分运行时，它才会失败。我尝试以其他用户（sa，benderlele等）的身份运行，并且始终获得相同的结果（失败）。

已邀请:

2 个回复

癸痊醒

\“通过合计或其他SET操作消除了空值\”是SQL Server警告，这意味着如果您运行查询并且引发此警告，则您将不会在输出中看到它（切换到\“ Messages SQL Server Management Studio中的\“标签以查看此信息）。尽管这是一个警告，但SQL作业中的任何警告都可能导致该作业出错。修正p_CallLog_GetAbandonedCallsForCallList程序，这样它不会发出警告，您的工作将按预期进行。

迪擅哨乘傅

我对查看ѭ8中的代码很感兴趣，因为它可能会解释为什么用户NT AUTHORITY \\ SYSTEM会发生这种情况。当您执行汇总时（例如，对其中包含空值的数据集进行“ 10”，“ 11”，“ 12”）时，会出现此消息。要解决此问题，您可能需要在相关字段周围放置一个ISNULL（），或使用INNER JOIN而不是LEFT或RIGHT JOIN。

要回复问题请先登录或注册

SQL作业失败，但不是存储过程

2 个回复

发起人

sql

linked_server

stored_procedures

jobs

问题状态

SQL作业失败，但不是存储过程

与内容相关的链接

2 个回复

发起人

sql

linked_server

stored_procedures

jobs

问题状态