SQL Server Remote connection Server detailed configuration (sp_addmediaserver), spaddmediaserver

SQL Server Remote connection Server detailed configuration (sp_addmediaserver), spaddmediaserver

Detailed configuration of remote connection Server

-- Create a connection Server EXEC sp_add+server 'remote server ip', 'SQL Server' -- mark the local name of the storage EXEC sp_add+server @ server = 'server', -- link to the Server. You can also use the Instance name, for example, MYSERVER \ SQL1 @ srvproduct = 'product _ name' -- the product name of the ole db data source. For SQL Server instances, product_name is 'SQL Server', @ provider = 'provider _ name' -- this is the unique programmable identifier of the OLE DB access interface. If it is not specified, the access interface name is the SQL Server data source. The explicit provider_name of SQL Server is SQLNCLI (Microsoft SQL Native Client OLE DB Provider ). Oracler is MSDAORA, and Oracle 8 or later is OraOLEDB. Oracle. MS Access and MS Excel are Microsoft. Jet. OLEDB.4.0. DB2OLEDB for IBM DB2 and MSDASQL for ODBC data sources, @ datasrc = 'data _ source' -- this is the data source interpreted by the specific ole db access interface. For SQL Server, This is the network name of SQL Server (servername or servername \ instancename. For Oracle, this is an alias for SQL * Net. For the MS Access and MSExcel, this is the full path and name of the file. For the ODBC data source, this is the system DSN name, @ location = 'location' -- the location explained by the specific ole db access interface, @ provstr = 'provider _ string' -- the connection string specified by the ole db access interface. For ODBC connections, this is an ODBC connection string. For MS Excel, this is the definition change of Excel 5.0, @ catalog = 'catalog '-- catalog Based on The Implementation of The ole db access interface. For SQL Server, this is an optional database name. For DB2, this directory is the database name -- create a EXEC ing between remote logon on the linked Server EXEC sp_add1_srvlogin 'remote Server ip ', 'false', 'sa ', 'Architecture name', 'Access password' -- Mark storage EXEC sp_add1_srvlogin @ rmtsrvname = 'remote server ip ', -- The local connection server @ useself = false to add login name ing -- when the value of true is used, the local SQL or Windows login name is used to connect to the remote server name. If it is set to false, the locallogin, rmtuser, and rmtpassword parameters of the stored procedure sp_add1_srvlogin will be applied to the new ing. @ locallogin = NULL, -- This is the name of the SQL Server logon or Windows user mapped to the remote login name. If this parameter is set to NULL, The ing applies all local login names in the SQL Server instance @ rmtuser = 'schema name ', -- name of the user/login name used to connect to the linked server @ rmtpassword = 'Access password' -- password of the user/login name used to connect to the linked server -- select top 10 * from [222]. pwms. pwms. bas_tb_sku -- View link Server Information select name, product, provider, data_source, query_timeout, lazy_schema_validation, is_remote_login_enabled, is_rpc_out_enabledfrom sys. serverswhere is_linked = 1 -- configure the link Server property exec sp_serveroption 'Alias ', 'name', 'remote server ip' -- mark the storage EX EC sp_serveroption @ server = 'server' -- name of the linked server in the Configuration Attribute, @ optname = 'option _ name' -- option to be configured, @ optvalue = 'option _ value' -- new option value -- parameter description collation compatible. Since SQL Server no longer needs to compare character columns between data sources, it assumes that they are the same sorting rules, this improves the performance of collation name. If you have enabled use remote collation and are not a data source of SQL Server, collation name specifies the name of the remote Server sorting rule. The name of this sorting rule must be the connect timeout supported by SQL Server, which specifies the number of seconds it will try to connect to the linked Server before the timeout occurs. If the value is "0", the server value of sp _ configure's remote query timeout is used as the default value data access. If enabled, the distributed query is allowed to access lazy schema validation. If set to true, the architecture does not detect remote tables at the beginning of the query. Although this reduces the load on remote queries, if the architecture changes and you do not perform architecture detection, for example, the objects referenced in the query cannot communicate with the query command, an error query timeout is generated to specify the query wait timeout value (in seconds ). If the value is 0, query wait options use sp_configure value rpc to enable remote Procedure Call from server rpc out enable remote Procedure Call to server use remote collation specify to use remote server sorting rule (true) or the local server sorting rule (false) -- delete the link server property exec sp_dropserver '4' -- mark the store EXEC sp_dropserver @ server = 'server' -- Name of the linked Server deleted from the SQL server instance, @ droplogins = 'droplogins' -- If droplogins is specified, You need to delete the login name ing before deleting the linked server -- view the link login name select s. name parameter servername, s. data_source implements server_source, s. is_linked, s. modify_date, ll. remote_name, ll. local_principal_id, ll. uses_self_credential, p. name localLoginNamefrom sys. performance_logins llinner join sys. servers s on s. server_id = ll. server_idleft join sys. server_principals p on p. principal_id = ll. local_principal_idwhere s. is_linked = 1 -- delete the connection Server login name exec ing exec sp_droplinkedsrvlogin 'remote server ip ', NULL -- mark the storage EXEC sp_droplinkedsrvlogin @ rmtsrvname = 'remote Server ip' -- Name of the linked Server mapped by the login name @ locallogin = NULL -- this is the ing of the SQL Server login or Windows user deleted from the linked Server name

SQL

-- Simply set up a remote connection server for the remote machine. -- Assume that the IP address of the remote machine is 127.0.0.1 -- establish a connection to the server EXEC sp_add1_server '123456. 0.0.1 ',' SQL Server' -- create a EXEC ing between remote logins on the linked Server EXEC sp_add1_srvlogin '10. 0.0.222 ', 'false', 'sa', 'Architecture name', 'Password' -- query data select top 10 * from [127.0.0.1]. database Name. architecture name. table name -- view the select name, product, provider, data_source, query_timeout, lazy_schema_validation, is_remote_login_enabled, is_rpc_out_enabledfrom sys. serverswhere is_linked = 1 -- configure the link Server property to set the alias exec sp_serveroption 'remotecmp ', 'name', '123. 0.0.1 '-- query data select top 10 * from remotecmp. database Name. architecture name. table Name

The above Article is implemented through the stored procedure that comes with SQL. For other articles, refer to other articles of helper's house.