SQL 分组排序及命令行运行脚本的例子 PsTools

以下实例用来通过DeviceID分组然后DeviceTime排序 每次9个9个放进新表中:

USE [LoadTestManager]
GO

/****** Object:  StoredProcedure [dbo].[uspSortDataForBattery]    Script Date: 12/30/2016 1:03:50 AM ******/
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO


CREATE PROCEDURE [dbo].[uspSortDataForBattery]
    
AS
BEGIN

declare @i int
declare @j int
select @j=count(1)/10 from  [dbo].[DataPointGeoBattery]
set @i=1

while @i < @j
begin

Insert INTO NewDataPointGeoBattery
select BB.[DeviceID]
      ,BB.[RequestID]
      ,BB.[DeviceTime]
      ,BB.[ReceiveTime]
      ,BB.[BatteryInternal]
      ,BB.[RowModifiedOn]
      ,BB.[RowCreatedOn]
from (select B.*,
               rank() over(partition by B.DeviceID order by B.DeviceTime) rk
          from [DataPointGeoBattery] B )BB
where BB.rk < 10;


DELETE from [DataPointGeoBattery] WHERE
 ID IN
(
 select BB.ID
 from (select B.*,
               rank() over(partition by B.DeviceID order by B.DeviceTime) rk
          from [DataPointGeoBattery] B )BB
 where BB.rk < 10);

set @i=@i+1

end


    SET NOCOUNT ON;
END

GO

 

以下示例通过SQLCMD命令行来运行脚本,并将运行的结果写到EndProcess.log文件中:

Call SetParameters

echo ==================== Create DataPoint Sort SP ==================== > EnvProcess.log
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForLoc.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForBatteryExt.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForLight.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForBattery.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForMotion.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForMotionInf.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForRssi.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForTemperature.sql" >> EnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "NewSPToSortData\uspSortDataForTemperatureExt.sql" >> EnvProcess.log 2>>&1

以下示例对PsTools中PsExec的应用,附上对PsTools中所有套件详解:http://blog.csdn.net/sysprogram/article/details/13001781

Call SetParameters

echo ==================== Sync time in all servers ==================== > CleanupEnvProcess.log
%PsExec% \\* cmd /c "NET START W32Time" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\* cmd /c "w32tm /resync" >> CleanupEnvProcess.log 2>>&1

echo ==================== Clean up AppFabric ==================== > CleanupEnvProcess.log
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "NET STOP AppFabricEventCollectionService" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "NET STOP AppFabricWorkflowManagementService" >> CleanupEnvProcess.log 2>>&1
SQLCMD /S %Tracks_DB_Server% -i "Scripts\CleanAppfabric.sql" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "NET START AppFabricEventCollectionService" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "NET START AppFabricWorkflowManagementService" >> CleanupEnvProcess.log 2>>&1

IF /i [%1] EQU [/q] GOTO SkipCleanLTDB
echo ==================== Clean up Load Test Database ==================== >> CleanupEnvProcess.log
SQLCMD /S %TestDB_Server% -i "Scripts\DropLoadTestDB.sql" >> CleanupEnvProcess.log 2>>&1
SQLCMD /S %TestDB_Server% -i "%LTResultsRepository%" >> CleanupEnvProcess.log 2>>&1

:SkipCleanLTDB
REM echo ==================== Reset SMTP Server ==================== >> CleanupEnvProcess.log
REM %PsExec% \\%SUP_Web_Server_IP% cmd /c "NET STOP SMTPSVC" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%SUP_Web_Server_IP% cmd /c "DEL /f /s /q C:\inetpub\mailroot" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%SUP_Web_Server_IP% cmd /c "NET START SMTPSVC" >> CleanupEnvProcess.log 2>>&1

echo ==================== Reset IIS Servers ==================== >> CleanupEnvProcess.log
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "IISReset /stop" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "DEL /f /s /q C:\inetpub\logs" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%Tracks_Server1_IP%,%Tracks_Server2_IP% cmd /c "IISReset /start" >> CleanupEnvProcess.log 2>>&1

REM echo ==================== Restart SQL Servers ==================== >> CleanupEnvProcess.log
REM %PsExec% \\%DB_Server_IP% cmd /c "NET STOP MSDTC" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET STOP SQLSERVERAGENT" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET STOP MSSQLSERVER" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET STOP %SUPDB_Agent_Service%" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET STOP %SUPDB_Server_Service%" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET START MSSQLSERVER" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET START SQLSERVERAGENT" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET START %SUPDB_Server_Service%" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET START %SUPDB_Agent_Service%" >> CleanupEnvProcess.log 2>>&1
REM %PsExec% \\%DB_Server_IP% cmd /c "NET START MSDTC" >> CleanupEnvProcess.log 2>>&1

echo ==================== Restart LoadTest SQL Servers ==================== >> CleanupEnvProcess.log
%PsExec% \\%TestDB_Server_IP% cmd /c "NET STOP SQLSERVERAGENT" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%TestDB_Server_IP% cmd /c "NET STOP MSSQLSERVER" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%TestDB_Server_IP% cmd /c "NET START MSSQLSERVER" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%TestDB_Server_IP% cmd /c "NET START SQLSERVERAGENT" >> CleanupEnvProcess.log 2>>&1

echo ==================== Restart Redis Service ==================== >> CleanupEnvProcess.log
%PsExec% \\%Test_Controller_Server_IP% cmd /c "NET STOP Redis" >> CleanupEnvProcess.log 2>>&1
%PsExec% \\%Test_Controller_Server_IP% cmd /c "NET START Redis" >> CleanupEnvProcess.log 2>>&1

REM echo ==================== Clean up Event Log ==================== >> CleanupEnvProcess.log
REM %PsExec% \\%AD1_Server_IP%,%AD2_Server_IP%,%CSWeb_Web1_Server_IP%,%CSWeb_Web2_Server_IP%,%CSWeb_App1_Server_IP%,%CSWeb_App2_Server_IP%,%SUP_Web_Server_IP%,%SUP_App1_Server_IP%,%SUP_App2_Server_IP%,%DB_Server_IP% -c Scripts\CleanEventLog.bat >> CleanupEnvProcess.log 2>>&1