ArgumentException on maxLength for TVP nvarchar columns

[NickDarvey]

Description

I get a Specified length '8000' is out of range. (Parameter 'maxLength') when trying to use a TVP with a column of NVARCHAR(4000).

Repro steps

Please provide the steps required to reproduce the problem

1. Create some tables like

CREATE TABLE [dbo].[Messages]
(
	[ChatId] INT NOT NULL
	, [Id] BIGINT IDENTITY(1,1) NOT NULL
	, [Body] NVARCHAR(4000) NOT NULL
)

1. Create a TVP like

CREATE TYPE [dbo].[ChatMessages] AS TABLE
(
	[ChatId] INT NOT NULL
	, [Body] NVARCHAR(4000) NOT NULL
)
GO

CREATE PROCEDURE dbo.usp_ChatMessagesInsert
   @Messages [dbo].[ChatMessages] READONLY
AS
BEGIN
	SET NOCOUNT ON
	INSERT INTO [dbo].[Messages] (ChatId, Body)
	OUTPUT INSERTED.Id
	SELECT msg.ChatId, msg.Body
	FROM @Messages AS msg
END

1. Insert some messages like

type InsertChatMessage = SqlCommandProvider<"exec usp_ChatMessagesInsert @messages", YourCompileTimeConnectionString>

use cmd = new InsertChatMessage(yourRuntimeConnectionString)
let messages = [ {| ChatId = 1; Body = "Yo" |} ]
let records = messages |> Seq.map (fun m ->
        InsertChatMessage.ChatMessages(m.ChatId, m.Body))
    cmd.Execute(records)

Expected behavior

Messages are inserted.

Actual behavior

Specified length '8000' is out of range. (Parameter 'maxLength')
Name: "Body"
dbType: NVarChar
maxLength: 8000
useServerDefault: false
isUniqueKey: false
columnSortOrder: -1
ILocale: 0

   at Microsoft.SqlServer.Server.SqlMetaData.Construct(String name, SqlDbType dbType, Int64 maxLength, Boolean useServerDefault, Boolean isUniqueKey, SortOrder columnSortOrder, Int32 sortOrdinal) in E:\A\_work\65\s\corefx\src\System.Data.SqlClient\src\System\Data\Sql\SqlMetaData.cs:line 493
   at Microsoft.SqlServer.Server.SqlMetaData..ctor(String name, SqlDbType dbType, Int64 maxLength) in E:\A\_work\65\s\corefx\src\System.Data.SqlClient\src\System\Data\Sql\SqlMetaData.cs:line 76
   at Mtime.Messaging.Persistence.records@99.Invoke(ChatMessage m) in C:\Users\me\repos\app\Persistence.fs:line 102

Known workarounds

Drop the column size to 2000.

Related information

• FSharp.Data.SqlClient 2.0.5
• Microsoft SQL Server Express Edition (64-bit)
• Version 13
• SQL_Latin1_General_CP1_CI_AS
• Microsoft Windows NT 6.3 (18362)

It's throwing on this line in SqlClient

if ((maxLength > x_lServerMaxUnicode || maxLength < 0) && maxLength != Max)
                    throw ...

and x_lServerMaxUnicode is set to 4000.

It seems like there's a mismatch in talking bytes verses characters. If I understand correctly, nvarchar(n) accepts a max of 4,000 which refers to characters/'byte-pairs' or 8,000 bytes. It looks like the maxLength provided to SqlClient's Construct should be referring to byte-pairs (because of the maximum declared in the x_lServerMaxUnicode constant, but is actually referring to bytes.

If this is actually the problem, I'm have no idea where it's happening. Perhaps it's how the metadata is being queried?

[smoothdeveloper]

@NickDarvey could you investigate a bit with the procedures mentionned at the bottom of http://fsprojects.github.io/FSharp.Data.SqlClient/

sys.sp_describe_undeclared_parameters and sys.sp_describe_first_result_set, they should bring info about column and paramter types.

Also, can you try using nvarchar(max) to see if it has any effect / possible work around your issue?

[NickDarvey]

@smoothdeveloper thanks for the pointer.
It looks like sp_describe_first_result_set does declare max_length as specifically being the length is in bytes.
sp_describe_undeclared_parameters has a suggested_max_length which refers to sys.column which also says the max_length is in bytes.

It seems like INFORMATION_SCHEMA.COLUMNS would have the right information in the column CHARACTER_MAXIMUM_LENGTH, but that might be a tonne of work to start using it in this type provider, if it's even possible. (I'm am both unfamiliar with SQL Server and Type Providers I'm afraid 😅)

nvarchar(max) does work and is a workaround for me, thank you.