Change collation on the fly (select statement)

• arup chakraborty

  Hall of Fame

  Points: 3640

  More actions

  October 4, 2011 at 10:53 am

  #249245

  Hi All,

  Can we change the collation on the fly? i.e. using select statement?

• Lowell

  SSC Guru

  Points: 323504

  More actions

  October 4, 2011 at 10:56 am

  #1390253

  sure!

  this was the first select + collation example i had in my snippets:

  SELECT

  'REVOKE ' + convert(varchar(50),x.[Action])

  + ' on ' + x.[Schema]

  + '.' + convert(varchar(50),x.[Object])

  + ' TO ' + convert(varchar(50),x.[User]) COLLATE Latin1_General_CI_AS

  FROM (

  SELECT

  u.name COLLATE Latin1_General_CI_AS AS 'User',

  schema_name(o.schema_id) As 'Schema',

  o.name COLLATE Latin1_General_CI_AS AS 'Object' ,

  p.permission_name COLLATE Latin1_General_CI_AS AS 'Action'

  --into tmp

  FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o

  WHERE o.object_id = p.major_id

  AND p.grantee_principal_id = u.principal_id

  AND p.grantee_principal_id IN (0, 2)

  ) x

  Lowell

  ---

  --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

• David Paskiet

  SSCrazy

  Points: 2949

  More actions

  October 4, 2011 at 11:12 am

  #1390257

  You need to add the alias identifier at the end. The select statement makes table 'x'.

  SELECT

  'REVOKE ' + convert(varchar(50),x.[Action])

  + ' on ' + x.[Schema]

  + '.' + convert(varchar(50),x.[Object])

  + ' TO ' + convert(varchar(50),x.[User]) COLLATE Latin1_General_CI_AS

  FROM

  (

  SELECT

  u.name COLLATE Latin1_General_CI_AS AS 'User',

  schema_name(o.schema_id) As 'Schema',

  o.name COLLATE Latin1_General_CI_AS AS 'Object' ,

  p.permission_name COLLATE Latin1_General_CI_AS AS 'Action'

  FROM sys.database_permissions p, sys.database_principals u, sys.all_objects o

  WHERE o.object_id = p.major_id

  AND p.grantee_principal_id = u.principal_id

  AND p.grantee_principal_id IN (0, 2)

  ) x

  Kindest Regards,
  David

  ** Obstacles are those frightening things that appear when we take our eyes off the goal. **