1
Vote

MERGE Destination fails to create TVP

description

The MERGE Destination failed during the create TVP step when the source/target contains an nvarchar(max) column.

Error:
Incorrect syntax near ')'.

Sample TVP create:

create type TVPType8b78e67fe4e746c4993ff76d8496e31b as Table(
id int,
largetext nvarchar(-1)
);

Note -1 was passed to SQL Server instead of max for the largetext column.

comments

jbelina wrote Feb 25, 2016 at 9:16 PM

Proposed change to createDeclareTVPTypeStatement:

<snip>
                if (String.Compare(dataTypeName, "char") == 0
                    ||
                    String.Compare(dataTypeName, "varchar") == 0
                    ||
                    String.Compare(dataTypeName, "nchar") == 0
                    ||
                    String.Compare(dataTypeName, "nvarchar") == 0)
                {

                    int characterMaximumLength = reader.GetInt32(2);

                    // replace: statement.Append("(" + characterMaximumLength + ")");
                    // with the following if/else block

                    if (characterMaximumLength >= 0) 
                    {
                        statement.Append("(" + characterMaximumLength + ")");
                    }
                    else
                    {
                        statement.Append("(max)");
                    }

                }
</snip>