SQL Server creates a linked server

The following issues were encountered:

On-line Server A, brokered server B, local server C

The data is above a and wants to operate on a B like select * FROM [a]. [database].table Such SQL, do not have to link the server, directly import the processing results into B and then generate the report.

The resulting error is as follows:

Msg 7202, Level 11, State 2, line 1th
Server ' A ' could not be found in sys.servers. Verify that the server name you specified is correct. If necessary, execute the stored procedure sp_addlinkedserver to add the server to Sys.servers.

Solution: The problem is obviously that there is no service A in B, strange B above the local import and export can be, why this can not? Looked carefully only to find that the SQL Server linked servers list above B does

does not exist a.

Do the following to create a server connection on server B,

Application Scenario: operate on a remote db.

- Compared to 2005: SQL Server 2000 also has links to remote DB, but the functionality is weak, the scalability is poor, the supported queries are relatively simple. While the SQL Server 2005 version of SSMs already has a function point for the server object, the linked server , the user first creates a linked object of the remote DB, and then executes the DML of the table like the local surface.

Create step: Open in SQL Server 2005 version ssms, server object, linked server, right-click New linked server, in Figure 2 is a way to set up, there are other settings, such as: [schematic] Create linked servers in SQL Server, Figure 3 is the security option to set the account and password for the remote database.

(Figure 1: New link)

(Figure 2: Setting up a link)

(Figure 3: Setting up an account)

Note: in MSSQL2005, the default setting of RPC is 4, you need to set it to Figure 5, right-click the remote link-properties-----server Option->RPC and RPC out, both values need to be set to true.

(Figure 4: Default setting)

(Figure 5: Set correctly)

However, under MSSQL2008 cannot directly modify the RPC configuration of the linked server ' Etv2_link ' to ture, which can be modified by the following statements:

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

Build Script: If you have generated the ' Etv2_link ' linked server through the interface, then we can use the following method to produce SQL script if we need to port it to another database (deployment, update). You can also quickly create or modify a linked server by modifying the SQL script, such as modifying the @server linked server name and modifying the @datasrc remote linked database object.

(Figure 6: Generate SQL Script)

SQL Server 2005 generates a remote-linked object for the SQL script:

/****** 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 '

Use : Assuming that a remote link object named Etv2_link has been created, you can use this object to manipulate the remote db as follows.

Use Scenario 1: query Etv2_link data from the visitelog_20100629 table in the [Etv2_online] database of this remote link object. Template shape such as: Select * from [linked server name]. [Remote database name]. [owner]. [Table name]

--Querying Remote DB table TableName
SELECT * from Etv2_link. [Etv2_online].dbo. visitelog_20100629

Use Scenario 2: determine if a table named visitelog_20100629 exists in the [Etv2_online] database of the Etv2_link object.

--Note: It's sys.objects not sysobjects.
--Determine if a remote user has a table
IF EXISTS (SELECT * from etv2_link.[ Etv2_online].sys.objects WHERE name = N ' visitelog_20100629 ' and type in (n ' U '))
BEGIN
--Logic processing
print ' Presence table '
END

Use Scenario 3: determine if a table named visitelog_20100629 exists in the [Etv2_online] database of the remote db. Only the table name is parameterized and can be judged by the parameters passed in. This simply sets the value of the variable and uses out to return the variable.

--to determine if a remote user exists a table (parameterized table name), return variable
DECLARE @IsExistTable VARCHAR (10)
DECLARE @Tablename VARCHAR (50)
DECLARE @sqlString NVARCHAR (4000)
SET @IsExistTable = ' False '
SET @Tablename = ' visitelog_ ' +convert (varchar (9), GETDATE ()-1,112)--such as visitelog_20100629
SET @sqlString =
' IF EXISTS (SELECT * from etv2_link.[ Etv2_online].sys.objects WHERE name = n "" [Email protected]+ ' and type in (N ' U '))
Set @IsExistTableOUT = ' True '
EXEC sp_executesql @sqlString, N ' @IsExistTableOUT varchar () output ', @[email protected] Output

IF (@IsExistTable = ' True ')--existence
BEGIN
--Logic processing
print ' Presence table '
END

Add: SQL Server 2000 version connects to the remote server's SQL script, more relevant steps can be consulted: Accessing a remote database in a T-SQL statement (openrowset/opendatasource/openquery)

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

SQL Server creates a linked server