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

h9vpoimq 于 2023-11-16 发布在 SQL Server

关注(0)|答案(1)|浏览(116)

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;

sql-server

来源：https://stackoverflow.com/questions/77358330/how-i-can-open-a-connection-to-a-sql-server-by-using-t-sql-script-and-execute-a

1条答案

按热度按时间

km0tfn4u1#

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

赞(0）回复(0）举报 2023-11-16

我来回答

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

1条答案

相关问题

热门标签

最新问答