前段时间,工作的需要,写了一个读取异地数据库中某几个表数据的功能,在此与大家分享。实现该功能写了三个存储过程,在作业中调用就可以了。
注意:添加连接,以及删除连接必须独立写成一个存储过程。
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportDataAddServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ImportDataAddServer] go --exec ImportDataAddServer /*-------------------------- --连接香港服务器 --yangys 2008-06-12 */ create proc ImportDataAddServer as --添加连接 EXEC sp_addlinkedserver @server = 'ImageUploadConnection', @srvproduct = '', @provider='SQLOLEDB', @datasrc='127.127.1.1' --添加添登录 EXEC sp_addlinkedsrvlogin 'ImageUploadConnection', 'false', NULL, 'yangys', '123456'
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportData]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ImportData] go /*-------------------------- --从香港服务器导入最新数据 --yangys */ create proc ImportData as set nocount on declare @ErrorCode int declare @Rowcount int declare @DateString varchar(20)--时间字符串 set @DateString=convert(varchar(20),getdate(),120) begin transaction --处理图片信息 --读取新的图片信息到临时表 SELECT a.pk_style_image, a.fk_item_code, a.width, a.height, a.Design_image, a.product_image, a.design_thumbnail, a.product_thumbnail, a.rowid into #tempImageNew FROM [ImageUploadConnection].erp_style_image.dbo.style_image a where not exists(select 1 from style_image b where a.fk_item_code=b.fk_item_code and a.pk_style_image=b.style_imageIdImport ) select @ErrorCode=@@error,@Rowcount=@@rowcount if @ErrorCode<>0 begin --记录错误信息 insert OPENDATASOURCE('MICROSOFT.JET.OLEDB.4.0','Text;HDR=Yes;DATABASE=C:/')...[ImportDataLog#txt] values(@DateString+N' 读取新图片信息时发生错误!') end --等等 --[ImageUploadConnection].erp_style_image.dbo.style_image 读取erp图库中的图片信息if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[ImportDataDropServer]') and OBJECTPROPERTY(id, N'IsProcedure') = 1) drop procedure [dbo].[ImportDataDropServer] go --exec ImportDataDropServer /*-------------------------- --删除数据库连接 --yangys */ create proc ImportDataDropServer as --删除登录 EXEC sp_droplinkedsrvlogin 'ImageUploadConnection', null --删除连接 exec sp_dropserver 'ImageUploadConnection'