SQL Trace for a Login

Question

I have two questions:

1. How do I filter it to run for a very specific SQL Login? e.g. "sa"
2. When a query from an SSIS is executed and failed, the Trace captures the failure but shows with "..." like @m... in the following error message. How do I get the full error and how do I know the the record it failed on?

Source: MyTask Execute SQL Task Description: Executing the query "declare @startDate datetime, @endDate dateTime, @m..." failed with the following error: "The conversion of a varchar data type to a datetime data type resulted in an out-of-range value."

Answer 1

Hi
Run SQL Server Management Studio
From menu select Tools
choice item SQL server Profiler.
trace view .

Best Regards.
Please remember to mark the replies as answers if they help.

Answer 2

I am not sure that I understand your question. You start talking about SQL Trace, but then all of a sudden you start to talk about SSIS. SQL Trace is a feature entirely unrelated to SSIS, but maybe you are talking about a trace in side SSIS (a tool that I don't know myself.)

In any case, no trace can tell you which row you got the conversion failure. However, you can use try_convert for the conversion:

try_convert(datetime, varcharvalue)

With try_convert a conversion returns NULL but does not fail the query. You can use this to trap errors beforehand.

Answer 3

Hi MalamMalam-4042,

How do I filter it to run for a very specific SQL Login? e.g. "sa"

You can filter login name in the Trace Properties -> Events Selection tab ->Column Filters.

Then in the Edit Filter dialog box, you can select LoginName and enter the login name in the Like section.

How do I get the full error and how do I know the the record it failed on?

You can try to implement logging in SSIS. With logging, you can capture run-time information about a package, helping you audit and troubleshoot a package every time it is run. For example, a log can capture the name of the operator who ran the package and the time the package began and finished.
Please refer to this doc for more information.

Best Regards,
Amelia

---

If the answer is helpful, please click "Accept Answer" and upvote it.
Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.