Results 1 to 6 of 6
  1. #1
    Join Date
    Dec 2009
    Posts
    2

    Unanswered: Setup of new table - Help

    Hello,

    We have a .net apllication in Dutch. All the labels are hardcoded in the application itself at the moment. Now we need to transelate the complete app in French. Instead of doing this hardcoded we would like to put all the labels in a table in our sql db. So adding an other language in the future will be easy. I've gattered all the different labels and info texts. I've come to a total of 500 labels.

    At the moment I've creates an new table and set it up like this:
    Id - data type (int) - identity
    field_1 - data type (text)
    field_2 - data type (text)
    field_3 - data type (text)
    field_4 - data type (text)
    .......
    field_500 - data type (text)

    I'm trying to add the labels in the new table but at about kolom 430 I run into a "Cannot create a row of size (n) which is greater than the allowable maximum of 8060" problem." I thought that 'text' data type's were not stored in the db itself so these doesn't take any of the 8060kb of the row?

    Can any body give me a hint for a correct set up of this?

    Thank you...

  2. #2
    Join Date
    Jan 2003
    Location
    British Columbia
    Posts
    44
    Which version of SQL Server are you using?

    I thought that 'text' data type's were not stored in the db
    A Text column requires 16 bytes and stores a pointer to the actual data however, the Text datatype is considered obsolete and should be replaced with varchar(MAX).

    In my opinion using a LOB datatype is inappropriate for your table.

    Regardless, all the functionality you are after is built into .NET via Resource Files and there is no need to use a database.
    Search the .NET help files using Globalization, Localization, Culture Info.

  3. #3
    Join Date
    Aug 2008
    Posts
    147
    You have hit the limit of the table width. Why don't you use a table with just a few columns : such as "CREATE TABLE myTable (field_name VARCHAR(50), someText(50))"?
    ---------------------------------
    www.sqlserver-dba.com
    www.dba-db2.com

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by ringfoon View Post
    At the moment I've creates an new table and set it up like this:
    Id - data type (int) - identity
    field_1 - data type (text)
    field_2 - data type (text)
    field_3 - data type (text)
    field_4 - data type (text)
    .......
    field_500 - data type (text)
    perhaps you can redesign your table to look like this --
    Code:
    CREATE TABLE labels
    ( id INTEGER NOT NULL 
    , fieldno SMALLINT NOT NULL    -- from 1 to 500
    , PRIMARY KEY ( id, fieldno )
    , fieldlabel TEXT
    );
    thus, instead of each id having 500 values on a single row, it now has 500 values on separate rows

    i.e. instead of wide, it's tall
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Aug 2009
    Posts
    262
    The problem could have been easily avoided by vertically partitioning the data across multiple tables.
    or
    You can set an option to consider all variable data as "out-of-the-row".

    More information on : In-Row Data

    the problem is not what u think it is

  6. #6
    Join Date
    Nov 2004
    Posts
    1,427
    Provided Answers: 4
    Try this:
    Code:
    CREATE TABLE labels
    ( id INTEGER NOT NULL 
    , CoLanguage CHAR(2) NOT NULL    -- 'NL' for Dutch, 'FR' for French, 'EN' for English, ...
    , Label NVARCHAR(50) NOT NULL
    , PRIMARY KEY ( id, CoLanguage)
    );
    The values could be:
    Code:
    Id CoLanguage Label
    1   NL   School
    1   FR   Ecole
    1   EN   School
    2   NL   Leeftijd
    2   FR   Age
    2   EN   Age
    ...
    When you have to display the "Age" label with Id = 2 , do this select:
    Code:
    SELECT Label
    FROM Labels
    WHERE Id = 2 AND
          CoLanguage = @UserCoLanguage
    (You could use a FK LanguageId instead of CoLanguage.)

    If your development environment provides localisation support, try to use that in the first place.
    With kind regards . . . . . SQL Server 2000/2005/2012
    Wim

    Grabel's Law: 2 is not equal to 3 -- not even for very large values of 2.
    Pat Phelan's Law: 2 very definitely CAN equal 3 -- in at least two programming languages

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •