一个查询窗口并行运行SQL脚本

最新推荐文章于 2022-10-16 18:30:21 发布

KevinLiu

最新推荐文章于 2022-10-16 18:30:21 发布

阅读量1.5k

点赞数

分类专栏： SQL Server安全

SQL Server安全专栏收录该内容

7 篇文章 0 订阅

订阅专栏

在论坛看到有朋友想在一个窗口并行跑多条语句，但是没有好的办法。我从网上找到了下面的办法，创建一个数据库的Project然后运行就可以实现并行运行数据库脚本的功能，希望对大家有帮助。

Transact-SQL does not have a simple method to launch multiple parallel running scripts. The pmaster is a database project that enables you to launch multiple SQL scripts to run in parallel. Just after running a single setup script, you can program your SQL script like the following demo:

use AdventureWorks;

declare @rc int;

-- init before launch sqls
exec @rc = sp_exec_init;

if @rc=0 begin
-- The following sqls will run in parallel
exec sp_exec 'select @@servername waitfor delay ''00:00:10''';
exec sp_exec 'use AdventureWorks
select * from Person.Address
select @@servername waitfor delay ''00:00:10''';
exec sp_exec 'select @@servername waitfor delay ''00:00:10''';
exec sp_exec 'select @@servername waitfor delay ''00:00:10''';

-- Wait until all 'done'.
exec sp_exec_wait;

-- The following sqls will run in parallel
exec sp_exec 'select @@servername waitfor delay ''00:00:10''';
exec sp_exec 'select @@servername waitfor delay ''00:00:10''';

-- Wait until all 'done'.
exec sp_exec_wait;
end

-- end of the session
exec sp_exec_end;

Note: I find the web page's default encoding (Unicode) might change some white space characters in the script, so can cause the script fail to run.

Workaround: First change the page encoding to be "Western European (Windows)", secondly copy and paste the code to a SSMS window, then change the strange character (it's â€ˆ on my screen) to white space.

代码：

[Purpose] Enable to execute sql scripts inparallel. This is the all-in-one setup script

[Author] James Ma

[Disclaimer] The user of this code must takefull responsibility for any changes to the system. The author is

exempt from any liabilities if this scriptcould cause any damage.

[Setup] Don't change anything here and makesure you're sysadmin. Then simply run this script.

[Smoke Test] Go to the last page of thisscript, highlight the smoke test script and run.

[Uninstall] Go to the last page of thisscript, highlight the uninstallation script and run.

[Usage] Simply init, exec sqls, wait, and end.For example,

use AdventureWorks;

declare @rc int;

-- Call init before launch sqls

exec @rc=sp_exec_init

-- The following sqls will run in parallel

if @rc=0 begin

exec sp_exec 'select @@servername waitfordelay ''00:00:10'''

exec sp_exec 'use AdventureWorks

select * from Person.Address

select @@servername waitfor delay''00:00:10''',

exec sp_exec 'select @@servername waitfordelay ''00:00:10'''

-- Wait until all 'done'.

exec sp_exec_wait

exec sp_exec 'select @@servername waitfordelay ''00:00:10'''

end

exec sp_exec_end;

[Change History]

Date By Description

-------- ------------------------------------------

20090609 James Ma Version 1.0.

use master;

create database pmaster;

ALTER DATABASE [pmaster]SETRECOVERY SIMPLEWITH NO_WAIT

use pmaster;

EXEC sp_grantlogin[NTAuthority\System]

EXEC sp_addsrvrolemember@loginame= [NT Authority\System],@rolename= 'sysadmin'

EXEC sp_changedbowner'NT AUTHORITY\SYSTEM'

use master

ALTER DATABASE pmasterSET ENABLE_BROKER

alter database pmasterset trustworthy on

USE [pmaster]

/****** Object:Table [dbo].[sysparameter] Script Date: 06/11/2009 15:58:24 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

SET ANSI_PADDINGON

CREATE TABLE [dbo].[sysparameter](

[parameter_name] [varchar](512)NOT NULL,

[parameter_value] [varchar](max)NOT NULL

) ON [PRIMARY]

SET ANSI_PADDINGOFF

/****** Object:StoredProcedure [dbo].[p_printerror] Script Date: 06/11/2009 15:58:25 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE PROCEDURE [dbo].[p_printerror]

BEGIN

SET NOCOUNT ON;

-- Print errorinformation.

PRINT 'Error ' + CONVERT(varchar(50),ERROR_NUMBER())+

', Severity ' +CONVERT(varchar(5),ERROR_SEVERITY())+

', State ' + CONVERT(varchar(5),ERROR_STATE())+

', Procedure ' +ISNULL(ERROR_PROCEDURE(),'-') +

', Line ' + CONVERT(varchar(5),ERROR_LINE());

PRINT ERROR_MESSAGE();

END;

/****** Object:StoredProcedure [dbo].[_p_exec_dropqueue] Script Date: 06/11/2009 15:58:25******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[_p_exec_dropqueue]

@master_spid smallint=null

,@options varchar(1024)= ''

set nocount on

if@master_spid isnullset @master_spid=@@SPID;

declare @sql varchar(max)set @sql=replace('set nocount on;

IF EXISTS (SELECT* FROM sys.objects WHERE object_id =OBJECT_ID(N''[dbo].[_p_exec_worker_<id>]'') AND type in (N''P'',N''PC''))

DROP PROCEDURE[dbo].[_p_exec_worker_<id>]

IF EXISTS (SELECT* FROM sys.services WHERE name = N''//pmaster/exec/TargetService_<id>'')

DROP SERVICE[//pmaster/exec/TargetService_<id>];

IF EXISTS (SELECT* FROM sys.service_queues WHERE name =N''pmaster_exec_TargetQueue_<id>'')

DROP QUEUE[dbo].[pmaster_exec_TargetQueue_<id>];'

,'<id>',convert(varchar,@master_spid));

if @options like'%debug%' print @sql;

if @options notlike '%printonly%'exec(@sql);

return @@error;

/****** Object:StoredProcedure [dbo].[_p_exec_createqueue] Script Date: 06/11/2009 15:58:25******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[_p_exec_createqueue]

@master_spid smallint=null

,@worker_num smallint= 4

,@options varchar(1024)= ''

set nocount on

if@master_spid isnullset @master_spid=@@SPID;

declare @sql varchar(max)

select @sql=replace(replace(replace(ROUTINE_DEFINITION

,'dbo._p_exec_worker','dbo._p_exec_worker_'+convert(varchar,@master_spid))

,'[dbo].[_p_exec_worker]','dbo._p_exec_worker_'+convert(varchar,@master_spid))

,'dbo.pmaster_exec_TargetQueue','dbo.pmaster_exec_TargetQueue_'+convert(varchar,@master_spid))

from INFORMATION_SCHEMA.ROUTINESwhereROUTINE_SCHEMA='dbo'and ROUTINE_NAME='_p_exec_worker'

if @options like'%debug%' print @sql;

if @options notlike '%printonly%'exec(@sql);

set @sql=replace(replace('set nocount on;

CREATE QUEUEpmaster_exec_TargetQueue_<id> WITH STATUS=ON,

ACTIVATION(PROCEDURE_NAME=_p_exec_worker_<id>,MAX_QUEUE_READERS=<wn>,EXECUTEAS SELF);

CREATE SERVICE[//pmaster/exec/TargetService_<id>]

ON QUEUEpmaster_exec_TargetQueue_<id>([//pmaster/exec/Contract]);'

,'<id>',convert(varchar,@master_spid))

,'<wn>',convert(varchar,@worker_num));

if @options like'%debug%' print @sql;

if @options notlike '%printonly%'exec(@sql);

return @@error;

/****** Object:Table [dbo].[exec_master] Script Date: 06/11/2009 15:58:25 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE TABLE [dbo].[exec_master](

[master_spid] [smallint] NOT NULL,

[worker_num] [smallint] NOT NULL,

[create_time] [datetime] NOT NULL,

[dialog_handle] [uniqueidentifier] NULL,

CONSTRAINT[PK_exec_master] PRIMARY KEY CLUSTERED

(

[master_spid] ASC

)WITH(PAD_INDEX= OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS= ON,ALLOW_PAGE_LOCKS =ON) ON [PRIMARY]

) ON [PRIMARY]

/****** Object:Table [dbo].[exec_log] Script Date: 06/11/2009 15:58:25 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

SET ANSI_PADDINGON

CREATE TABLE [dbo].[exec_log](

[log_id] [int] IDENTITY(1,1)NOT NULL,

[log_spid] [smallint] NOTNULL,

[exec_queue_id] [bigint] NULL,

[log_msg] [varchar](max)NULL,

[log_time] [datetime] NOTNULL,

CONSTRAINT[PK_exec_log] PRIMARY KEYCLUSTERED

(

[log_id] ASC

)WITH(PAD_INDEX= OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS= ON,ALLOW_PAGE_LOCKS =ON) ON [PRIMARY]

) ON [PRIMARY]

SET ANSI_PADDINGOFF

/****** Object:StoredProcedure [dbo].[p_exec_cleanqueue] Script Date: 06/11/2009 15:58:25******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

create procedure [dbo].[p_exec_cleanqueue]

set nocount on

declare@conversation uniqueidentifier;

while exists(select* from sys.conversation_endpointswhere[state] in('CD','DI'))begin

set@conversation =(selecttop 1 conversation_handlefromsys.conversation_endpointswhere [state]in('CD','DI'))

end conversation @conversationwithcleanup

end

return @@error

/****** Object:UserDefinedFunction [dbo].[f_exec_option] Script Date: 06/11/2009 15:58:26******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE function [dbo].[f_exec_option](@optionsvarchar(max),@argvarchar(512))returns varchar(512)

begin

declare@startPos int,@endPosint

set @startPos=charindex(@arg,@options,1)

if @startPos=0return null

set @endPos=charindex(';',@options,@startPos)

if (@endPos=0)set @endPos=len(@options)+1

return substring(@options,@startPos+len(@arg)+1,(@endPos-@startPos-len(@arg)-1))

end

/****** Object:Table [dbo].[exec_queue] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE TABLE [dbo].[exec_queue](

[exec_queue_id] [bigint] IDENTITY(-9223372036854775808,1)NOT NULL,

[master_spid] [smallint] NOT NULL,

[worker_spid] [smallint] NULL,

[wait_type] [tinyint] NOTNULL,

[create_time] [datetime] NOT NULL,

[send_time] [datetime] NULL,

[worker_start_time] [datetime] NULL,

[worker_end_time] [datetime] NULL,

[return_code] [int] NULL,

[return_msg] [nvarchar](max)NULL,

CONSTRAINT[PK_exec_queue] PRIMARY KEY CLUSTERED

(

[exec_queue_id] ASC

)WITH(PAD_INDEX= OFF,STATISTICS_NORECOMPUTE =OFF,IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS= ON,ALLOW_PAGE_LOCKS =ON) ON [PRIMARY]

) ON [PRIMARY]

CREATE NONCLUSTEREDINDEX[IX_exec_queue_master_spid] ON [dbo].[exec_queue]

(

[master_spid] ASC

)WITH(PAD_INDEX= OFF,STATISTICS_NORECOMPUTE =OFF,SORT_IN_TEMPDB = OFF, IGNORE_DUP_KEY= OFF,DROP_EXISTING = OFF, ONLINE= OFF,ALLOW_ROW_LOCKS =ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]

/****** Object:StoredProcedure [dbo].[p_exec_log] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[p_exec_log]

@exec_queue_id bigint

,@msg nvarchar(max)

set nocount on

insert dbo.exec_log(log_spid,exec_queue_id,log_msg,log_time)

select @@SPID,@exec_queue_id,@msg,GETDATE()

return @@error

/****** Object:StoredProcedure [dbo].[_p_exec_worker] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[_p_exec_worker]

set nocount on

DECLARE@RecvReqDlgHandle UNIQUEIDENTIFIER;

DECLARE@RecvReqMsg NVARCHAR(max);set @RecvReqMsg=null

DECLARE@RecvReqMsgName sysname;

BEGIN TRANSACTION;

WAITFOR(RECEIVETOP(1)

@RecvReqDlgHandle =conversation_handle,

@RecvReqMsg =message_body,

@RecvReqMsgName =message_type_name

FROM dbo.pmaster_exec_TargetQueue

), TIMEOUT 1000;

COMMIT TRANSACTION;

IF (@RecvReqMsgName=N'//pmaster/exec/RequestMessage')AND(@RecvReqMsg is not null)

and(@RecvReqMsglike '--//pmaster/exec/request:%')

begin

declare@header varchar(2048),@qidbigint,@msg nvarchar(max),@rcint;

----//pmaster/exec/request:is the prefix

set @header=substring(@RecvReqMsg,26,charindex(N'--',@RecvReqMsg,26)-26);

set @qid=dbo.f_exec_option(@header,'@exec_queue_id');

update dbo.exec_queuesetworker_spid=@@SPID,worker_start_time=GETDATE()where exec_queue_id=@qid;

begin try

--execdbo.p_exec_log @RecvReqMsg

exec @rc=sp_executesql@RecvReqMsg;

set @msg='';

end try

begin catch

select @rc=ERROR_NUMBER(),@msg=ERROR_MESSAGE();

exec dbo.p_exec_log@qid,@RecvReqMsg

end catch

update dbo.exec_queuesetreturn_code=@rc,return_msg=@msg,worker_end_time=GETDATE()whereexec_queue_id=@qid;

end

return @@error

/****** Object:StoredProcedure [dbo].[_p_exec_clean] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[_p_exec_clean]

@master_spid smallint=null

,@options varchar(1024)= ''

set nocount on

if@master_spid isnullset @master_spid=@@SPID;

declare@conversation uniqueidentifier

select@conversation=dialog_handlefrom dbo.exec_masterwhere master_spid=@master_spid;

if@conversation isnotnull end conversation @conversation withcleanup;

update dbo.exec_mastersetdialog_handle=nullwheremaster_spid=@master_spid

exec dbo._p_exec_dropqueue@master_spid,@options

delete from dbo.exec_queuewhere master_spid=@master_spid

delete from dbo.exec_masterwhere master_spid=@master_spid

while exists(select* from sys.conversation_endpointswhere[state] in('CD','DI'))begin

set@conversation =(selecttop 1 conversation_handlefromsys.conversation_endpointswhere [state]in('CD','DI'))

end conversation @conversationwithcleanup

end

return @@error

/****** Object:StoredProcedure [dbo].[p_exec] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[p_exec]

[Purpose] Execute sql in parallel

[Owner] James Ma

[Usage in an example] Simply init, exec sqls,and wait.

use AdventureWorks;

declare @rc int,@db varchar(256) set@db=DB_NAME();

-- init before launch sqls

exec @rc=sp_exec_init

-- The following sqls will run in parallel

if @rc=0 begin

exec sp_exec 'select @@servername waitfordelay ''00:00:10''',0,@db

exec sp_exec 'use AdventureWorks

select * from Person.Address

select @@servername waitfor delay ''00:00:10''',1

exec sp_exec 'select @@servername waitfordelay ''00:00:10''',2

-- Wait until all 'done'.

exec sp_exec_wait

end

-- end the exec in the end

exec sp_exec_end

[Change History]

Date By Description

-------- ------------------------------------------

20090609 James Ma Version 1.0.

@sql nvarchar(max)

,@wait_type tinyint= 2 -- 0: send only and no wait;

-- 1: wait tillworker starts;

-- 2: wait tillworker finishes.

,@db varchar(256)= null

set nocount on

declare@master_spid smallintset@master_spid=@@SPID;

declare @rc intset @rc=null;

DECLARE@InitDlgHandle UNIQUEIDENTIFIER,@exec_queue_id bigint,@header varchar(2048)

begin try

begin transaction;

insert dbo.exec_queue(master_spid,wait_type,create_time)

select@master_spid,@wait_type,GETDATE();

set@exec_queue_id=SCOPE_IDENTITY();

declare @tmp nvarchar(2048)

select@InitDlgHandle=dialog_handlefrom dbo.exec_masterwhere master_spid=@master_spid

if@InitDlgHandle isnullbegin

set @tmp=replace('set nocount on

BEGIN DIALOG@InitDlgHandle

FROM SERVICE [//pmaster/exec/InitiatorService]

TO SERVICEN''//pmaster/exec/TargetService_<Id>''

ON CONTRACT [//pmaster/exec/Contract]

WITH ENCRYPTION = OFF;','<Id>',@master_spid);

exec sp_executesql@tmp,N'@InitDlgHandleUNIQUEIDENTIFIER output',@InitDlgHandleoutput;

update dbo.exec_mastersetdialog_handle=@InitDlgHandlewhere master_spid=@master_spid

end;

set @sql=replace(replace(replace(replace(replace(

N'--//pmaster/exec/request:@exec_queue_id=<qid>;@master_spid=<id>;@wait_type=<wt>--

use [<db>];

<sql>'

,'<id>',convert(varchar,@master_spid)),'<qid>',CONVERT(varchar,@exec_queue_id)),'<wt>',CONVERT(varchar,@wait_type))

,'<db>',isnull(@db,DB_NAME())),'<sql>',@sql);

SEND ON CONVERSATION@InitDlgHandle MESSAGE TYPE[//pmaster/exec/RequestMessage](@sql);

update dbo.exec_queuesetsend_time=getdate()whereexec_queue_id=@exec_queue_id;

commit transaction;

set @rc=@@error;

end try

begin catch

exec dbo.p_printerror;

set @rc=ERROR_NUMBER();

IF XACT_STATE()<> 0 ROLLBACK TRANSACTION;

end catch

return @rc

/****** Object:StoredProcedure [dbo].[p_exec_wait] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[p_exec_wait]

set nocount on

declare@master_spid smallintset@master_spid=@@SPID

while exists(select* from dbo.exec_queuewheremaster_spid=@master_spid

and((wait_type=0and worker_spid isnull) or

(wait_type=1andworker_start_time isnull)or

(wait_type=2andworker_end_time isnull)))

waitfor delay'00:00:03';

exec dbo.p_exec_cleanqueue;

return @@error

/****** Object:StoredProcedure [dbo].[p_exec_reset] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[p_exec_reset]

set nocount on

declare @rc intset @rc=null;

BEGIN TRY

declare@master_spid smallint

BEGIN TRANSACTION;

declare curId cursorlocal forward_only

for select master_spidfromdbo.exec_master

open curId

fetch next from curId into @master_spid

while (@@FETCH_STATUS=0)begin

exec dbo._p_exec_clean@master_spid,''

fetch next from curId into @master_spid

end

COMMIT TRANSACTION;

set @rc=0;

END TRY

BEGIN CATCH

exec dbo.p_printerror;

set @rc = -1;

IF XACT_STATE()<> 0 ROLLBACK TRANSACTION;

END CATCH;

exec dbo.p_exec_cleanqueue;

return @rc

/****** Object:StoredProcedure [dbo].[p_exec_init] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[p_exec_init]

@worker_num smallint= 4

set nocount on

declare@master_spid smallintset@master_spid=@@SPID

exec dbo.p_exec_cleanqueue;

--last sessionhas not ended yet, cannot proceed!

if exists(select* from dbo.exec_queuewheremaster_spid=@master_spidand worker_spid isnull)

begin

RAISERROR ('Previous running has notfinished, cannot init the pmaster exec system!',16,1);

return -1;

end

if (@worker_num<1or @worker_num>16)

begin

RAISERROR ('@worker_num parameter mustbe between 1 and 16.',16,1);

return -1;

end

declare @rc intset @rc=null

begin try

begin transaction;

exec dbo._p_exec_clean@master_spid,''

exec dbo._p_exec_createqueue@master_spid,@worker_num,'';

insert dbo.exec_master(master_spid,worker_num,create_time)

select@master_spid,@worker_num,GETDATE();

commit transaction;

set @rc=@@error;

end try

begin catch

exec dbo.p_printerror;

set @rc=-1;

IF XACT_STATE()<> 0 ROLLBACK TRANSACTION;

end catch

return @rc;

/****** Object:StoredProcedure [dbo].[p_exec_end] Script Date: 06/11/2009 15:58:26 ******/

SET ANSI_NULLSON

SET QUOTED_IDENTIFIERON

CREATE procedure [dbo].[p_exec_end]

set nocount on

declare@master_spid smallintset@master_spid=@@SPID

while exists(select* from dbo.exec_queuewheremaster_spid=@master_spidand worker_spid isnull)

waitfor delay'00:00:03';

begin try

begin transaction;

exec dbo._p_exec_clean@master_spid,'';

commit transaction;

end try

begin catch

exec dbo.p_printerror;

IF XACT_STATE()<> 0 ROLLBACK TRANSACTION;

return Error_number();

end catch

exec dbo.p_exec_cleanqueue;

return @@error

/****** Object:ForeignKey [FK_exec_queue_exec_master] Script Date: 06/11/2009 15:58:26 ******/

ALTER TABLE [dbo].[exec_queue]WITHCHECK ADD CONSTRAINT[FK_exec_queue_exec_master] FOREIGNKEY([master_spid])

REFERENCES [dbo].[exec_master]([master_spid])

ALTER TABLE [dbo].[exec_queue]CHECKCONSTRAINT[FK_exec_queue_exec_master]

USE pmaster

CREATE MESSAGETYPE[//pmaster/exec/RequestMessage] VALIDATION = NONE;

CREATE MESSAGETYPE[//pmaster/exec/ReplyMessage] VALIDATION = NONE;

CREATE CONTRACT [//pmaster/exec/Contract](

[//pmaster/exec/RequestMessage] SENT BY INITIATOR,

[//pmaster/exec/ReplyMessage] SENT BY TARGET);

CREATE QUEUE pmaster_exec_TargetQueueWITH STATUS=ON,

ACTIVATION (PROCEDURE_NAME=_p_exec_worker,MAX_QUEUE_READERS=16,EXECUTEAS SELF );

CREATE SERVICE [//pmaster/exec/TargetService]ON QUEUEpmaster_exec_TargetQueue([//pmaster/exec/Contract]);

CREATE QUEUE pmaster_exec_InitiatorQueue;

CREATE SERVICE [//pmaster/exec/InitiatorService]ON QUEUEpmaster_exec_InitiatorQueue;

use master

create procedure dbo.sp_exec_init

@worker_num smallint= 4

set nocount on;

declare @rc int;

exec @rc=pmaster.dbo.p_exec_init@worker_num;

return @rc;

EXECUTEsp_ms_marksystemobject'sp_exec_init'

create procedure dbo.sp_exec

[Purpose] Execute sql in parallel

[Owner] James Ma

[Usage in an example] Simply init, exec sqls,and wait.

use AdventureWorks;

declare @rc int,@db varchar(256) set@db=DB_NAME();

-- Call init before launch sqls

exec @rc=sp_exec_init

-- The following sqls will run in parallel

if @rc=0 begin

exec sp_exec 'select @@servername waitfordelay ''00:00:10''',0,@db

exec sp_exec 'use AdventureWorks

select * from Person.Address

select @@servername waitfor delay ''00:00:10''',1

exec sp_exec 'select @@servername waitfordelay ''00:00:10''',2

-- Wait until all 'done'.

exec sp_exec_wait;

end

exec sp_exec_end;

[Change History]

Date By Description

-------- ------------------------------------------

20090609 James Ma Version 1.0.

@sql nvarchar(max)

,@wait_type tinyint= 2 -- 0: send only and no wait;

-- 1: wait tillworker starts;

-- 2: wait tillworker finishes.

,@db varchar(256)= null

set nocount on;

declare @rc int;

exec @rc=pmaster.dbo.p_exec@sql,@wait_type,@db;

return @rc;

EXECUTEsp_ms_marksystemobject'sp_exec'

create procedure dbo.sp_exec_wait

set nocount on;

declare @rc int;

exec @rc=pmaster.dbo.p_exec_wait;

return @rc;

EXECUTEsp_ms_marksystemobject'sp_exec_wait'

create procedure dbo.sp_exec_end

set nocount on;

declare @rc int;

exec @rc=pmaster.dbo.p_exec_end;

return @rc;

EXECUTEsp_ms_marksystemobject'sp_exec_end'

/* Smoke Test

use tempdb

declare @rc int

exec@rc=sp_exec_init 8

if (@rc=0) begin

exec sp_exec 'select @@servername waitfordelay ''00:00:30'''

exec sp_exec_wait

exec sp_exec 'select @@servername waitfordelay ''00:00:30'''

exec sp_exec 'select @@servername waitfor delay''00:00:30'''

exec sp_exec_wait

end

exec sp_exec_end

--use anotherwindow to monitor the progress

select * frompmaster.sys.conversation_endpoints

select * frompmaster.dbo.exec_queue

select * frompmaster.dbo.exec_log

/* Uninstall& Cleanup

[Disclaimer] The user of this code must takefull responsibility for any changes to the system. The author is

exempt from any liability if this script couldcause any damage.

[Warning] Are you sure of what you are goingto drop?

------use master

------go

------dropprocedure dbo.sp_exec

------dropprocedure dbo.sp_exec_init

------dropprocedure dbo.sp_exec_wait

------dropprocedure dbo.sp_exec_end

------go

------DROPDATABASE [pmaster]

------GO

原文来自：http://www.sqlservercentral.com/scripts/T-SQL/68002/

KevinLiu

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
一个查询窗口并行运行SQL脚本

在论坛看到有朋友想在一个窗口并行跑多条语句，但是没有好的办法。我从网上找到了下面的办法，创建一个数据库的Project然后运行就可以实现并行运行数据库脚本的功能，希望对大家有帮助。 Transact-SQL does not have a simple method to launch multiple parallel running scripts. The pmaster is a
复制链接

扫一扫

专栏目录