SQL Trace for a Login

Malam Malam 121 Reputation points
2021-01-14T18:42:33.62+00:00

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."

SQL Server
SQL Server
A family of Microsoft relational database management and analysis systems for e-commerce, line-of-business, and data warehousing solutions.
12,883 questions
0 comments No comments
{count} votes

3 answers

Sort by: Most helpful
  1. Abdulhakim M. Elrhumi 351 Reputation points
    2021-01-14T20:32:51.037+00:00

    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.

    0 comments No comments

  2. Erland Sommarskog 102.3K Reputation points
    2021-01-14T22:39:49.847+00:00

    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.

    0 comments No comments

  3. AmeliaGu-MSFT 13,961 Reputation points Microsoft Vendor
    2021-01-15T07:57:16.707+00:00

    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.

    57111-01.jpg

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

    57112-02.jpg

    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.

    0 comments No comments