Search code examples
sql-servert-sqlssms

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


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;

Solution

  • 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' ;