2
Vote

Problem with Non-nullable nvarchar columns

description

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?

comments

ondrejsv wrote Aug 13, 2008 at 8:05 AM

Hello,

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 = @"
      select
        C1.Name as ""ColumnName"", 
        TP.name as ""SystemTypeName"", 
        C1.is_Nullable, 
        C1.max_length, 
        C1.is_Identity 
      from Sys.tables T1, Sys.columns C1, Sys.types TP
      where
        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 2:29 AM