I have this stored procedure
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
If I execute it with a SQL query
exec [dbo].[sp_RebuildIndexes]
It works fine.
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 exception:
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?
EDIT:
The problem appears only in the case where Indexes can't be rebuilt with ONLINE=ON
EDIT 2: If I use this sp with a SqlConnection object it works.
Finally here is the fix:
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
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).
So replacing
_context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]");
With
_context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "exec [dbo].[sp_RebuildIndexes]");
And it works!