调用exec sp并在SQL Azure上使用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.

你有什么线索出现​​问题吗？

Do you have any clue on what is going wrong?

编辑：

只有在索引无法使用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]");

它的工作原理

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