Stored procedure that works when calling exec sp and throws from EF6 to SQL Azure
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 SQL query
exec [dbo].[sp_RebuildIndexes]
It works great.
Now, calling it from EF6, this code throws 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]");
An 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 information on what is going wrong?
EDIT:
The problem only occurs if the indexes cannot be rebuilt with ONLINE = ON
EDIT 2: If I use this sp with SqlConnection object it works.
source to share
Finally, this fix:
I suspected there was a transaction problem, and after some research, there is a problem here: If you check here http://msdn.microsoft.com/en-us/data/dn456843
Starting with EF6 Database.ExecuteSqlCommand (), by default, a command in a transaction will be completed if it was not already present. There are overloads of this method that allow you to override this behavior if you like. Also in EF6, executing stored procedures included in the model through the API, such as ObjectContext.ExecuteFunction (), does the same (except that the default behavior cannot be overridden at this time).
So, replacing
_context.Database.ExecuteSqlCommand("exec [dbo].[sp_RebuildIndexes]");
FROM
_context.Database.ExecuteSqlCommand(TransactionalBehavior.DoNotEnsureTransaction, "exec [dbo].[sp_RebuildIndexes]");
And it works!
source to share