代码之家  ›  专栏  ›  技术社区  ›  STLDev

从SQL代理作业调用时,工作存储过程失败

  •  0
  • STLDev  · 技术社区  · 8 年前

    错误3930:

    [Billing] . 它使用的大多数表也在 [帐单]

    运行SQL代理作业步骤的数据库用户既不是Sysadmin角色,也不是dbo角色。它属于db\u datareader和db\u datawriter数据库角色,并在中被授予Delete、Execute、Insert、References、Select和Update等权限 [帐单] 架构。

    CREATE PROCEDURE [Billing].[GenerateBillXml]
      @pUseProductionPsSystem bit
      ,@pPeriodYear int
      ,@pPeriodMonthNumber int
      ,@pDocumentTypeName varchar(20)
      ,@pUser varchar(100)
      ,@pFilePath varchar(500)
      ,@pExportDateTime datetime2(7)
      ,@pResultCode int = 0 OUTPUT
    AS
    BEGIN
        SET NOCOUNT ON;
    
      SET @pResultCode = 0;
    
      DECLARE @transactionBegun bit = 0
              ,@RC int = 0
              ,@processDateTimeUtc datetime2(7) = GETUTCDATE()
              ,@billGenerationId int
              ,@PsJobSuffix char(2)
              ,@periodDate date
              ,@periodDateString char(6)
              ,@PsBaseJobNumber char(6)
              ,@okayToRun int = 0
              ,@msg varchar(500);
    
      BEGIN TRY
        /* calculate the period date */
        SET @periodDate = CONVERT(date,CAST(@pPeriodMonthNumber as varchar) + '/01/' + CAST(@pPeriodYear as varchar))
        SET @periodDateString = CONVERT(varchar, @periodDate, 12); -- yyMMdd
    
        /* retrieve the job suffix */
        SELECT @PsJobSuffix = CAST([PsJobSuffix] as char(2))
          FROM [dbo].[DocumentType] dt
          WHERE [Name] like @pDocumentTypeName;
    
        /* build the base job number */
        SET @PsBaseJobNumber = LEFT(@periodDateString, 4) + @PsJobSuffix
    
        /*
         * We've made it past the input check - record the fact that we're generating a bill
         */
        INSERT [Billing].[BillGeneration] (
          [PsBaseJobNumber], [Status], [RunBy], [ProcessDateTimeUtc]
        ) VALUES (
          @PsBaseJobNumber, 'Running', @pUser, @processDateTimeUtc
        );
    
        IF @@ROWCOUNT = 1
          SET @billGenerationId = SCOPE_IDENTITY();
    
        EXECUTE @RC = [Billing].[_0_OkayToGenerateBill]
          @PsBaseJobNumber
          ,@okayToRun OUTPUT
          ,@pResultCode OUTPUT;
    
        IF @pResultCode = 0  
        BEGIN
          -- called stored procedure completed without error
          IF @okayToRun = -1 -- this bill has already been generated
          BEGIN
            SET @msg = 'The billing for job ' + CAST(@PsBaseJobNumber as varchar) + ' has already been produced.';
            RAISERROR(@msg, 16, 1)
          END
    
          IF @okayToRun = -2 -- too early to run billing for this period
          BEGIN
            SET @msg = 'It is too early to generate billing for job ' + CAST(@PsBaseJobNumber as varchar) + '.';
            RAISERROR(@msg, 16, 1)
          END
    
          IF @okayToRun <> 1 -- unknown error...
          BEGIN
            SET @msg = 'Unknown error occured while determining whether okay to generate bill for job ' + CAST(@PsBaseJobNumber as varchar) + '.';
            RAISERROR(@msg, 16, 1)
          END
        END
        ELSE
        BEGIN
          SET @msg = 'Unknown failure in sub-stored procedure [Billing].[_0_OkayToRun]() for job ' + CAST(@PsBaseJobNumber as varchar) + '.';
          RAISERROR(@msg, 16, 1)  -- will cause branch to CATCH
        END
    
        /* Okay to generate bill */
    
        /* If not in a transaction, begin one */
        IF @@TRANCOUNT = 0
        BEGIN
          BEGIN TRANSACTION
          SET @transactionBegun = 1;
        END
    
        EXECUTE @RC = [Billing].[_1_GeneratePsPreBillData] 
           @PsBaseJobNumber
          ,@pUser
          ,@pResultCode OUTPUT;
    
        IF @pResultCode = 0
        BEGIN
          -- stored proced ran to successful completion
          EXECUTE @RC = [Billing].[_2_GetBillingDataForXmlGeneration]
             @pUseProductionPsSystem
            ,@PsBaseJobNumber
            ,@pResultCode OUTPUT;
    
          IF @pResultCode = 0
          BEGIN
            -- stored proced ran to successful completion
            IF @transactionBegun = 1
              -- all table data has been created/updated
              COMMIT TRANSACTION
    
            -- Output XML bill to file
            EXECUTE @RC = [Billing].[_3_GenerateBillingXmlFilesForPsJob] 
               @PsBaseJobNumber
              ,@pFilePath
              ,@pExportDateTime
              ,@pResultCode OUTPUT;
    
            IF @pResultCode <> 0
            BEGIN
              -- called stored procedure failed
              SET @msg = '[Billing].[_3_GenerateBillingXmlFilesForPsJob]() failed for job ' + CAST(@PsBaseJobNumber as varchar);
              RAISERROR(@msg, 16, 1)  -- will cause branch to CATCH
            END
          END
          ELSE
          BEGIN
            -- called stored procedure failed
            SET @msg = '[Billing].[_2_GetBillingDataForXmlGeneration]() failed for job ' + CAST(@PsBaseJobNumber as varchar);
            RAISERROR(@msg, 16, 1)  -- will cause branch to CATCH
          END
        END
        ELSE
        BEGIN
            -- called stored procedure failed
          SET @msg = '[Billing].[_1_GeneratePsPreBillData]() failed for job ' + CAST(@PsBaseJobNumber as varchar);
          RAISERROR(@msg, 16, 1)  -- will cause branch to CATCH
        END
    
        -- bill generation was successful
        IF @billGenerationId IS NOT NULL
          UPDATE [Billing].[BillGeneration]
            SET [Status] = 'Successful', [ProcessEndDateTimeUtc] = GETUTCDATE()
            WHERE [Id] = @billGenerationId;
    
      END TRY
      BEGIN CATCH
        -- rollback transaction if we started one
        IF @transactionBegun = 1
          ROLLBACK TRANSACTION
    
        -- record the error
        INSERT [Billing].[BillGenerationError] (
          [DateTime], [Object], [ErrorNumber], [ErrorMessage]
        ) VALUES (
          GETDATE(), OBJECT_NAME(@@PROCID), ERROR_NUMBER(), ERROR_MESSAGE()
        );
    
        -- bill generation failed
        IF @billGenerationId IS NOT NULL
          UPDATE [Billing].[BillGeneration]
            SET [Status] = 'Failed'
                ,[Note] = ERROR_MESSAGE()
                ,[ProcessEndDateTimeUtc] = GETUTCDATE()
            WHERE [Id] = @billGenerationId;
    
        SELECT ERROR_NUMBER() as ErrorNumber;
        SELECT ERROR_MESSAGE() as ErrorMessage;
        SET @pResultCode = 1
      END CATCH
    END
    
    1 回复  |  直到 8 年前
        1
  •  0
  •   STLDev    8 年前

    正如@Lukasz Szozda在对我的问题的一个评论中所暗示的,问题是当SQL代理作业执行 BCP.EXE 命令,它在SQL代理使用的服务帐户下运行,对我来说,该帐户是相当严格的“本地系统”帐户。在这一点上,我很明显,必须使用代理帐户。所以我在下面创建了一个代理 Operating System (CmdExec) ,这是唯一有意义的选择。

    Transact-SQL script (TSQL) ,无法分配代理帐户。

    SQLCMD.EXE . 然后我将作业步骤类型从 Transact-SQL脚本(TSQL) 到 操作系统(CmdExec) . 然后我可以设置 Run As 我之前创建的代理的字段。我指定了作为运行的命令 CMD.EXE /c SQLCMD.EXE -S [ServerName] -Q "EXEC [NewProcedureName] [parameters]" .

    SQLCMD.EXE文件 CMD.EXE '%date:~4,10%' ),SQL Server作业执行环境不支持,但 命令行 当然有。