Creating SQL Server Linked Server using VBS

Here is a sample script for creating Linked Server using Vb script.

createLinkedSrvQuery "MACNAME\INSTANCENAME", "LINKEDSERVERNAME", "MAC_To_EstablishConnection\INSTNAME", "SERVICE_ACC_UNAME", "rmtUserLS", "rmtPwdLS"

Function createLinkedSrvQuery(strInstance, strLnkdSrv, dataSource, locLogin, rmtUser, rmtPwd)
    On Error Resume Next
    database = "master"
    strConnParams = "Provider=SQLOLEDB; Data Source=" & strInstance & "; Integrated Security=SSPI"

    Set objConn = CreateObject("ADODB.Connection")
    objConn.Open strConnParams

    strQuery = "IF EXISTS (SELECT srv.name FROM sys.servers srv WHERE srv.server_id != 0 AND srv.name = N'" & strLnkdSrv & "') EXEC master.dbo.sp_dropserver @server=N'" & strLnkdSrv & "', @droplogins='droplogins'"
    strQuery = strQuery & " BEGIN "
    strQuery = strQuery & " EXEC master.dbo.sp_addlinkedserver @server = N'" & strLnkdSrv & "', @srvproduct=N'SQLOLEDB', @provider=N'SQLNCLI', @datasrc=N'" & dataSource & "', @catalog=N'master' "
    strQuery = strQuery & "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'" & strLnkdSrv & "',@useself=N'True',@locallogin=NULL,@rmtuser=NULL,@rmtpassword=NULL "
    strQuery = strQuery & "EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'" & strLnkdSrv & "',@useself=N'False',@locallogin=N'" & locLogin & "',@rmtuser=N'" & rmtUser & "',@rmtpassword='" & rmtPwd & "' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'collation compatible', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'data access', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'dist', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'pub', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'rpc', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'rpc out', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'sub', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'connect timeout', @optvalue=N'0' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'collation name', @optvalue=null "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'lazy schema validation', @optvalue=N'false' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'query timeout', @optvalue=N'0' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'use remote collation', @optvalue=N'true' "
    strQuery = strQuery & "EXEC master.dbo.sp_serveroption @server=N'" & strLnkdSrv & "', @optname=N'remote proc transaction promotion', @optvalue=N'true' "
    strQuery = strQuery & "END"



    objConn.Execute strQuery
 

End Function