Repository navigation
5.2.0 broke DATETIMEOFFSET(1..4) in TVPs #2423
Description
From 5.2.0, an exception is thrown when using DATETIMEOFFSET(n) in a TVP if n is 1, 2, 3, or 4.
Repro code:
open System
open System.Data
open Microsoft.Data.SqlClient
open Microsoft.Data.SqlClient.Server
type DateTimeOffsetList(datetimeoffset: DateTimeOffset) as this =
inherit SqlDataRecord([| SqlMetaData("datetimeoffset", SqlDbType.DateTimeOffset, 0uy, 1uy) |])
do this.SetValues(datetimeoffset) |> ignore
let param =
SqlParameter(
"@params",
SqlDbType.Structured,
TypeName = "dbo.DateTimeOffsetList",
Value = [ DateTimeOffsetList(datetimeoffset = DateTimeOffset(2000, 1, 1, 0, 0, 0, TimeSpan.Zero)) ]
)
let conn = new SqlConnection("Data Source=.;Initial Catalog=TvpRepro;Integrated Security=True;Encrypt=False")
conn.Open()
let cmd = conn.CreateCommand()
cmd.CommandText <- "SELECT * FROM @params"
cmd.Parameters.Add(param) |> ignore
cmd.ExecuteReader() |> ignoreRunning this as-is requires a database TvpRepro with the following TVP:
CREATE TYPE dbo.DateTimeOffsetList AS TABLE
(
[Value] DATETIMEOFFSET(1) NOT NULL
)When run, it produces this exception:
Unhandled exception. Microsoft.Data.SqlClient.SqlException (0x80131904): The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter 0 (""), row 1, column 1: The supplied value is not a valid instance of data type datetimeoffset. Check the source data for invalid values. An example of an invalid value is data of numeric type with scale greater than precision.
The statement has been terminated.
at Microsoft.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action`1 wrapCloseInAction)
at Microsoft.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj, SqlCommand command, Boolean callerHasConnectionLock, Boolean asyncClose)
at Microsoft.Data.SqlClient.TdsParser.TryRun(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj, Boolean& dataReady)
at Microsoft.Data.SqlClient.SqlDataReader.TryConsumeMetaData()
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boo
at Microsoft.Data.SqlClient.SqlDataReader.get_MetaData()
at Microsoft.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString, Boolean isInternal, Boolean forDescribeParameterEncryption, Boolean shouldCacheForAlwaysEncrypted)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean isAsync, Int32 timeout, Task& task, Boolean asyncWrite, Boolean inRetry, SqlDataReader ds, Boolean describeParameterEncryptionRequest)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, TaskCompletionSource`1 completion, Int32 timeout, Task& task, Boolean& usedCache, Boolean asyncWrite, Boolean inRetry, String method)
at Microsoft.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior)
at Microsoft.Data.SqlClient.SqlCommand.ExecuteReader()
at <StartupCode$TvpRepro>.$Program.main@() in C:\Users\cmeer\Source\Repos\TvpRepro\TvpRepro\Program.fs:line 23
ClientConnectionId:fa0131e2-6148-4429-b44e-8d90ecdeba22
Error Number:8043,State:1,Class:16
Strictly speaking, this only happens if the scale set in SqlMetaData is 1, 2, 3, or 4. It seems to be unrelated to the number used in the SQL type definition (though I assume they should always match).
This error does not occur before 5.2.0.
Further technical details
Microsoft.Data.SqlClient 5.2.0
.NET 8
SQL Server Developer 15.0.2104.1
Windows 11
Activity
Revert "Update Microsoft.Data.SqlClient from 5.1.4 to 5.2.0"
DavoudEshtehari commentedon Mar 26, 2024
Thank you, we'll check it out.
arellegue commentedon Apr 4, 2024
@cmeeren. Just needed to clarify the repro. You mentioned in the repro that "Running this as-is requires a database TvpRepro with the following TVP:"
CREATE TYPE dbo.DateTimeOffsetList AS TABLE
(
[Value] DATETIMEOFFSET(1) NOT NULL
)
However, in the command text "SELECT * FROM @params " it seems it is querying a user defined table type and not an actual instance of a table. Do you actually intend to query an actual instance of a table using dbo.DateTimeOffsetList type?
cmeeren commentedon Apr 4, 2024
Not sure what you mean by "instance of a table". I don't mention a table anywhere in my post. If you are referring to the name
TvpRepro, note that I mention this is the name of the database, not a table. (It's used in the connection string in the repro code, and it is of course trivial to replace.)Was that clarifying?
arellegue commentedon Apr 4, 2024
@cmeeren. Using version 5.1 displayed the parameter value without issues.
The root cause of regression is being investigated.
arellegue commentedon Apr 8, 2024
The issue was introduced in enhancement #2170.
@saitama951, do you have a unit test for the above enhancement you can share? If not, would you mind testing locally when the fix on this issue is done to ensure the enhancement is not affected by the change?
saitama951 commentedon Apr 9, 2024
@arellegue I didn't have a specific unit test for this enhancement. apparently, I tested this against the manual and functional test cases present in SqlClient (to see nothing breaks with x86 and s390x). Sure I will test it out.
saitama951 commentedon Apr 9, 2024
I think there are different lengths for SqlDbType and probably the extra bytes written to the stream might be the cause ( in some cases)?
I would suggest we use the old way.