Problem with Non-nullable nvarchar columns


We are having trouble with a database table that contains non-nullable nvarchar columns. When you try to load data with the InsertManager, an exception is thrown in TableBase.CreateTableObjectFromDatabase.
What seems to be happening is:
* The routine is trying to load all of the required fields into a dictionary
* The SQL query to find the columns returns duplicate rows because nvarchar and sysname have the same "systemtypeid"
* The routine tries to add the column to the StringDictionary twice, which throws an exception
Is there any possibility that this will get fixed? Could I submit a patch?


ondrejsv wrote Aug 13, 2008 at 7:05 AM


I came across this bug also but when I tried to get existing rows in a table with an nvarchar column. The exception being thrown was coming from the CreateTableObjectFromDatabase method and the framework was added the same value twice.

Fortunately, this bug is very easy to fix.

Open the DateUtilities.cs, find the GetColumnSchemaInfo method and change the SQL in the first line to this:
    string strSql = @"
        C1.Name as ""ColumnName"", 
        TP.name as ""SystemTypeName"", 
      from Sys.tables T1, Sys.columns C1, Sys.types TP
        C1.object_Id = T1.object_id
        and C1.user_type_id = TP.user_type_id
      and T1.Name = '" + strTableName + @"'
      order by C1.name
The only change is using user_type_id instead of system_type_id to join sys.columns and sys.types to get the type for a column. System_type_id is not a primary key of the sys.types view and beside nvarchar type there is one more row with the same id (sysname type), hence the loaded schema was incorrect.

wrote Feb 2, 2013 at 1:29 AM