Trouble shooting a rather long sql script - over 12,000 lines that includes multiple bulk insert statements, truncation of temporary tables, etc.
After running the script, I receive multiple lines within the results indicating the number of rows affected for each of the bulk inserts. It appears that the number of rows affected may be lower than I would expect.
[Bold]Is it possible/feasible to modify the sql script to not only show the number of rows affected for the multiple bulk inserts but also display the start line number (or other useful information) for each specific bulk insert statement within the sql script?[/Bold]
This would assist in readily identifying the section of the sql script that may need revision.
Per preliminary research, it appears that the use of tracing may accomplish the objective as noted in the excerpt below. It appears that the use of tracing would require the use of another database...
However, I am not familiar with the advantages/disadvantages of tracing or of any other method that would accomplish the objective.
Alternatively, to readily identify the section that appears to not display a low number of rows affected I could just run the sql script a section at a time instead of running the entire sql script at once... Of course, this will be more time consuming.
Thanks in advance for any insight.
After running the script, I receive multiple lines within the results indicating the number of rows affected for each of the bulk inserts. It appears that the number of rows affected may be lower than I would expect.
[Bold]Is it possible/feasible to modify the sql script to not only show the number of rows affected for the multiple bulk inserts but also display the start line number (or other useful information) for each specific bulk insert statement within the sql script?[/Bold]
This would assist in readily identifying the section of the sql script that may need revision.
Per preliminary research, it appears that the use of tracing may accomplish the objective as noted in the excerpt below. It appears that the use of tracing would require the use of another database...
However, I am not familiar with the advantages/disadvantages of tracing or of any other method that would accomplish the objective.
Alternatively, to readily identify the section that appears to not display a low number of rows affected I could just run the sql script a section at a time instead of running the entire sql script at once... Of course, this will be more time consuming.
Thanks in advance for any insight.
Code:
The Execute with Trace capability uniquely enables you to log data related to SQL query execution independently of the main transaction. This is achieved by employing an external transaction that is logged to a database other than the one to which the main transaction is stored.
This approach enables you to perform logging of your executed SQL queries at all times, even if the execution has failed, or the main transaction has been rolled back.
You can trace the SQL query execution process using a set of 4 auxiliary SQL scripts. Two of these scripts are responsible for reporting on the process and result of the entire SQL script’s execution, whereas the other two are responsible for reporting on the individual SQL statements’ execution. They are:
•Script Start - Performs execution before the first SQL statement in the main SQL script.
•Script End - Performs execution after the last SQL statement is executed.
•Statement Start - Performs execution before each individual SQL statement within a SQL batch.
•Statement End - Performs execution after each individual SQL statement within a SQL batch.
Tracing allows you to identify the reasons for a SQL script having failed by re-executing a batch.
