execute sql of linked server and execute procedure of remote

前段时间写个合区合服的存储过程

发现直接连接服务器写sql 做交互不如 直接通过链接服务器调用存储过程。

原sql

USE [xxxx]
GO
/****** 对象:  StoredProcedure [dbo].[USP_UNION_AREA_SERVER]    脚本日期: 04/19/2010 09:43:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_UNION_AREA_SERVER]
(@GAMEID NVARCHAR(32) --- UNION GAME NAME
,@RAREAID NVARCHAR(32) ---RESOURCE AREAID
,@RSERVERID NVARCHAR(32) --RESOURCE SERVERID
,@TAREAID NVARCHAR(32)   -- TARGET AREAID
,@TSERVERID NVARCHAR(32) -- TARGET SERVERID
,@UPGRADETYPE TINYINT =1  --- 1 UNION AREA /SERVER 
,@TOTALBIZID NVARCHAR(4000) OUTPUT --- OUTPUT UPGRADE BIZOFFERID
)
AS
DECLARE @COUNT TINYINT,@tabnumber varchar(4),@tabname sysname,@TOTALBIZID_66 Nvarchar(4000),@TOTALBIZID_76 Nvarchar(4000);
IF OBJECT_ID(N'TEMPDB..#') IS NOT NULL
DROP TABLE # ;
CREATE TABLE #
(BIZOFFERID NVARCHAR(32));

/*获取tabnumber 转换进制并把进制转换成字符串并截取最后位*/

select  @tabnumber= right(sys.fn_varbintohexstr(cast(tablenumber as varbinary(255))),4)
   from [192.168.1.xx].sc5173.dbo.game WHERE ID=@GAMEID;

/* 获取需要更改游戏分表bizoffer 的表名*/

select @tabname='bizofferby'+@tabnumber;

/* 65 主表bkoffer*/

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END

/*65 BKOfferDB */

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BKOfferDB.dbo.BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END

/* 65 BKOfferAPI */

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BKOfferAPI.dbo.BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END

/* 51 BKOFFERDK*/
Begin
SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) [192.168.1.xx].BKOFFERDK.dbo.BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END
/*记录修改的bizofferid */
WAITFOR  DELAY '00:00:03'
INSERT INTO # SELECT ID FROM [192.168.1.51].BKOFFERDK.dbo.BIZOFFER WITH(NOLOCK)
 WHERE GameId=@GAMEID AND GAMEAREAID=@TAREAID AND GAMESERVERID=@TSERVERID ;
END

/* 76游戏分表searchoffer */ -- 改成存储过程在上面建
IF EXISTS( SELECT 1 FROM [192.168.1.xx].SearchOffer.SYS.TABLES WHERE NAME=@tabname)
BEGIN
  EXECUTE [192.168.1.xx].SearchOffer.dbo.[USP_UNION_AREA_SERVER_CATEGORY] @GAMEID,@RAREAID,@RSERVERID,@TAREAID,@TSERVERID,@UPGRADETYPE,@tabname,@TOTALBIZID_76 OUTPUT
END
/* 66 游戏分表searchoffer  */  -- 改成存储过程在上面建
IF EXISTS( SELECT 1 FROM [192.168.1.66].SearchOffer.SYS.TABLES WHERE NAME=@tabname)
BEGIN
 EXECUTE [192.168.1.xx].SearchOffer.dbo.[USP_UNION_AREA_SERVER_CATEGORY]  @GAMEID,@RAREAID,@RSERVERID,@TAREAID,@TSERVERID,@UPGRADETYPE,@tabname,@TOTALBIZID_66 OUTPUT
END
;
/* 递归输出修改的bizofferid  @TOTALBIZID_66 记录分表的bizofferid  @TOTALBIZID_76 记录76分表的bizofferid*/
WITH XWJ_X1
AS
(SELECT BIZOFFERID,PX=ROW_NUMBER()OVER(ORDER BY (SELECT 1))
 FROM #
),
XWJ_X2
AS
(SELECT CAST(BIZOFFERID AS NVARCHAR(4000)) AS BIZOFFERID ,PX FROM XWJ_X1  WHERE PX=1
 UNION ALL
 SELECT CAST(A.BIZOFFERID+','+B.BIZOFFERID AS NVARCHAR(4000)),A.PX
   FROM XWJ_X2 AS B
    INNER JOIN XWJ_X1 AS A ON B.PX=A.PX-1
)
SELECT @TOTALBIZID=BIZOFFERID FROM XWJ_X2 AS A WHERE PX=(SELECT MAX(PX) FROM XWJ_X1 AS B)  OPTION(MAXRECURSION 0);
SELECT @TOTALBIZID=@TOTALBIZID+'|66 '+@tabname+':'+'|76 '+@tabname+':' ;

其中这段

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) [192.168.1.xx].BKOFFERDK.dbo.BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END
/*记录修改的bizofferid */
WAITFOR  DELAY '00:00:03'
INSERT INTO # SELECT ID FROM [192.168.1.xx].BKOFFERDK.dbo.BIZOFFER WITH(NOLOCK)
 WHERE GameId=@GAMEID AND GAMEAREAID=@TAREAID AND GAMESERVERID=@TSERVERID ;
END

执行起来超时严重后经修改

USE [BKOffer]
GO
/****** Object:  StoredProcedure [dbo].[USP_UNION_AREA_SERVER]    Script Date: 05/03/2010 16:24:20 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[USP_UNION_AREA_SERVER]
(@GAMEID NVARCHAR(32) --- UNION GAME NAME
,@RAREAID NVARCHAR(32) ---RESOURCE AREAID
,@RSERVERID NVARCHAR(32) --RESOURCE SERVERID
,@TAREAID NVARCHAR(32)   -- TARGET AREAID
,@TSERVERID NVARCHAR(32) -- TARGET SERVERID
,@UPGRADETYPE TINYINT =1  --- 1 UNION AREA /SERVER 
,@OPLOGINID nvarchar(32)  ----operator
,@OPLOGINIP nvarchar(32) --- operator ip
)
AS
DECLARE @COUNT TINYINT,@tabnumber varchar(4),@tabname sysname,@TOTALBIZID_66 Nvarchar(4000),@TOTALBIZID_76 Nvarchar(4000),@TOTALBIZID nvarchar(4000),@TOTALBIZID_51 nvarchar(4000),@sumcount_51 int;
IF OBJECT_ID(N'TEMPDB..#') IS NOT NULL
DROP TABLE # ;
CREATE TABLE #
(BIZOFFERID NVARCHAR(32));

/*获取 tabnumber 转换16进制 并把16进制转换成字符串并截取最后4位 */

select  @tabnumber= right(sys.fn_varbintohexstr(cast(tablenumber as varbinary(255))),4)
   from [192.168.1.xx].sc5173.dbo.game WHERE ID=@GAMEID;

/* 获取需要更改游戏分表 bizoffer 的表名*/

select @tabname='bizofferby'+@tabnumber;

/* 65 主表 bkoffer*/

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的 bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END

/*65 BKOfferDB */

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BKOfferDB.dbo.BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的 bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID  ;
  END
  SET @COUNT=@COUNT+1;
END

/* 65 BKOfferAPI */

SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BKOfferAPI.dbo.BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的 bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID  ;
  END
  SET @COUNT=@COUNT+1;
END

/* 51 BKOFFERDK*/
EXECUTE [192.168.1.xx].BKOFFERDK.dbo.[USP_UNION_AREA_SERVER_51] @GAMEID,@RAREAID,@RSERVERID,@TAREAID,@TSERVERID,@UPGRADETYPE,@TOTALBIZID_51 output,@sumcount_51 output;

/* 76游戏分表 searchoffer */ -- 改成存储过程 在76 上面建
IF EXISTS( SELECT 1 FROM [192.168.1.xx].SearchOffer.SYS.TABLES WHERE NAME=@tabname)
BEGIN
  EXECUTE [192.168.1.xx].SearchOffer.dbo.[USP_UNION_AREA_SERVER_CATEGORY] @GAMEID,@RAREAID,@RSERVERID,@TAREAID,@TSERVERID,@UPGRADETYPE,@tabname,@TOTALBIZID_76 OUTPUT
END
/* 66 游戏分表 searchoffer  */  -- 改成存储过程 在66 上面建
IF EXISTS( SELECT 1 FROM [192.168.1.xx].SearchOffer.SYS.TABLES WHERE NAME=@tabname)
BEGIN
 EXECUTE [192.168.1.xx].SearchOffer.dbo.[USP_UNION_AREA_SERVER_CATEGORY]  @GAMEID,@RAREAID,@RSERVERID,@TAREAID,@TSERVERID,@UPGRADETYPE,@tabname,@TOTALBIZID_66 OUTPUT
END
;
/* 递归输出 修改的 bizofferid  @TOTALBIZID_66 记录66分表 的bizofferid  @TOTALBIZID_76 记录 76分表的 bizofferid*/
WITH XWJ_X1
AS
(SELECT BIZOFFERID,PX=ROW_NUMBER()OVER(ORDER BY (SELECT 1))
 FROM #
),
XWJ_X2
AS
(SELECT CAST(BIZOFFERID AS NVARCHAR(4000)) AS BIZOFFERID ,PX FROM XWJ_X1  WHERE PX=1
 UNION ALL
 SELECT CAST(A.BIZOFFERID+','+B.BIZOFFERID AS NVARCHAR(4000)),A.PX
   FROM XWJ_X2 AS B
    INNER JOIN XWJ_X1 AS A ON B.PX=A.PX-1
)
SELECT @TOTALBIZID=BIZOFFERID FROM XWJ_X2 AS A WHERE PX=(SELECT MAX(PX) FROM XWJ_X1 AS B)  OPTION(MAXRECURSION 0);
SELECT @TOTALBIZID=isnull(@TOTALBIZID+';','')+isnull(@TOTALBIZID_51,'')+'|66 '+@tabname+':'+'|76 '+@tabname+':' ;
insert into MergeAreaServerDataLog(ReportDate,GameId,AreaId,OldAreaId,ServerId,OldServerId,BizOfferSums,Bizofferdetail,OpLoginId,OpLoginIp)
select getdate(),@GAMEID,@TAREAID,@RAREAID,@TSERVERID,@RSERVERID,(select count(1) from #)+@sumcount_51,@TOTALBIZID,@OPLOGINID,@OpLoginIp;

替换成

/* 51 BKOFFERDK*/
EXECUTE [192.168.1.xx].BKOFFERDK.dbo.[USP_UNION_AREA_SERVER_51] @GAMEID,@RAREAID,@RSERVERID,@TAREAID,@TSERVERID,@UPGRADETYPE,@TOTALBIZID_51 output,@sumcount_51 output;

USP_UNION_AREA_SERVER_51 代码如下：

USE [xxxx]
GO
/****** 对象:  StoredProcedure [dbo].[USP_UNION_AREA_SERVER_51]    脚本日期: 05/03/2010 16:29:16 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

ALTER PROCEDURE [dbo].[USP_UNION_AREA_SERVER_51]
(@GAMEID NVARCHAR(32) --- UNION GAME NAME
,@RAREAID NVARCHAR(32) ---RESOURCE AREAID
,@RSERVERID NVARCHAR(32) --RESOURCE SERVERID
,@TAREAID NVARCHAR(32)   -- TARGET AREAID
,@TSERVERID NVARCHAR(32) -- TARGET SERVERID
,@UPGRADETYPE TINYINT =1  --- 1 UNION AREA /SERVER
,@TOTALBIZID nvarchar(4000) output
,@sumcount int output
)
AS
DECLARE @COUNT TINYINT,@tabnumber varchar(4)
IF OBJECT_ID(N'TEMPDB..#') IS NOT NULL
DROP TABLE # ;
CREATE TABLE #
(BIZOFFERID NVARCHAR(32));
/* 51 BKOFFERDK*/
SET @COUNT=0 ;
WHILE @COUNT<4
BEGIN
 SELECT @COUNT;
 WHILE @@ROWCOUNT>0
  BEGIN
    UPDATE TOP(100) BIZOFFER
      SET GAMESERVERID=@TSERVERID
        ,GAMEAREAID=@TAREAID
        ,LastModified=getdate()
 OUTPUT INSERTED.ID INTO #  /*记录修改的 bizofferid */
    WHERE GameId=@GAMEID AND GAMEAREAID=@RAREAID AND GAMESERVERID=@RSERVERID ;
  END
  SET @COUNT=@COUNT+1;
END
;
/* 递归输出 修改的 bizofferid  @TOTALBIZID_66 记录66分表 的bizofferid  @TOTALBIZID_76 记录 76分表的 bizofferid*/
WITH XWJ_X1
AS
(SELECT BIZOFFERID,PX=ROW_NUMBER()OVER(ORDER BY (SELECT 1))
 FROM #
),
XWJ_X2
AS
(SELECT CAST(BIZOFFERID AS NVARCHAR(4000)) AS BIZOFFERID ,PX FROM XWJ_X1  WHERE PX=1
 UNION ALL
 SELECT CAST(A.BIZOFFERID+','+B.BIZOFFERID AS NVARCHAR(4000)),A.PX
   FROM XWJ_X2 AS B
    INNER JOIN XWJ_X1 AS A ON B.PX=A.PX-1
)
SELECT @TOTALBIZID=BIZOFFERID FROM XWJ_X2 AS A WHERE PX=(SELECT MAX(PX) FROM XWJ_X1 AS B)  OPTION(MAXRECURSION 0);
select @sumcount=(select count(1) from #);

trace 抓下来解决超时问题