I am in the process of refactoring legacy system which relies on huge a stored procedure with large calling tree of dependent stored procedures/UDFs and a lot of interconnected linked servers.
Production stored procedure runs fine while test one fails with
Login failed for user 'user_test'
I can get no further details from error_procedure() and error_line().
Complete output:
(1 row affected)
(1 row affected)
…skipped
ErrorNumber=[18456]; ErrorSeverity=[14]; ErrorState=[1]; ErrorProcedure=[]; ErrorLine=[1]; ErrorMessage=[Login failed for user 'user_test'.]
Msg 18456, Level 14, State 1, Line 1
Login failed for user 'user_test'.
Apparently some of the linked servers are not properly setup on the test environment. So I would need to either set them up or mock/comment external calls. I cannot debug the stored procedure on a test server.
How to find exact line in the code which throws this error without adding traces after each line of SQL?
System runs in SQL Server 2016 on Windows Server 2012 R2.
Best Answer
SQL Profiler can capture the individual statements in a stored procedure. See details here. Using such capture I have identified statement preceding an error. Then I have searched through SQL code using Toad. Problem was pinpointed. It was something like:
exec sp_executesql 'select top 1 * from openquery(ext_server, ...If there exists more elegant solution to this - please post an answer here.