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 抓下来解决超时问题

posted on 2010-05-03 16:32 徐郞顾阅读(1258) 评论(0) 编辑收藏举报

刷新页面返回顶部

练一技,修百艺,而成于自然.

execute sql of linked server and execute procedure of remote

导航

公告