Handling errors when using ExecuteNonQuery

Let's say that I have a StringCollection named scripts that contains a bunch of DDL statements I want to execute. So I execute the statements using ExecuteNonQuery as follows:

ExecuteNonQuery(scripts)

If one of the DDL statements contained in the StringCollection returns an error, it means that all subsequent statements will not be executed. Even if I use try catch as follows:

try

{

db.ExecuteNonQuery(scripts);

}

catch (Exception ex)

{

Console.WriteLine("Error while recreating objects. See errorlog for more details\r\n");

tw.Write("Error while recreating objects: {0}\r\n", ex.GetBaseException().ToString());

tw.Flush();

}

Is there any way to keep executing the subsequent statements in the StringCollection even if some of the DDL statements return an error?

If you send them one at a time, handle the error that occurs, then send the next script, you could then continue after an error. DDL errors, however, always make me want to stop, because DML usually depends on the DDL being correct.

|||

Thanks for your answer Allen,

I tried what you suggested but I noticed that none of my stored procs kept their settings. For example:

I script my proc as follows:

if (u.Type == "StoredProcedure")

{

ScriptingOptions so = new ScriptingOptions();

StoredProcedure o = (StoredProcedure)svr.GetSmoObject(u);

so.SchemaQualify = true;

so.SchemaQualifyForeignKeysReferences = true;

so.Permissions = true;

so.ExtendedProperties = true;

so.EnforceScriptingOptions = true;

so.NoCollation = true;

foreach (string os in o.Script(so))

{

sc.Add(os.ToString());

}

}

If I try to execute my statement as follows:

foreach (string s in scripts)

{

db.ExecuteNonQuery(s);

}

Then two statementswill be sent to SQL Server and the ANSI_NULL Settings will not necessarily be maintained:

first statement:

SET ANSI_NULLS ON

Second statement:

CREATE PROCEDURE...

The problem being that if the setting for my second connection is SET ANSI_NULLS OFF then my proc will be created with the wrong settings.