Parallel processing of dynamic sql statements

• coreyg@rotellacapital.com

  SSC Enthusiast

  Points: 124

  More actions

  July 9, 2004 at 9:50 am

  #109087

  I want to execute several dynamic SQL statements in parallel, but I can't think of a way to do it.  This is a simplified version of what I am trying to do...

  create table tblDBNames ( vcName varchar( 100))

  declare @table table ( vcNAME varchar( 100))

  exec sp_addumpdevice 'disk', 'northwind', 'c:\northwind.bak'

  exec sp_addumpdevice 'disk','pubs', 'c:\pubs.bak'

  exec sp_addumpdevice 'disk','model', 'c:\model.bak'

  exec sp_addumpdevice 'disk','msdb', 'c:\msdb.bak'

  exec sp_addumpdevice 'disk','master', 'c:\master.bak'

  insert tblDBNames( vcName) select 'northwind'

  insert tblDBNames( vcName) select 'pubs'

  insert tblDBNames( vcName) select 'model'

  insert tblDBNames( vcName) select 'msdb'

  insert tblDBNames( vcName) select 'master'

  insert @table

       select 'backup database ' + vcName + ' to ' + vcName + ' with init' from tblDBNames

  select * from @table

  I want to execute all the statements returned from the query at once (in parallel).  Obviously, I could iterate through @table and run the statements one at a time, but that makes the server wait for the first one to be done before it starts the next one, and takes way too long.  I was using a DTS package with a bunch of T-SQL command objects in it, but that involves too much maintenance.

  It should be fairly straight-forward to do this, but I haven't been able to find an example of it.

• Allen Cui-55137

  SSC Guru

  Points: 51650

  More actions

  July 9, 2004 at 9:55 am

  #514123

  You have to submit backup statements from mulitiple sessions.

• coreyg@rotellacapital.com

  SSC Enthusiast

  Points: 124

  More actions

  July 9, 2004 at 9:59 am

  #514126

  Hmmm, I thought it would automatically run in their own sessions because it's in an exec() block.  That's not the case?  Plus, that's specific only to the example I'm using here, imagine it's a set of SELECT queries.

  I can think of many other uses for being able to run dynamic sql in parallel like this.  This is only one example.  There must be a straight-forward way to do it.