0

There are many questions regarding this error, but none seem related.

The original exception we get

Der eingehende Tabular Data Stream (TDS) für das RPC-Protokoll (Remote Procedure Call) ist nicht richtig.

0-Tabellenwertparameter (""), Zeile 0, Spalte 0: Der 0xE7-Datentyp hat eine ungültige Daten- oder Metadatenlänge.

My attempt at translating it

The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect

0-TableValuedParameter (""),Row 0, Column 0: The 0xE7-Datatype has an invalid Data- or Metadatalength.

The Version of SqlServer

Microsoft SQL Server 2016 (SP1) (KB3182545) - 13.0.4001.0 (X64) Oct 28 2016 18:17:30 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows Server 2016 Standard 6.3 (Build 14393: ) (Hypervisor)

What is causing this:

We use user defined types to pass table valued parameters. The error is reproducable passing a DataTable with a single row and column containing a String.Empty as a prameter for a type like the following:

CREATE TYPE dbo.para_table_varchar AS TABLE (value nvarchar(10) COLLATE Latin1_General_ci_ai NOT NULL);

The C# Code (.net 4.6.1)

  DataTable table = new DataTable();
  DataColumn column = table.Columns.Add("value", typeof(string));
  column.MaxLength = 0;
  table.Rows.Add(string.Empty);

  SqlParameter para = _cmd.Parameters.Add("@ptable", SqlDbType.Structured, 0);
  para.Value = table;
  para.TypeName ="dbo.para_table_varchar";

The Command looks like this

SELECT * FROM someTable WHERE someColumn IN (SELECT value FROM @ptable)

This only happens on the mentioned SQLServer 2016. About 20 other clients do not have this issue.

While Microsoft covers this issue here, the suggested workarround (setting Size to -1) doesn't seem to work.

CSharpie
  • 9,195
  • 4
  • 44
  • 71
  • Try `DataColumn column = table.Columns.Add("value", typeof(string), 10);`. I don't know if the KB article applies here since it was released in 2009 and mentioned it would be fixed in the next major .NET framework release. – Dan Guzman Nov 17 '17 at 11:24
  • Also, [this article](http://www.dbdelta.com/sql-server-tvp-performance-gotchas/) discusses why one should specify the max string length for `DataTable` columns when the `DataTable` is passed as a TVP. – Dan Guzman Nov 17 '17 at 11:29
  • @DanGuzman sry i forogt to mention, the columns's max length gets set to 0 in this case. – CSharpie Nov 17 '17 at 11:45
  • Right, that's why I suggested specifying the max length of 10 explicitly on the Add method. That might avoid the error. – Dan Guzman Nov 17 '17 at 11:55
  • *Why* are you setting the `MaxLength` to 0, though? It isn't 0, it's 10. TVPs require an exact match for their column types; the type cannot be inferred/converted on the fly like with regular parameters (where you can get away with incorrect lengths, at a cost to performance). This is an engine requirement (or limitation, if you will); you can't get around that on the client size. – Jeroen Mostert Nov 17 '17 at 11:55
  • I also recall some weird code in .NET itself for actually determining the types in the TDS streams when you start streaming rows with differently-sized columns -- that is, the first row determines the actual types used and they can't be changed mid-stream. I ran into this issue once when writing a custom ORM that did its own row streaming (not using `DataTable`). I worked around it by actually getting the TVP definition from the server first with a separate query, rather than relying on the client to get it right. I'm pretty sure the framework doesn't do that. – Jeroen Mostert Nov 17 '17 at 12:02
  • @JeroenMostert i do not believe this is correct, regarding the maximum size. If it were incorrect, this would result in more than one specific SqlServer to throw an error. Also the article Dan Guzman mentioned in his comment proves my point. – CSharpie Nov 17 '17 at 13:40
  • Are you also sending empty strings in the first row on other servers, though? Can you give a specific SQL Server version where your code works, as-is, with the same data? It may well be that `MaxDataLength` isn't actually used by the code to determine the column size in the TVP metadata, that much I will admit to as I haven't delved in that far. The KB article linked to is irrelevant because it specifically deals with an error when the size is set in a certain range (4001 - 8000), which is an old and fixed bug. This may well be a new bug along the same lines, but I actually doubt that. – Jeroen Mostert Nov 17 '17 at 13:49
  • The other obvious thing to check is if the .NET version matters. Whatever the problem is is very likely not with SQL Server (and hence not dependent on the version of that), but with the client code constructing the TDS stream. – Jeroen Mostert Nov 17 '17 at 13:50
  • @JeroenMostert as this is a production system, testing will be difficult. It runs fine on different systems, including an older 2008R2. But other clients have a 2016 where it runs fine. We will wait for the customer to update to the latest version of 2016 and see if this helps. – CSharpie Nov 17 '17 at 19:05
  • Turned out that it did help. – CSharpie Sep 22 '19 at 20:58
  • I had the same problem in a different setup, changing from 0 to 1 solved the problem – Bernardo Dal Corno Aug 24 '20 at 19:27

2 Answers2

0

change the variable type from not null to null then I think you won't get this error

Saikrishna dugyala
  • 16
  • 1
  • 1
    Welcome to Stack Overflow where answering questions is welcomed. However, this answer is extremely similar to several other answers and so it adds no value. New answers to old questions should add new information or give new insights into the problem. – AdrianHHH Sep 02 '21 at 17:29
0

I got this error (#8037, The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Table-valued parameter % has an invalid data length or metadata length) when passing a SqlBinary into a BINARY(32) TVP column, and I hadn't set MaxLength for the column.

Tinged Bubba
  • 1
  • This does not really answer the question. If you have a different question, you can ask it by clicking [Ask Question](https://stackoverflow.com/questions/ask). To get notified when this question gets new answers, you can [follow this question](https://meta.stackexchange.com/q/345661). Once you have enough [reputation](https://stackoverflow.com/help/whats-reputation), you can also [add a bounty](https://stackoverflow.com/help/privileges/set-bounties) to draw more attention to this question. - [From Review](/review/late-answers/34074399) – Daniel Mann Mar 27 '23 at 00:04