SQL外部链接解决方案 实例

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

 

ALTER    PROCEDURE usp_省物价局数据同步以及查询数据上传 
AS

--如果数据库里有一个叫Interface的链接连，就删除他
if exists(select * from  master.dbo.sysservers where srvname = 'Interface')
EXEC sp_dropserver 'Interface','droplogins'

--创建一个叫Interface的链接连接到acsess数据库
EXEC   sp_addlinkedserver    
        @server   =   'Interface',    
        @provider   =   'Microsoft.Jet.OLEDB.4.0',    
        @srvproduct   =   'OLE   DB   Provider   for   Jet',  
        @datasrc   =   'C:/Documents and Settings/Administrator/桌面/DBBackup/ShouFeiJK.mdb'

--设置链接的安全性为：不使用安全上下文进行 
EXEC sp_addlinkedsrvlogin 'Interface', 'false'

--这个允许调用链接服务器上的存储过程
EXEC   sp_serveroption   'Interface','rpc out','true'

 

--将项目价格表的数据更新到当前数据库中的tbl_诊治字典
UPDATE tbl_诊治字典

   set 医院级别 = b.YiYuanJB,收费查询编码 = b.DaYinBM,价格上浮比率 = b.ShangFuBL,
       价格下浮比率 = b.XiaFuBL,文件编号 = b.WenJianBM,项目基价 = b.XiangMuJJ,执行日期 = b.ZhiXingRQ,项目内涵 = b.XiangMuNH,
       其他内容 = b.QiTaNR,说明 = b.ShuoMing,JiaMiM_MAC = b.JiaMiM_MAC

FROM tbl_诊治字典 a, [Interface]...Price_I_ItemPrice b
WHERE a.物价局编码 = b.XiangMuBM AND a.执行日期 <> b.ZhiXingRQ

 

--查找接口表Price_I_TaskDetail中待处理的检查任务，
--再从数据库里查找到相应的记录并插入接口表Price_I_Check

DECLARE @Row int
--查找接口表中待处理的检查任务的条数
SELECT @Row = count(XiangMuBM) FROM [Interface]...Price_I_TaskDetail WHERE ShiFouDC = 0

IF @Row > 0
BEGIN

 DECLARE @xiangmubm nvarchar(11)
 DECLARE @renwubm nvarchar(11)
 DECLARE @shangchuansl int
 DECLARE @kaishirq nvarchar(10)
 DECLARE @jieshurq nvarchar(10)
 --创建游标
 DECLARE xiangmubm_get INSENSITIVE CURSOR FOR
 --查找接口表Price_I_TaskDetail中待处理的检查任务数据
 SELECT XiangMuBM,RenWuBM,ShangChuanSL,KaiShiRQ,JieShuRQ FROM [Interface]...Price_I_TaskDetail WHERE ShiFouDC = 0
 --打开游标
 open xiangmubm_get
 FETCH NEXT FROM xiangmubm_get INTO @xiangmubm,@renwubm,@shangchuansl,@kaishirq,@jieshurq
 WHILE @@fetch_status = 0
   BEGIN

 --根据接口表的检查任务提取当前数据库中的相关数据
 --然后返回到接口表
 EXEC(
 'INSERT INTO [Interface]...Price_I_Check(XiangMuBM,RenWuBM,DaYinBM,XiangMuMC,XiangMuJJ,ShangFuBL,XiaFuBL,YiYuanJB,BingRenBM,BingRenMC,KanBingRQ,WenJianBM,YiyuanDJ,JiaMiM_MAC,JiaMiM_HISMAC,ZhiXingRQ)
 SELECT DISTINCT TOP '+ @shangchuansl + '物价局编码,任务编码,收费查询编码,诊治名,项目基价,价格上浮比率,价格下浮比率, 医院级别,病人编号,姓名,起始时间,文件编号,单价,JiaMiM_MAC,JiaMiM_HISMAC,执行日期
 FROM
 (
 --挂号
 SELECT DISTINCT dbo.[tbl_诊治字典].收费查询编码, dbo.病人表.姓名, dbo.病人表.病人编号,
       dbo.[tbl_诊治字典].诊治名, dbo.[tbl_诊治字典].项目基价,
       dbo.[tbl_诊治字典].价格上浮比率, dbo.[tbl_诊治字典].价格下浮比率,
       dbo.[tbl_诊治字典].医院级别, dbo.[tbl_诊治字典].文件编号, dbo.[tbl_诊治字典].单价,
              cast(Year(dbo.[tbl_医嘱].起始时间) as nvarchar)+'''+ '-'+'''+Right('''+ '00'+'''+cast(Month(dbo.[tbl_医嘱].起始时间) as nvarchar),2)+'''+ '-'+'''+Right('''+ '00'+'''+cast(Day(dbo.[tbl_医嘱].起始时间) as nvarchar),2) as 起始时间,
              dbo.[tbl_诊治字典].物价局编码,dbo.[tbl_诊治字典].执行日期,'+ @renwubm +'as 任务编码,dbo.[tbl_诊治字典].JiaMiM_MAC, dbo.[tbl_诊治].JiaMiM_HISMAC
 FROM dbo.[tbl_诊治字典] INNER JOIN
       dbo.[tbl_诊治] ON
       dbo.[tbl_诊治字典].诊治字典ID = dbo.[tbl_诊治].诊治字典ID INNER JOIN
       dbo.[tbl_医嘱] ON dbo.[tbl_诊治].医嘱ID = dbo.[tbl_医嘱].医嘱ID INNER JOIN
       dbo.挂号表 ON dbo.[tbl_医嘱].流水号 = dbo.挂号表.流水号 INNER JOIN
       dbo.病人表 ON dbo.挂号表.病人编号 = dbo.病人表.病人编号
 WHERE dbo.[tbl_医嘱].起始时间 >= CONVERT(datetime,'''+ @kaishirq +''') AND dbo.[tbl_医嘱].起始时间 <= CONVERT(datetime,'''+ @jieshurq +''')
 AND dbo.[tbl_诊治字典].物价局编码 = ''' + @xiangmubm + '''
 
 UNION ALL
 
 --住院
 SELECT DISTINCT dbo.[tbl_诊治字典].收费查询编码, dbo.病人表.姓名, dbo.病人表.病人编号,
       dbo.[tbl_诊治字典].诊治名, dbo.[tbl_诊治字典].项目基价,
       dbo.[tbl_诊治字典].价格上浮比率, dbo.[tbl_诊治字典].价格下浮比率,
       dbo.[tbl_诊治字典].医院级别, dbo.[tbl_诊治字典].文件编号, dbo.[tbl_诊治字典].单价,
       cast(Year(dbo.[tbl_医嘱].起始时间) as nvarchar)+'''+ '-'+'''+Right('''+ '00'+'''+cast(Month(dbo.[tbl_医嘱].起始时间) as nvarchar),2)+'''+ '-'+'''+Right('''+ '00'+'''+cast(Day(dbo.[tbl_医嘱].起始时间) as nvarchar),2) as 起始时间,
              dbo.[tbl_诊治字典].物价局编码 ,dbo.[tbl_诊治字典].执行日期,'+ @renwubm + 'as 任务编码,dbo.[tbl_诊治字典].JiaMiM_MAC, dbo.[tbl_诊治].JiaMiM_HISMAC
 FROM dbo.[tbl_诊治字典] INNER JOIN
       dbo.[tbl_诊治] ON
       dbo.[tbl_诊治字典].诊治字典ID = dbo.[tbl_诊治].诊治字典ID INNER JOIN
       dbo.[tbl_医嘱] ON dbo.[tbl_诊治].医嘱ID = dbo.[tbl_医嘱].医嘱ID INNER JOIN
       dbo.tblVisit ON dbo.[tbl_医嘱].VisitId = dbo.tblVisit.VisitId INNER JOIN
       dbo.病人表 ON dbo.tblVisit.PatientId = dbo.病人表.病人编号
 WHERE dbo.[tbl_医嘱].起始时间 >= CONVERT(datetime,'''+ @kaishirq +''') AND dbo.[tbl_医嘱].起始时间 <= CONVERT(datetime,'''+ @jieshurq +''')
 AND dbo.[tbl_诊治字典].物价局编码 = ''' + @xiangmubm + '''
 )AB '
        +'
        DECLARE @Rows int
        SELECT @Rows = COUNT(XiangMuBM) FROM [Interface]...Price_I_Check WHERE XiangMuBM = ''' + @xiangmubm + '''
        if @Rows = 0
         BEGIN
           INSERT INTO [Interface]...Price_I_Check(XiangMuBM,RenWuBM)
           VALUES ('''+@xiangmubm+''', '''+@renwubm+''')
         END'

 )

       --将已经查找的任务的ShiFouDC属性改为1，即已完成查找
       UPDATE [Interface]...Price_I_TaskDetail SET ShiFouDC = 1 WHERE XiangMuBM = @xiangmubm AND RenWuBM = @renwubm
 
 FETCH NEXT FROM xiangmubm_get INTO @xiangmubm,@renwubm,@shangchuansl,@kaishirq,@jieshurq

   END
 --关闭游标
 CLOSE xiangmubm_get
 DEALLOCATE xiangmubm_get

END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

 

  '绑定过往招标的信息
            Dim i As Integer
            '循环取出的药品的记录集
            For i = 3 To xTable.Columns.Count - 5
                '显示现在已招标的列
                GridView1.Columns(32 + 3 - i).Visible = True
                Dim chkTender As WebControls.CheckBoxField = GridView1.Columns(32 + 3 - i)
                '给已招标的列赋值
                chkTender.DataField = xTable.Columns(i).ColumnName
            Next