none
How to set collation of a table?

    Question

  •  

    Hi,

    I need to set a collation of one table to another.
    I'm using following code.

    declare @collation1 int
    Set @collation1 = (select collation from sysobjects so
    inner join syscolumns sc
    on so.id = sc.id and sc.name = 'uiName'
    where so.name like 'Dictionary')

    if ( exists ( select * from sysobjects where name = '#a' ) )
     drop table #a
    GO

    CREATE TABLE #a(ObjectIdHi int, ObjectIdLo int, OwnerIdHi int, OwnerIdLo int, OwnerPath varchar(200) COLLATE collation1, uiName varchar(200) COLLATE @collation1) select d.ObjectIdHi, d.ObjectIdLo, d.OwnerIdHi, d.OwnerIdLo, d.OwnerPath, d.uiName
    from #ObjList d

    But in at CREATE TABLE statement I'm getting the below error

    Invalid collation 'collation1'.

    How to resolve this error?

    Please help me.

    Wednesday, October 08, 2008 9:00 PM

Answers

  • Hi sindhu:

     

    you make some error in your code:

     

    1:if you use variable in create table statement,you must use dynamic t-sql like below:

     

    Code Snippet

     declare @collation varchar(100)
    declare @sql varchar(100)

    set @collation = 'Latin1_General_BIN'

    set @sql = 'create table test(name varchar(20) collate ' + @collation + ')'

    exec (@sql)

    drop table test
    go

     

     

     

             2:variable  can only store in same batch process,for example,below code will occur error:

     

    Code Snippet

    declare @collation varchar(100)
    declare @sql varchar(100)

    set @collation = 'Latin1_General_BIN'
    go

    set @sql = 'create table test(name varchar(20) collate ' + @collation + ')'

    exec (@sql)

    drop table test
    go

     

     

     

    Error:

     

    Msg 137, Level 15, State 2, Line 2
    Must declare the scalar variable "@collation".
    Msg 137, Level 15, State 2, Line 4
    Must declare the scalar variable "@sql".

     

     

    Hope it helps.


    Thursday, October 09, 2008 1:29 AM

All replies

  • Here you go 

    Create table Mytable (
    [colu] char(10) COLLATE Albanian_CI_AI_KS_WS NULL,
    [Maydate] [char] (8) COLLATE Korean_Wansung_Unicode_CS_AS_KS NOT NULL ,
    [Risk_Rating] [char] (2) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL 
    )
    Wednesday, October 08, 2008 9:09 PM
  • Hi sindhu:

     

    you make some error in your code:

     

    1:if you use variable in create table statement,you must use dynamic t-sql like below:

     

    Code Snippet

     declare @collation varchar(100)
    declare @sql varchar(100)

    set @collation = 'Latin1_General_BIN'

    set @sql = 'create table test(name varchar(20) collate ' + @collation + ')'

    exec (@sql)

    drop table test
    go

     

     

     

             2:variable  can only store in same batch process,for example,below code will occur error:

     

    Code Snippet

    declare @collation varchar(100)
    declare @sql varchar(100)

    set @collation = 'Latin1_General_BIN'
    go

    set @sql = 'create table test(name varchar(20) collate ' + @collation + ')'

    exec (@sql)

    drop table test
    go

     

     

     

    Error:

     

    Msg 137, Level 15, State 2, Line 2
    Must declare the scalar variable "@collation".
    Msg 137, Level 15, State 2, Line 4
    Must declare the scalar variable "@sql".

     

     

    Hope it helps.


    Thursday, October 09, 2008 1:29 AM
  •  

    Hi CN_SQL,

     

    Thanks a lot.

    You solution is a perfect answer to my question.

     

    Thanks again

    Thursday, October 09, 2008 1:32 PM