Useful scrips for linked server

最新推荐文章于 2025-09-06 09:33:29 发布

weixin_30549175

最新推荐文章于 2025-09-06 09:33:29 发布

阅读量49

点赞数

CC 4.0 BY-SA版权

文章标签：数据库

原文链接：http://www.cnblogs.com/Roy_Zhou/archive/2010/05/20/1740259.html

本文介绍了一个用于创建远程登录存储过程的SQL脚本。该脚本首先检查并删除已存在的服务器别名，然后创建一个新的链接服务器，并设置使用SQL Server验证进行远程连接的身份验证。

IF OBJECT_ID('sp_RmtLogin','P') IS NOT NULL
	DROP PROC dbo.sp_RmtLogin
GO
/***************************************************************************
* Author:	Roy
* Date:		04/14/2010
* Test:		exec sp_RmtLogin xxxx, 'uid','pwd', 'Sql207'
***************************************************************************/
CREATE PROC dbo.sp_RmtLogin
(
	@rmtSrvName varchar(20),
	@rmtSrvUid varchar(20),
	@rmtSrvPwd varchar(20),
	@rmtSrvAlias varchar(20)
)
AS
BEGIN
	IF exists (select * from sys.servers where name = @rmtSrvAlias )	
	BEGIN
		EXEC  sp_dropserver  @rmtSrvAlias,'droplogins'
	END

	DECLARE @rmtConn_str VARCHAR(200)

	SET @rmtConn_str = 'SERVER=' + @rmtSrvName

	EXEC sp_addlinkedserver   
		@rmtSrvAlias,	--Remote server alias name in the local mapping table
		'',				--Product name
		'SQLOLEDB',		--Provider name 
		NULL,			--Data source name
		NULL,			--location
		@rmtConn_str,	--Connection string to the remote database server
		NULL			--Catelog
	EXEC sp_addlinkedsrvlogin     
		@rmtSrvAlias,	--Remote server alias in local mapping table
		'false',		--Indicates the authentication type is SQL Server Authentication, so need username and password.
		NULL,			--The local login name, if null means for all
		@rmtSrvUid,		--Username used to connect the remote server.
		@rmtSrvPwd		--Password used to connect the remote server.
END
GO


Select * from information_schema.routines
select * from sys.servers
select * from information_schema.tables
select * from information_schema.columns

转载于:https://www.cnblogs.com/Roy_Zhou/archive/2010/05/20/1740259.html