stored-procedures - 从 SQL 代理作业调用时,工作存储过程失败
问题描述
我有一个在 SQL Server Management Studio 中运行良好且没有错误的存储过程。但是,当相同的存储过程作为 SQL 代理作业的一个步骤执行时,它会以以下方式终止:
错误 3930:当前事务无法提交,并且无法支持写入日志文件的操作。回滚事务。
存储过程位于名为[Billing]
. 它使用的大多数表也在[Billing]
模式中。
主存储过程开始一个数据库事务。由主存储过程调用的存储过程在继承的事务上完成所有工作。主存储过程负责提交或回滚事务。
运行 SQL 代理作业步骤的数据库用户不是 Sysadmin 角色,也不是 dbo。它属于 db_datareader 和 db_datawriter 数据库角色,并且在[Billing]
模式中被赋予了 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
解决方案
正如@Lukasz Szozda 在他对我的问题的评论中暗示的那样,问题是当 SQL 代理作业执行BCP.EXE
命令时,它在 SQL 代理使用的服务帐户下运行,对我来说这是相当严格的“本地系统”帐户。在这一点上,对我来说很明显必须使用代理帐户。所以我在 下创建了一个代理Operating System (CmdExec)
,这是唯一有意义的选择。
我回到工作步骤将其更改为使用代理,但随后注意到在其当前类型中Transact-SQL script (TSQL)
,无法分配代理帐户。
在尝试了几件事之后,最终决定将作业步骤中的 TSQL 语句放入一个新的存储过程中,然后从 SQL 命令行可执行文件中调用该存储过程SQLCMD.EXE
。然后我将作业步骤类型从 更改Transact-SQL script (TSQL)
为Operating System (CmdExec)
。然后我可以将该Run As
字段设置为我之前创建的代理。我指定了以CMD.EXE /c SQLCMD.EXE -S [ServerName] -Q "EXEC [NewProcedureName] [parameters]"
.
如果您对我为什么在SQLCMD.EXE
下运行感到好奇CMD.EXE
,那是因为新存储过程的参数之一是特定格式 ( '%date:~4,10%'
) 的当前日期,SQL Server 作业执行环境不支持,但CMD.EXE
当然可以。
总的来说,我认为这比我预期的要付出更多的努力。
推荐阅读
- directory - Linux下的数据丢失
- amazon-web-services - 只让另一个微服务可以访问一个微服务
- css - Autoprefixer 在 gatsby 项目中做的很少
- javascript - 页面加载后如何使用javascript修改html
- angular - 当我在 FormBuilder 中设置字段时,为什么要在 DOM 中使用 `required`?
- java - 当我们声明一个向量时
> r,我们可以通过 r[0].insert(10) 轻松地将元素插入到集合中。同样,它是如何在java中完成的? - c++ - 如何逃脱 switch 和 while(1) 循环?
- python - Python:在同一个数据表上具有多个过滤器的仪表板
- for-loop - 由于不相关的表索引分配,Lua '尝试调用 for..in 附近的数字值' 错误 - 为什么?
- javascript - React setState 不会触发 Contexts 的重新渲染