当前位置: 数据库>sqlserver
sql数据库新增链接服务器
来源: 互联网 发布时间：2014-08-29
本文导语: 如何在sql数据库中新增链接服务器？ 例子，创建链接服务器的sql语句。 代码示例: if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[data] GO SET QUOTED_IDEN...
如何在sql数据库中新增链接服务器？
例子，创建链接服务器的sql语句。
代码示例:
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[data]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[data]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure [dbo].[data]
as
begin
SET NOCOUNT ON;
--判断是否存在该链接服务器
IF NOT EXISTS(SELECT 1 FROM master.dbo.sysservers WHERE SRVNAME='YGL14')
BEGIN
EXEC SP_ADDLINKEDSERVER 'YGL14','','SQLOLEDB','10.16.144.14'
EXEC sp_addlinkedsrvlogin 'YGL14','false',null,'sa','yplserver'
EXEC master.dbo.sp_serveroption @server=N'YGL14',@optname=N'rpc out',@optvalue=N'true'
END
declare @isexists varchar(100)
set @isexists=(select name from dbo.sysobjects where id = object_id(N'[dbo].[BarCodeMake]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--判断是否存在该表
if @isexistsnull AND @isexists''
BEGIN
DELETE FROM yplmis.dbo.BarCodeMake
set IDENTITY_INSERT BarCodeMake on
INSERT INTO BarCodeMake(MakeNo,Modedate,customerid,customername,ItemName,ColourName,labelColour,ModeId,Qty,Standlong,HandleNo,HandleQty,Remark,Handleman,Dc,RCPQTY)
SELECT MakeNo,Modedate,customerid,customername,ItemName,ColourName,labelColour,ModeId,Qty,Standlong,HandleNo,HandleQty,Remark,Handleman,Dc,RCPQTY
FROM YGL14.YPLMIS.DBO.BARCODEMAKE AS MIS
set IDENTITY_INSERT BarCodeMake off
END
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
drop procedure [dbo].[data]
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
create procedure [dbo].[data]
as
begin
SET NOCOUNT ON;
--判断是否存在该链接服务器
IF NOT EXISTS(SELECT 1 FROM master.dbo.sysservers WHERE SRVNAME='YGL14')
BEGIN
EXEC SP_ADDLINKEDSERVER 'YGL14','','SQLOLEDB','10.16.144.14'
EXEC sp_addlinkedsrvlogin 'YGL14','false',null,'sa','yplserver'
EXEC master.dbo.sp_serveroption @server=N'YGL14',@optname=N'rpc out',@optvalue=N'true'
END
declare @isexists varchar(100)
set @isexists=(select name from dbo.sysobjects where id = object_id(N'[dbo].[BarCodeMake]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
--判断是否存在该表
if @isexistsnull AND @isexists''
BEGIN
DELETE FROM yplmis.dbo.BarCodeMake
set IDENTITY_INSERT BarCodeMake on
INSERT INTO BarCodeMake(MakeNo,Modedate,customerid,customername,ItemName,ColourName,labelColour,ModeId,Qty,Standlong,HandleNo,HandleQty,Remark,Handleman,Dc,RCPQTY)
SELECT MakeNo,Modedate,customerid,customername,ItemName,ColourName,labelColour,ModeId,Qty,Standlong,HandleNo,HandleQty,Remark,Handleman,Dc,RCPQTY
FROM YGL14.YPLMIS.DBO.BARCODEMAKE AS MIS
set IDENTITY_INSERT BarCodeMake off
END
end
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
您可能感兴趣的文章:
本站(WWW.)旨在分享和传播互联网科技相关的资讯和技术，将尽最大努力为读者提供更好的信息聚合和浏览方式。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。
本站(WWW.)站内文章除注明原创外，均为转载、整理或搜集自网络。欢迎任何形式的转载，转载请注明出处。