SqlClient: OUTPUT clause causes exceptions to not get thrown until read

[benrr101]

This scenario is a bit lengthy, so I'll load it into a gist: https://gist.github.com/benrr101/31628b57f1ab410090237c9018860e05

Basically what this boils down to is that if I .ExecuteReader() on a SqlCommand that has an OUTPUT clause in it, I don't get an exception from the query failing until I call .Read() on the SqlDataReader I gt back.

For instance executing:

INSERT INTO [dbo].[testtable]([small_col]) VALUES ("this value will be truncated")
SELECT * FROM [dbo].[testtable]

SqlDataReader reader = cmd.ExecuteReader(); // Doesn't throw
reader.Read() // DOES throw

But executing:

INSERT INTO [dbo].[testtable]([small_col]) OUTPUT inserted.[small_col] VALUES ("this value will be truncated")

SqlDataReader reader = cmd.ExecuteReader(); // DOES throw
reader.Read(); // Never executed

At this point I'm not sure if this is an expected behavior due to quirks in the TDS protocol or if it's a bug. Nevertheless, this is a bit of weird behavior that has already caused us a couple unexpected issue.

[saurabh500]

@benrr101 Can you repro this on .Net Framework as well>

[benrr101]

Just ran the test on .NET 4.6.1 and it repros, so it's not a regression from .NET Framework to .NET Core. Please let me know if you need any additional information.

[saurabh500]

Thanks @benrr101 This is good for me to get started. I will get in touch in case I need more information.

[divega]

As recently announced in the .NET Blog, focus on new SqlClient features an improvements is moving to the new Microsoft.Data.SqlClient package. For this reason, we are moving this issue to the new repo at https://github.com/dotnet/SqlClient. We will still use https://github.com/dotnet/corefx to track issues on other providers like System.Data.Odbc and System.Data.OleDB, and general ADO.NET and .NET data access issues.

[David-Engel]

As you suspected, this is just how SQL Server and TDS work. Since the command includes output, the server doesn't even send the error message back to the driver until the driver tries to read the output. It behaves this way for all drivers, not just SqlClient.