存储过程在 SQL Azure 上调用 exec sp 并使用 EF6 抛出时工作 [英] Stored procedure working when calling exec sp and throws with EF6 on SQL Azure

问题描述

我有这个存储过程

CREATE PROCEDURE [dbo].[sp_RebuildIndexes]
AS
BEGIN
  DECLARE @TableName varchar(255)

  DECLARE TableCursor CURSOR FOR (SELECT
    '[' + IST.TABLE_SCHEMA + '].[' + IST.table_name + ']' AS [TableName]
  FROM INFORMATION_SCHEMA.TABLES IST
  WHERE IST.TABLE_TYPE = 'BASE TABLE')

  OPEN
  TableCursor
  FETCH NEXT FROM TableCursor INTO @TableName
  WHILE @@fetch_status = 0

  BEGIN
    PRINT ('Rebuilding Indexes on ' + @TableName)
  BEGIN TRY
    EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD with (ONLINE=ON)')
  END TRY
  BEGIN CATCH
    PRINT ('Cannot do rebuild with Online=On option, taking table ' + @TableName + ' down for doing rebuild')
    EXEC ('ALTER INDEX ALL ON ' + @TableName + ' REBUILD')
  END CATCH
    FETCH NEXT FROM TableCursor INTO @TableName
  END

  CLOSE TableCursor
  DEALLOCATE TableCursor
END

如果我使用 SQL 查询执行它

If I execute it with a SQL query

exec [dbo].[sp_RebuildIndexes]

效果很好.

现在使用此代码从 EF6 调用它会在 SQL Azure 上引发但在 localdb 上有效:

Now calling it from EF6 with this code throws on SQL Azure but works on localdb:

var sqlConnection = (SqlConnection) _context.Database.Connection;
sqlConnection.InfoMessage += (s, m) => messages = m.Message;
_context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]");

例外:

The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.
Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.
[SqlException (0x80131904): The current transaction cannot be committed and cannot support operations that write to the log file. Roll back the transaction.

你有什么线索吗?

仅在使用 ONLINE=ON 无法重建索引的情况下才会出现此问题

The problem appears only in the case where Indexes can't be rebuilt with ONLINE=ON

编辑 2:如果我将此 sp 与 SqlConnection 对象一起使用，它就可以工作.

EDIT 2: If I use this sp with a SqlConnection object it works.

推荐答案

终于解决了:

我怀疑是交易问题，经过进一步调查，问题出在:如果您在这里查看 http://msdn.microsoft.com/en-us/data/dn456843

I was suspecting a transaction issue, and after a few more investigation here is the problem: If you check here http://msdn.microsoft.com/en-us/data/dn456843

默认情况下，从 EF6 Database.ExecuteSqlCommand() 开始会将命令包装在事务中(如果尚不存在).如果您愿意，此方法的重载允许您覆盖此行为.此外，在 EF6 中，通过 API(例如 ObjectContext.ExecuteFunction())执行模型中包含的存储过程也是如此(除了目前无法覆盖默认行为).

Starting with EF6 Database.ExecuteSqlCommand() by default will wrap the command in a transaction if one was not already present. There are overloads of this method that allow you to override this behavior if you wish. Also in EF6 execution of stored procedures included in the model through APIs such as ObjectContext.ExecuteFunction() does the same (except that the default behavior cannot at the moment be overridden).

所以替换

_context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]");

有

_context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "exec [dbo].[sp_RebuildIndexes]");

而且它有效！

这篇关于存储过程在 SQL Azure 上调用 exec sp 并使用 EF6 抛出时工作的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！