dbdeploy set status 1 in changelog table for errored sql file

[manojsitapara]

I had following error in my sql script. (i.e. length of column is less than actual value in sql file)

warning 8152: String or binary data would be truncated.

MSBuild raised this error but dbdeploy set ScriptStatus column value as 1 instead of 3.

Can you please help to fix this issue?

[brunomlopes]

Hi,

Isn't that "just" a warning? If so, it doesn't cause the script to fail with an error.

[manojsitapara]

Thank you for reply !!!

I think MSBuild doesn't understand that if there is error during execution of Sql script. For any such type of errors like column is already exist in table, String data would be truncated etc. it always raised warning in MSBuild instead of error.

What I was expecting that whenever there is such type of error raised from sql file, dbDeploy should set ScriptStatus as 3 instead of 1, otherwise it consider that it is executed successfully.

Let me know if you need more information to understand the scenario. I will add here.

[geetmenon]

The behavior of DBDeploy is not consistent with regards to SQLServer warnings. For e.g. lets say there is as sql file with 2 sqls. And the first sql ran into a SQLServer warning.

Case A: If it runs into one of the 2 SQLServer warnings below for example: warning 2627: Violation of PRIMARY KEY constraint warning 8152: String or binary data would be truncated.

In this case, after the warning a) it proceeds to run the second sql statement within the this file b) and it sets the status of the sql file to 1 in changelog table

Case B: Where as, if it runs into one of the 2 SQLServer warnings below for example: warning 2705: Column names in each table must be unique warning 102: Incorrect syntax near

In this case, after the warning a) it does not run the 2nd sql b) and it sets the status of the sql file to 3 in changelog table

Would you be able to make the behavior consistent regardless of the SQLServer warning, preferably

• rollback if any data or schema change was made as part of the sqls in this file
• do not proceed to run the remaining sqls in the file
• set status to 3 in changelog table