none
Cannot insert NULL value in NVARCHAR(MAX) field in linked SQL Server table using SQL Server Native Client 10.0

    Question

  • I have a SQL Server 2008 table, that is linked in Microsoft Access 2010 through SQL Server Native Client 10.0. When I open that table in Access I can insert records just fine. When I try to update a record I cannot update the fields defined on SQL Server as NVARCHAR(MAX) or NVARCHAR(number > 255) to NULL. This worked fine with SQL Native Client 9.0.

    Here's the script for the SQL Server 2008 table:

    CREATE TABLE [dbo].[TestTable](
    [ID] [int] IDENTITY(1,1) NOT NULL,
    [Text_nvarchar255] [nvarchar](255) NULL,
    [Text_nvarcharMax] [nvarchar](max) NULL,
    [Text_ntext] [ntext] NULL,
    [Text_nvarchar500] [nvarchar](500) NULL,
    [Text_varchar255] [varchar](255) NULL,
    [Text_varcharMax] [varchar](max) NULL,
    [Text_text] [text] NULL,
    [Text_varchar500] [varchar](500) NULL,
    CONSTRAINT [PK_TestTable] PRIMARY KEY CLUSTERED
    (
    [ID] ASC
    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
    ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

    Update to a NULL value works for the fields:

    • Text_nvarchar255
    • Text_ntext
    • Text_varchar255
    • Text_varcharMax
    • Text_text
    • Text_varchar500

    Update to a NULL value does not work for the fields:

    • Text_nvarcharMax
    • Text_nvarchar500

    The error message I get:

    "ODBC: Update on a linked table 'TestTable' failed. [Microsoft][SQL Server Native Client 10.0][SQL Server]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 3 (""): Data type 0xE7 has an invalid data length or metadata length. (#8016)"

    Does anybody know how I can make the two fields Text_nvarcharMax and Text_nvarchar500 accept NULL values?

    BTW: I don't have any problems updating these fields to NULL in Microsoft SQL Server Management Studio or with SQL Native Client 9.0. And unfortunately I have to use the NVARCHAR datatype.

    Any help with this issue would be greatly appreciated.

    Tuesday, August 10, 2010 9:24 AM

Answers

All replies