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
