MSSQL: Creating Link Database Server

You can create any link database server with any provider string.

EXEC master.dbo.sp_addlinkedserver

    @server = N'LZAMSMEXPRD\AMSMEX',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

    @provstr=N'PROVIDER=SQLOLEDB;SERVER=lzamsmexqat\amsmex,1433'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LZAMSMEXPRD\AMSMEX',

    @useself=N'False',@locallogin=NULL,@rmtuser=N'sa',@rmtpassword='P@ssword!'

Eariler version could have some issue with user having no sysadmin role. You can use the following one for that.

EXEC master.dbo.sp_addlinkedserver

    @server = N'CHRGBKP2NT',

    @srvproduct=N'MSSQL',

    @provider=N'SQLNCLI',

@datasrc='PSM-TESTSVR2'


EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'CHRGBKP2NT',
    @useself=N'False',@locallogin=NULL,@rmtuser=N'jcolton',@rmtpassword='jcolton254'


For SQL Server 2012, use the following script.

EXEC master.dbo.sp_addlinkedserver @server = N'TOCGJTIM1PV\JTIME', @srvproduct=N'MSSQL', @provider=N'SQLNCLI', @datasrc=N'TOCFJTIM1UV\JTIME'

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'TOCGJTIM1PV\JTIME',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL