Collation options using the command line

John Bayley

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

Brian Donahue

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

John Bayley

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 Donahue

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'