SQL 分组排序及命令行运行脚本的例子 PsTools
以下实例用来通过DeviceID分组然后DeviceTime排序 每次9个9个放进新表中:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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文件中:
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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
![](https://images.cnblogs.com/OutliningIndicators/ContractedBlock.gif)
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