I have a stored procedure that runs fine with no errors inside SQL Server Management Studio. However, when the same stored procedure is executed as a step of a SQL Agent Job, it terminates with:
Error 3930: The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The stored procedure lives in a schema named [Billing]. Most of the tables it uses are also in the [Billing] schema.
The main stored procedure begins a database transaction. The stored procedures called by the main stored procedure do all of their work on that inherited transaction. The main stored procedure is responsible for committing or rolling back the transaction.
The database User running the SQL Agent Job Step is not in the Sysadmin role, nor is it dbo. It belongs to the db_datareader and db_datawriter database roles, and has been given Delete, Execute, Insert, References, Select, and Update, permissions in the [Billing] schema.
Here is the main stored procedure:
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
As @Lukasz Szozda hinted in one of his comments to my question, the issue was that when the SQL Agent job executed the BCP.EXE command, it was running under the service account used by SQL Agent, which for me is the fairly restrictive "Local System" account. At this point it became obvious to me that a Proxy account had to be used. So I created a Proxy under Operating System (CmdExec), which was the only choice that made sense.
I went back to the job step to change it to use the Proxy, but then noticed that in its current type of Transact-SQL script (TSQL), there is no way to assign a Proxy account.
After trying a few things, finally decided to put the TSQL statements that were in the job step into a new stored procedure, and then call that stored procedure from the SQL command-line executable SQLCMD.EXE. I then changed the job step type from Transact-SQL script (TSQL) to Operating System (CmdExec). I could then set the Run As field to the Proxy I created earlier. I specified the command to run as CMD.EXE /c SQLCMD.EXE -S [ServerName] -Q "EXEC [NewProcedureName] [parameters]".
If you're curious as to why I'm running SQLCMD.EXE under CMD.EXE, it's because one of the parameters to the new stored procedure was the current date in a particular format ('%date:~4,10%'), which the SQL Server job execution environment didn't support, but which CMD.EXE certainly does.
Overall, I think this took a bit more effort than I expected.