使用链接服务器实现不同数据库服务器之间数据访问的一个存储过程
USE [database] --此database为即将创建存储过程的数据库
GO
/****** Object: StoredProcedure [dbo].[PR_PROCName] Script Date: 03/04/2009 12:51:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROCName]
AS
DECLARE @Sql VARCHAR(4000)
BEGIN
EXEC sp_addlinkedserver 'dbx','','SQLOLEDB','192.168.1.11'
EXEC sp_addlinkedsrvlogin 'dbx','false',null,'sa','saPassword'
SET @Sql=('SELECT TOP 10 * FROM [Database].[dbo].[TableName] ')
EXEC (@Sql)
EXEC sp_droplinkedsrvlogin 'dbx',null
EXEC sp_dropserver 'dbx'
END
GO
/****** Object: StoredProcedure [dbo].[PR_PROCName] Script Date: 03/04/2009 12:51:09 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE [dbo].[PROCName]
AS
DECLARE @Sql VARCHAR(4000)
BEGIN
EXEC sp_addlinkedserver 'dbx','','SQLOLEDB','192.168.1.11'
EXEC sp_addlinkedsrvlogin 'dbx','false',null,'sa','saPassword'
SET @Sql=('SELECT TOP 10 * FROM [Database].[dbo].[TableName] ')
EXEC (@Sql)
EXEC sp_droplinkedsrvlogin 'dbx',null
EXEC sp_dropserver 'dbx'
END