What SQL is being sent from a SqlCommand object

Question

I have a SqlCommand object on my c# based asp.net page. The SQL and the passed parameters are working the majority of the time. I have one case that is not working, I get the following error:

String or binary data would be truncated. The statement has been terminated.

I understand the error and but all the columns in the database should be long enough to hold everything being sent.

My questions,

Is there a way to see what the actual SQL being sent to the database is from SqlCommand object? I would like to be able to email the SQL when an error occurs.

Thanks, Justin

Answer 1

You need to use the SQL Server Profiler to watch what comes from the application. I believe it can show you the SQL and the parameters, which you will need to see.

Answer 2

Check out this question it should provide what you are looking for.

http://stackoverflow.com/questions/265192/how-to-get-the-generated-sql-statment-from-a-sqlcommand-object

Answer 3

While you will not be able to plug is into something like Enterprise Manager to run it works for logging.

public static string ToReadableString(this IDbCommand command)
{
    StringBuilder builder = new StringBuilder();
    if (command.CommandType == CommandType.StoredProcedure)
        builder.AppendLine("Stored procedure: " + command.CommandText);
    else
        builder.AppendLine("Sql command: " + command.CommandText);
    if (command.Parameters.Count > 0)
        builder.AppendLine("With the following parameters.");
    foreach (IDataParameter param in command.Parameters)
    {
        builder.AppendFormat(
            "     Paramater {0}: {1}",
            param.ParameterName,
            (param.Value == null ? 
            "NULL" : param.Value.ToString())).AppendLine();
    }
    return builder.ToString();
}