Using SMO for script out Defining objects from a SQL server database in .net in a parallel loop

Question

I use SMO for the script from my objects from the Sql server database using .Net code. But now I'm going through a sequential loop.

foreach(var table in TableCollection) { var stringCollection=table.Script(); } 

It is working fine. But when I convert the same loop into a Parallel.ForEach loop, like:

 Parallel.ForEach(TableCollection,table=> { var stringCollection=table.Script(); }); 

The script fails. Is there anyone who has used the same approach or some other approach to script objects from a parallel Sql server?

UPDATE:

I have not been able to process Parallel LOOP yet, but I used the code below:

  server.SetDefaultInitFields(true); 

This improves performance to some extent.

Answer 1

It seems that SMO was not built in a thread-safe manner. When you call Script() on a Table , it uses some kind of common state from its Server , so you cannot execute it on two tables from the same Server . But you can get around this by creating a new Server object for each Table :

 private static TableCollection GetTables() { Server server = new Server(…); Database database = server.Databases[…]; var tables = database.Tables; return tables; } … Parallel.For(0, GetTables().Count, i => { var stringCollection = GetTables()[i].Script(); … }); 

This will cause your queries to be parallel, but I don’t know if it will do it faster.

EDIT: If you want to create one Server for each thread, you can use Parallel.For() overload, which allows stream-based initialization. Sort of:

 Parallel.For(0, GetTables().Count, () => GetTables(), (i, _, tables) => { var stringCollection = tables[i].Script(); … return tables; }, tables => { }); 

Thus, each thread will have its own Server object, but only one.