Quering tables residing on different servers and collation problem

If you want to query tables residing on another server first step is to link this server. You will do this in SQL Server Management Studio. Just expand the Server Objects node and you will see the Linked Servers node on which you can select "New Linked Server..." command.

Another option is to run these commands:

EXEC p_addlinkedserver    
  @server='<machine name>', 
  @srvproduct='',
  @provider='SQLOLEDB',
  @datasrc='<server name>',
  @catalog ='<database name>'
  
EXEC sp_addlinkedsrvlogin 
  '<server name>', 
  'false', 
   NULL, 
  '<login name>', 
  '<login password>'

Now you can query tables and views from the databases residing on the server you linked. They should be addressed this way:

[server].[database].[schema].[table]


It may happen that there are different collations in your host database and in linked database. As result, when linking tables from multiple servers in one query, you will get error:

Cannot resolve the collation conflict between "Polish_CI_AS" and "Latin1_General_CI_AS" in the equal to operation.

My first guess was to change the collation. I choose the host database as it was still in the development phase.

ALTER DATABASE [database-name] SET SINGLE_USER WITH ROLLBACK IMMEDIATE
ALTER DATABASE [database-name] COLLATE Polish_CI_AS
ALTER DATABASE [database-name] SET MULTI_USER


But it hasn't helped. The above commands changed the default collation of database and tables, but not the collation of existing columns.

Below you can find the code of the stored procedure generating ALTER TABLE statements for changing the columns collations all over the database.

But watch out! If there is any key or index on the column, the statement will fail. You have to drop the key, run the statement and recreate the key.

CREATE PROCEDURE [dbo].[ChangeCollation]

(

    @collation_name varchar(25)

)

AS

BEGIN

    SET NOCOUNT ON;

      

    DECLARE @TableName nvarchar(100)

    DECLARE @ColumnName nvarchar(100)

    DECLARE @ColumnType nvarChar(100)

    DECLARE @ColumnLengh float

    DECLARE @SQL nvarchar(1000)

    DECLARE @IsNullAble nvarchar(50)

    DECLARE cursor_tables CURSOR FOR

        select sysobjects.name from sysobjects where xtype='u'

             

    OPEN cursor_tables

    FETCH NEXT FROM cursor_tables INTO @TableName

    WHILE @@FETCH_STATUS = 0 BEGIN

      

        DECLARE cursor_columns CURSOR FOR

            select  syscolumns.name,

                    systypes.name as Type,

                    syscolumns.length,

                    syscolumns.isnullable

            from syscolumns

            inner join sysobjects on sysobjects.id=syscolumns.id

            inner join systypes on syscolumns.xtype = systypes.xtype

            where

                sysobjects.xtype='u' and

                (systypes.name='nvarchar' or systypes.name='varchar') and

                sysobjects.name=@TableName

             

        OPEN cursor_columns

        FETCH NEXT FROM cursor_columns 

            INTO @ColumnName, @ColumnType, @ColumnLengh, @IsNullAble

        

        WHILE @@FETCH_STATUS = 0 BEGIN

            IF (@IsNullAble='1')

                SET @IsNullAble = ' NULL'

            ELSE

                SET @IsNullAble = ' NOT NULL'

                    

            SET @SQL = 'ALTER TABLE ' + @TableName + ' ALTER COLUMN ' + @ColumnName + ' '

                     + @ColumnType + '(' + CAST(@ColumnLengh as NVARCHAR) + ') COLLATE '

                     + @collation_name + @IsNullAble

            PRINT(@SQL)

            --EXEC(@@SQL)

            FETCH NEXT FROM cursor_columns 

                INTO @ColumnName, @ColumnType, @ColumnLengh, @IsNullAble

        END

        CLOSE cursor_columns

        DEALLOCATE cursor_columns

        FETCH NEXT FROM cursor_tables INTO @TableName

    END

    CLOSE cursor_tables

    DEALLOCATE cursor_tables

END

GO