linked Server Collation Compatible

[Gruss]

I am having SQL 2005... I tried changing the Collation compatibility to my linked server it didn't worked for me... Do SQL allow query collation to linked server...??? Please Help...!

[Praetor]

I think you should use the collate option for casting collation in the query. I hope you understood what i told you to do...! Also was thinking what compatibility mode you where using???

[Katty]

Defining Linked Server Options :

The following linked server options that are defined by using sp_serveroption control whether and how SQL Server uses collations from linked servers :

• The use remote collation option specifies whether the collation of a remote column or of a local server will be used. When true, the collation of remote columns is used for SQL Server data sources, and the collation specified in collation name is used for data sources other than SQL Server. When false, distributed queries always use the default collation of the local server instance, and collation name and the collation of remote columns are ignored.
  
  
• The collation name option specifies the name of the collation used by the remote data source if use remote collation is true and the data source is not a SQL Server data source. The name must be one of the collations supported by SQL Server. Use this option when you access an OLE DB data source other than SQL Server, but whose collation matches one of the SQL Server collations. SQL Server data sources report their column collations, and collation name is ignored for linked servers that reference SQL Server data sources.

[kelfro]

Changing the Server Collation :

Changing the default collation for an instance of SQL Server can be a complex operation and involves the following steps :

• Make sure you have all the information or scripts needed to re-create your user databases and all the objects in them.
  
  
• Export all your data using a tool such as the bcp Utility. For more information, see Importing and Exporting Bulk Data.
  
  
• Drop all the user databases.
  
  
• Rebuild the master database specifying the new collation in the SQLCOLLATION property of the setup command. For example:
  
  

        Setup /QUIET /ACTION=REBUILDDATABASE /INSTANCENAME=InstanceName 
        /SQLSYSADMINACCOUNTS=accounts /[ SAPWD= StrongPassword ] 
        /SQLCOLLATION=CollationName

• Create all the databases and all the objects in them.
  
  
• Import all your data.