Article From:https://www.cnblogs.com/hongboiiii/p/9971925.html

The following problems were encountered:

Online Server A, Transfer Server B, Local Server C

Data on A, want to operate on B such as select * from [A]. [database]. table SQL, do not need to go to the link server, directly import the processing results into B and generate reports.

The results are as follows:

Message 7202, level 11, status 2, line 1
Server’A’could not be found in sys. servers. Verify that the specified server name is correct. If necessary, execute the stored procedure sp_addlinkedserver to add the server to sys. servers.

Solution: The obvious problem is that there is no service A in B. It’s strange that the local import and export on B can be done. Why not? Looking carefully, I found that the list of sqlserver link servers above B is true.

There is no A.

Perform the following actions to create A server connection on B server.

 

Applicable scenarios:Operate on remote DB.

2000Compared with 2005:There are also SQL links to remote DB in the version of SQL Server 2000, but the functions are weak, the scalability is poor, and the queries supported are relatively simple. And SQL Server 2005 version of SMS already has.Server Object – & gt; Link ServerFunction point: Users first create a link object for remote DB, and then they can execute the DML of the table like the local table.

Create steps:Open SSMS in SQL Server 2005, Server Object – & gt; Link Server – & gt; Right-click New Link Server. In Figure 2, there are other settings, such as: Create Link Server in [Diagram] SQL ServerServer, Figure 3 is the account and password of the remote database set in the security options.

(Figure 1: New links)

 

(Figure 2: Setting Links)

 

(Figure 3: Setting Accounts)

 

Notes:The default setting of Rpc in MSQL 2005 is shown in Figure 4. You need to set it to Figure 5. Right-click on remote link – & gt; attribute – & gt; server option – & gt; Rpc and Rpc Out. These two values need to be set to True.

 

(Figure 4: Default settings)

 

(Figure 5: Correct settings)

However, under MSSQL 2008, the RPC configuration of the link server `ETV2_LINK’can not be directly modified to TURE, which can be modified by the following statements:

Copy Code uuuuuuuuuuuu
 
USE [master]
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’rpc out’, @optvalue=N’true’
GO
 
Copy Code uuuuuuuuuuuu

 

Generate scripts:If the link server of’ETV2_LINK’has been generated through the operation interface, then if we need to transplant it to other databases (deployment, update), we can produce the SQL script by the following way. You can also quickly create or modify the SQL script by modifying it.Modify the link server, such as modifying the name of the @server link server and modifying the database object of the @datasrc remote link.

 

(Figure 6: Generating SQL scripts)

 

SQL Server 2005Generate SQL scripts for remote link objects:

Copy Code uuuuuuuuuuuu
 
/****** Object: LinkedServer [ETV2_LINK]: script date: 09/08/2010 17:36:11 ******//
EXEC master.dbo.sp_addlinkedserver @server = N’ETV2_LINK’, @srvproduct=N’ETV2_LINK’, @provider=N’SQLNCLI’, @datasrc=N’BWA035\BWA035_2K5′
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’collation compatible’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’data access’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’dist’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’pub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’rpc’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’rpc out’, @optvalue=N’true’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’sub’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’connect timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’collation name’, @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’lazy schema validation’, @optvalue=N’false’
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’query timeout’, @optvalue=N’0′
GO
EXEC master.dbo.sp_serveroption @server=N’ETV2_LINK’, @optname=N’use remote collation’, @optvalue=N’true’
 
Copy Code uuuuuuuuuuuu

 

UseAssuming that a remote link object named ETV2_LINK has been created, you can use this object to manipulate remote DB as follows.

Scenario 1:Query the data of VisiteLog_20100629 table in the [etV2_Online] database of ETV2_LINK remote link object. Templates such as: Select * From [Link Server Name]. [Remote Database Name]. [All[Table name]

–Query remote DB table TableName
select * from ETV2_LINK.[etV2_Online].dbo.VisiteLog_20100629

 

Use scenario 2:Determine whether a table named VisiteLog_20100629 exists in the [etV2_Online] database of ETV2_LINK, a remote linked object.

Copy Code uuuuuuuuuuuu
 
–Note: Sys. objects are not sys objects
–Determine whether a table exists for a remote user
IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N’VisiteLog_20100629′ AND type in (N’U’))
BEGIN
    –Logic processing
    print ‘Existence table
END
 
Copy Code uuuuuuuuuuuu

 

Scenario 3:Determine whether a table named VisiteLog_20100629 exists in the [etV2_Online] database of remote DB. However, the table name is parameterized and can be judged by the parameters passed in. Here we simply set the value of the variable and use OUT to return it.Variables.  

Copy Code uuuuuuuuuuuu
 
–Determine whether a table (parameterized table name) exists for remote users and return variables
DECLARE @IsExistTable VARCHAR(10)
DECLARE @Tablename VARCHAR(50)
DECLARE @sqlString NVARCHAR(4000)
SET @IsExistTable = ‘False’
SET @Tablename = ‘VisiteLog_’+convert(varchar(9),getdate()-1,112) –For example, VisiteLog_2010 00629
SET @sqlString = 
‘IF EXISTS (SELECT * FROM ETV2_LINK.[etV2_Online].sys.objects WHERE name = N”’+@Tablename+”’ AND type in (N”U”))
    set @IsExistTableOUT =”True”’
EXEC sp_executesql @sqlString,N’@IsExistTableOUT varchar(10) OUTPUT’,@IsExistTableOUT=@IsExistTable OUTPUT

IF (@IsExistTable = ‘True’)–existence
BEGIN
    –Logic processing
    print ‘Existence table
END

 
Copy Code uuuuuuuuuuuu

 

Supplement: SQL Server 2000Version of the SQL script for connecting remote servers, more relevant steps can be referred to: Accessing remote databases (openrowset/opendatasource/openquery) in T-SQL statements

–Method 1:
select *  from openrowset(‘SQLOLEDB’,’server=192.168.0.67;uid=sa;pwd=password’,’SELECT * FROM BCM2.dbo.tbAppl’)

–Method 2:
select *  from openrowset(‘SQLOLEDB’,’192.168.0.67′;’sa’;’password’,’SELECT * FROM BCM2.dbo.tbAppl’)

Link of this Article: Creating Link Server by SQL Server

Leave a Reply

Your email address will not be published. Required fields are marked *