locked
SQL Server Migration Assistant (SSMA) for MS Access is creating a check constraint for disallow zero length for a column that Access allows zero length. RRS feed

  • Question

  • My Access 2013 database tables have columns of type "Long Text" that have the Required property set to 'No' and the 'Allow zero length' property set to 'Yes'.
    Why then the SSMA for Access is creating a check constraint on these columns as disallow zero length. You can test the issue by creating a test db with one table in Access with one column of type 'Short Text' or 'Long Text'. The table will have default 'Required' property set to 'No' and the 'Allow zero length' property set to 'Yes'. Then you migrate that table to SQL Server. You will notice that SSMA will create a check constraint disallowing zero length like the one shown below:

        Create table table1(c1 nvarchar(max),  constraint c_ssma_disallow_zero_length  check(len(c1) >0))

    The above check constraint forces the SQL Server nvarchar column to have a non-zero length.



    • Edited by namwam Thursday, September 17, 2015 9:33 PM
    Wednesday, September 16, 2015 3:48 PM

All replies

  • Hello,

    The expected behavior is the following:

    “If the Allow zero length column property is set to false on text/memo columns in Jet, it is mapped to a check constraint with a constraint name "c_ssma_disallow_zero_length"  on the varchar/nvarchar columns in SQL Server 2005.

    For example, the following code shows how to convert the Allow zero length column property on column c1 in table table1:

    Create table table1(c1 nvarchar(10),  constraint c_ssma_disallow_zero_length  check(len(c1) >0))”

    Source: http://download.microsoft.com/download/e/c/8/ec8d5025-7ef7-4dcc-a9f3-9c297cf5350e/SSMAAccess.docx



    Hope this helps.



    Regards,

    Alberto Morillo
    SQLCoffee.com

    Wednesday, September 16, 2015 7:33 PM
  • Alberto, it seems you misunderstood my question. My column allows zero length (Allow Zero Length = Yes by default) but SSMA is creating a check constraint on that column that disallows zero length.
    Thursday, September 17, 2015 9:37 PM