当前位置: 数据库>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