How I can open a connection to a SQL Server by using T-SQL script and execute a ETL task?

Question

When I tried this script, I got an error indicating that the login had failed. However, I am able to initiate a connection with the server normally from the SSMS environment

USE MyDB;
GO

TRUNCATE TABLE MyDB.dbo.MyTable

EXEC sp_addlinkedserver 'MySer', N'SQL Server';
EXEC sp_addlinkedsrvlogin 'MySer', 'false', 'Uname', 'pw';

INSERT INTO dbo.MyTable (ID, Name, NameEn, Acronym)
    SELECT 
        r.R_Id, 
        r.R_Desc, 
        r.R_Desc_E, 
        ''
    FROM 
        MySer.RemoteDB.dbo.RemoteTable r;

Answer 1

For your linked server add datasource and catalog.

EXEC master.dbo.sp_addlinkedserver 
      @server = N'MySer', 
      @srvproduct=N'', 
      @provider=N'SQLNCLI11', 
      @datasrc=N'YourRegistratedServerName', 
      @catalog=N'RemoteDB';
    go
    EXEC master.dbo.sp_addlinkedsrvlogin 
      @rmtsrvname=N'MySer',
      @useself=N'False',
      @locallogin=NULL,
      @rmtuser=N'Uname',
      @rmtpassword = N'pw' ;