Creating link server Linked servers problems and workarounds

Problem Description

Today, when you do a database migration and then create a new link server (linked server), you encounter the following issues.

My SQL script is like this.

Then, you receive the following error message when you execute it.

MSG 468, Level A, State 9, line 7

Cannot resolve the collation conflict between "Latin1_General_CI_AS" and "sql_latin1_general_cp1_ci_as" in the equal to OP Eration.

cause Analysis

Depending on the error message, it is easy to find because the collation of the current DB is inconsistent with the collation of the DB corresponding to the linked server.

However, it is sql_latin1_general_cp1_ci_as to confirm that the collation of the current DB is exactly the same as the DB collation of the linked server pair.

Solutions

Of course, my solution here is just an alternative solution.

Since I created it through UI actions, I changed the scenario by using a SQL script (which can be used to find a normal link server right-click Export script and then modify it) to create the result.

Create a script for the link server:

/** * * * * object:linkedserver [Hp580dbsz] Script date:09/10/2015 17:25:22 * * * * **/IF  EXISTS(SELECTSrv.name fromSys.servers SRVWHEREsrv.server_id!= 0  andSrv.name=N'Hp580dbsz')EXECMaster.dbo.sp_dropserver@server=N'Hp580dbsz',@droplogins='droplogins'GO/** * * * * object:linkedserver [Hp580dbsz] Script date:09/10/2015 17:25:22 * * * * **/EXECMaster.dbo.sp_addlinkedserver@server =N'Hp580dbsz',@srvproduct=N'Hp580dbsz',@provider=N'SQLNCLI10',@datasrc=N'Hp580dbsz\dev' /*For security reasons the linked server remote logins password are changed with ########*/EXECMaster.dbo.sp_addlinkedsrvlogin@rmtsrvname=N'Hp580dbsz',@useself=N'False',@locallogin=NULL,@rmtuser=N'SA',@rmtpassword='Ctrip.hp580dbsz.dev'GO

Issues and workarounds for creating link server linked Servers