Collation options using the command line

John BayleyJohn Bayley Posts: 7
edited October 3, 2006 6:08PM in SQL Packager Previous Versions
Using SQL Packager's command line, what is the equivalent of the GUI's "Ignore collation order" setting?

I have unsuccessfully tried:
/collation:ignore
/collation:none

Using these settings, I can generate a package executable, but it gives an error when I run it:
Invalid collation 'none'.

What is the correct setting?

Thanks,
John

Comments

  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Hello,

    In the command-line version of Packager (sqlpackager.exe), you're looking at specifying some options.
    sqlpackager.exe /database1:Northwind /options:Default,IgnoreCollations /name:nw /makeexe
    
  • Thanks very much, Brian. However, when I run the resulting executable, I get this error:
    Cannot resolve the collation conflict between "Latin1_General_CI_AI" and "Latin1_General_BIN" in the equal to operation.
    
    create procedure dbo.show_table_dictionary @table_name varchar(200) as
        select
      
        o.[name] as 'Table_Name',
        e.value as 'Purpose',
        e2.value as 'Related_Tables'
    
        from sysobjects o 
        inner join ::FN_LISTEXTENDEDPROPERTY('Purpose', 'user','dbo','table',@table_name, null, null) e on o.name = e.objname
        inner join ::FN_LISTEXTENDEDPROPERTY('Related Tables', 'user','dbo','table',@table_name, null, null) e2 on o.name = e2.objname
        where o.xtype = 'U'
    

    Would you expect "IgnoreCollations" to prevent this error?

    If not, do you have any other suggestions?

    -- John
  • Brian DonahueBrian Donahue Posts: 6,590 New member
    Probably not -- Ignore collations simply throws away any collation specified for the columns in your tables. Extended properties are capable of holding binary information and maybe this is causing the problem.

    Maybe changing the function to CAST FN_LISTEXTENDEDPROPERTY as nvarchar will prevent this?
    create procedure dbo.show_table_dictionary @table_name varchar(200) as 
        select 
      
        o.[name] as 'Table_Name', 
        e.value as 'Purpose', 
        e2.value as 'Related_Tables' 
    
        from sysobjects o 
        inner join ::FN_LISTEXTENDEDPROPERTY('Purpose', 'user','dbo','table',@table_name, null, null) e on o.name COLLATE Latin1_General_CI_AS = e.objname  inner join ::FN_LISTEXTENDEDPROPERTY('Related Tables', 'user','dbo','table',@table_name, null, null) e2 on o.name COLLATE Latin1_General_CI_AS = e2.objname 
        where o.xtype = 'U'
    
Sign In or Register to comment.