INTELLIGENT WORK FORUMS
FOR COMPUTER PROFESSIONALS

Log In

Come Join Us!

Are you a
Computer / IT professional?
Join Tek-Tips Forums!
  • Talk With Other Members
  • Be Notified Of Responses
    To Your Posts
  • Keyword Search
  • One-Click Access To Your
    Favorite Forums
  • Automated Signatures
    On Your Posts
  • Best Of All, It's Free!

*Tek-Tips's functionality depends on members receiving e-mail. By joining you are opting in to receive e-mail.

Posting Guidelines

Promoting, selling, recruiting, coursework and thesis posting is forbidden.

Jobs

Sql Server 2012 display line number of specific bulk inserts along with rows affected

Sql Server 2012 display line number of specific bulk inserts along with rows affected

Sql Server 2012 display line number of specific bulk inserts along with rows affected

(OP)
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.

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?

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. 

Red Flag This Post

Please let us know here why this post is inappropriate. Reasons such as off-topic, duplicates, flames, illegal, vulgar, or students posting their homework.

Red Flag Submitted

Thank you for helping keep Tek-Tips Forums free from inappropriate posts.
The Tek-Tips staff will check this out and take appropriate action.

Reply To This Thread

Posting in the Tek-Tips forums is a member-only feature.

Click Here to join Tek-Tips and talk with other members!

Resources

Close Box

Join Tek-Tips® Today!

Join your peers on the Internet's largest technical computer professional community.
It's easy to join and it's free.

Here's Why Members Love Tek-Tips Forums:

Register now while it's still free!

Already a member? Close this window and log in.

Join Us             Close