EF bug? Stored proc called from EF returns Uncommittable error

Question

I have a stored procedure that I execute in SSMS and it works fine. No Issues.  The sproc contains an insert to a table via an existing linked server.  For example:

    USE MyDevDb
    GO

    ...

    BEGIN

    -- declare and init some vars etc...
    ...

    BEGIN TRY
        INSERT INTO [LinkedSQLServer].[MyDb].[MySchema].[MyTable] (Col1, Col2, Col3)
        SELECT Col1, Col2, Col3 FROM MySchema.MyTable WHERE Col1 = 'XYZ'

    END TRY

    BEGIN CATCH
      -- something goes here
    END CATCH
    END

In my application I am using entity framework(6.1.1) and have an edmx that I have updated to include the sproc.  When I run the application I get the error

    -- Executing at 1/15/2015 6:02:15 AM -05:00
    -- Failed in 94 ms with error: Uncommittable transaction is detected at the end of the batch. The transaction is rolled back.

I have triple checked and did a schema compare.  All checks out.  The only thing I can think of is there is something on the other server I am linked to but I cannot identify what. 

I have other sprocs that are included for my app and they all work fine.  However the other sprocs do not contain any calls via a linked server... just this one sproc.

What I am trying to find out is what else can I check for besides schemas, keys, indexes, etc...?

thanks!

This was my original post in StackOverflow... One additional bit of information.   I rolled back EF from 6.1.1 to 5.0.0 and 6.0.0 and it worked.  So my question becomes is this a known bug?  Is there a work around?

Answer 1

I think it has nothing to do with EF.

http://technet.microsoft.com/en-us/library/ms179296(v=sql.105).aspx

<copied>

When a batch finishes running, the Database Engine rolls back any active uncommittable transactions. If no error message was sent when the transaction entered an uncommittable state, when the batch finishes, an error message will be sent to the client application that indicates an uncommittable transaction was detected and rolled back For more information about distributed transactions, see Distributed Transactions (Database Engine).

<end>

You can so go to the EF backdoor and execute the sproc using stright-up ADO.NET and SQL Command objects.

http://blogs.msdn.com/b/alexj/archive/2009/11/07/tip-41-how-to-execute-t-sql-directly-against-the-database.aspx

Answer 2

Yeah I read the first link before a couple of times.  Not much help.  The second link goes against our policy of inline sql code...

As I mentioned, when I rolled back EF from 6.1.1 to 5.0.0 it worked.   It runs in Management Studio without problem or errors.   As it turned out  I ended up using ado.net for this particular need.  Which took me all of 2 minutes to do...

I still think this is an issue with EF as I put all kinds of code to check and report any errors and nothing came back during execution.  I also set up sql profiler and nothing came back to indicate a problem. Unfortunately I cant make a career out of digging in on this so...

Thanks for the reply ....

Answer 3

   

The second link goes against our policy of inline sql code...

How can that be? You can execute a stored procedure using the EF backdoor just like you can execute in-line T-SQL using ADO.NET that'ss being shown in the example. It makes no diffference if executing in-line T-SQL or a sproc is being called.

Answer 4

EF6 will wrap the proc call in a transaction, which is probably causing a failed DTC escalation when calling the linked server.

See the  Enable Promotion of Distributed Transactions Linked Server configuration settings.

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 5

It makes a big difference when the client/customer says they do not want inline SQL code in the source. 

Answer 6

But a stored procedure call would not normally be considered "inline SQL".

David

---

David http://blogs.msdn.com/b/dbrowne/

Answer 7

It makes a big difference when the client/customer says they do not want inline SQL code in the source. 

I know what inline T-TSQL is about. I have done plenty of it over the years. I don't think you nor your client/customer knows what inline T-SQL is about. The calling of a stored procedure where the T-SQL is executed in a stored procedure called from code is not inline T-SQL.

Answer 8

It makes a big difference when the client/customer says they do not want inline SQL code in the source. 

I know what inline T-TSQL is about. I have done plenty of it over the years. I don't think you nor your client/customer knows what inline T-SQL is about. The calling of a stored procedure where the T-SQL is executed in a stored procedure called from code is not inline T-SQL.

You're confused. I was referring more to actually having TSQL statements in the source.  in-line for me means I have a var sql = "Select * from myTable".   Something my client doesn't want to see in the source code.   A great deal of data related functionality is handled via stored procs called through EF.   So hopefully this will clarify differences in terminology. 

Answer 9

You're confused. I was referring more to actually having TSQL statements in the source.  in-line for me means I have a var sql = "Select * from myTable".   Something my client doesn't want to see in the source code.   A great deal of data related functionality is handled via stored procs called through EF.   So hopefully this will clarify differences in terminology. 

Your head is ten bricks hard.  And in no way is calling the sproc through the EF backdoor is even in the ballpark of using inline SQL, inline T-SQL or anything else you want to make up and call it. You are scared to run a sproc from code behind file using ADO.NET when EF is doing the same thing no matter how the sproc is called via the EF backdoor, EF calling the sproc without the backdoor or EF engine using the internal MS SQL Server sproc to submit generated T-SQL from a Linq query.

You make no sense to me -- none --  in your inability to call a cow a cow or a horse a horse.