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