title: How can I convert a variable from one collation to another?
body: <p>I have a script that switches between two databases: master &amp; 'B'.</p>
  
  <p>Database master has collation 'SQL_Latin1_General_CP1_CI_AS'</p>
  
  <p>Database B has collation 'Latin1_General_CI_AS'</p>
  
  <p>I have tried using the COLLATE and CAST commands but to no avail so far.</p>
  
  <pre><code>USE B
  DECLARE @ProductsUserName varchar(200)
  SET @ProductsUserName = 'SomeValue'
  
  USE master 
  DECLARE @UserNameMaster varchar(200) = @ProductsUserName COLLATE SQL_Latin1_General_CP1_CI_AS
  DECLARE @GrantViewServerStatement varchar(200) = 'GRANT VIEW SERVER STATE TO ' + @UserNameMaster
  </code></pre>
  
  <p>The Query will blow up on the last line and will give the error:</p>
  
  <p>'Implicit conversion of varchar value to varchar cannot be performed because the collation of the value is unresolved due to a collation conflict between "Latin1_General_CI_AS" and "SQL_Latin1_General_CP1_CI_AS" in add operator.'</p>
  
  <p>Obviously there is some kind of problem using the '+' operator between two varchars that have different collations. But not sure how to "cast" the variable into the new collation!</p>