Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Location
    Dubai
    Posts
    29

    Exclamation Unanswered: How to have data stored in UTF 8 format in SQLServer

    Dear friends,
    In SQLServer, it seems to make data stored in Unicode, you need to go to each column type and change it from varchar to nvarchar.

    Apart from this, is there a direct way of Setting Unicode for the whole database at once, which would set all coulns to the unicode equivalent.
    It seems there is such an option in Oracle

    Please comment on this..

    Regards

    Benny

  2. #2
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    15,579
    Provided Answers: 54
    Oracle and DB2 have both taken the approach that "characters are characters", regardless of their size. The rest of the databases I know of still understand that 16 bit characters need different handling (for many reasons) than 8 bit characters need.

    I don't know of any option in SQL Server to allow 16 bit characters to become the default, although many of the database modeling tools will happily let you do just that.

    -PatP

  3. #3
    Join Date
    Aug 2002
    Location
    Scotland
    Posts
    1,578
    You could do this through the system tables, make a backup first. It is risky to modify system tables so you need to thoroughly test this. Look up the syscolumns table to create your update.

    I'd used this script earlier (written by Mike Walsh)

    set nocount on
    declare @ColumnName varchar(255)
    declare @TableName varchar(255)
    declare @strSQL nvarchar(255)

    DECLARE curFixColsGetTables CURSOR
    FOR SELECT TABLE_NAME
    FROM information_schema.tables
    WHERE table_Type = 'Base Table'

    OPEN curFixColsGetTables
    FETCH NEXT FROM curFixColsGetTables into @TableName

    WHILE @@FETCH_STATUS = 0
    BEGIN

    DECLARE curFixColsGetCols CURSOR
    FOR SELECT COLUMN_NAME
    FROM information_schema.columns
    WHERE table_name = @TableName AND DATA_TYPE = 'varchar'

    OPEN curFixColsGetCols
    FETCH NEXT FROM curFixColsGetCols INTO @ColumnName

    WHILE @@FETCH_STATUS = 0
    BEGIN
    SELECT @strSQL = 'ALTER TABLE ' + @TableName
    + ' ALTER COLUMN ' + @ColumnName
    + ' nvarchar(500)'
    PRINT @strSQL
    EXEC SP_EXECUTESQL @strSQL

    FETCH NEXT FROM curFixColsGetCols
    INTO @ColumnName
    END
    CLOSE curFixColsGetCols
    DEALLOCATE curFixColsGetCols

    FETCH NEXT FROM curFixColsGetTables
    INTO @TableName
    END
    CLOSE curFixColsGetTables
    DEALLOCATE curFixColsGetTables

    HTH
    --Satya SKJ
    Microsoft SQL Server MVP
    [IMG]http://sqlserver-qa.net/google_bart.gif[/IMG]

Posting Permissions

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