March 14, 2018 at 6:12 pm
Hi,
A query I maintain uses a join to a SQL function that I feel is the cause of an error.
The error that gets returned is the following default text: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I am interested in knowing if I can capture the ID value of the parameter to the function that caused the error.
SELECT
*
FROM
INVOICE I INNER JOIN
ACCOUNT A ON
DBO.get_account_id_by_invoice_date( i.invoice_id ) = a.account_id
There might be a million invoices, I'd like to know which invoice ID is the cause of the error.
I know I can't use TRY - CATCH in a T-SQL UDF.
I can't think of a way to capture the ID of the function parameter when it is called.
March 14, 2018 at 7:56 pm
Scott Thornton-407727 - Wednesday, March 14, 2018 6:12 PMHi,A query I maintain uses a join to a SQL function that I feel is the cause of an error.
The error that gets returned is the following default text: "Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression"
I am interested in knowing if I can capture the ID value of the parameter to the function that caused the error.
SELECT
*
FROM
INVOICE I INNER JOIN
ACCOUNT A ON
DBO.get_account_id_by_invoice_date( i.invoice_id ) = a.account_idThere might be a million invoices, I'd like to know which invoice ID is the cause of the error.
I know I can't use TRY - CATCH in a T-SQL UDF.
I can't think of a way to capture the ID of the function parameter when it is called.
You can trace it or do an extended events session and capture what was executed including the parameters that were passed.
Sue
March 14, 2018 at 8:14 pm
the error is most likely inside your function.
DBO.get_account_id_by_invoice_date( i.invoice_id )
just based on the name, it's probably trying to find the "latest" invoice, but what if i ordered two things ont eh same date?
show the definition of the procedure, and we can help debug it, but it will have a design that says something like WHERE SomeDateColumn=(SELECT
and that is where the error is.
Lowell
March 14, 2018 at 8:33 pm
The function retrieves what we call a Debtor Account ( its a financial system). It isn't dependant on date.. Or rather is can be, but also on Invoice Type, location Type, location ID, Department ID etc etc etc. Names of function and other tables changed to protect the innocent.
There are a number of select queries within the function. I know the particular query that is the cause of the error within the function. I have fixed the data that was the cause of the error. ( Account ID info was imported from a spread sheet outside of the checking functionality of the user application ). The error wouldn't generally occur except that the spread sheet wasn't sanitised enough. It is now.
I actually spent a number of hours this morning running the queries to no avail. Then a light bulb moment.. The problem was in a different instance of the application, different from what I spent hours checking.
If I had a way of including the invoice_id in the error output raised by MS SQL, it would have been easier to track down.
Judging by my googling, there isn't out-of-the-box SQL solution, but I am happy to be corrected.
You can trace it or do an extended events session and capture what was executed including the parameters that were passed.
March 15, 2018 at 1:28 pm
It doesn't look like you're passing a date, you're passing an id instead, which will get interpreted as a date but it won't be the date you expect or want.
SQL DBA,SQL Server MVP(07, 08, 09) A socialist is someone who will give you the shirt off *someone else's* back.
March 15, 2018 at 1:34 pm
Scott Thornton-407727 - Wednesday, March 14, 2018 8:33 PMHi,The function retrieves what we call a Debtor Account ( its a financial system). It isn't dependant on date.. Or rather is can be, but also on Invoice Type, location Type, location ID, Department ID etc etc etc. Names of function and other tables changed to protect the innocent.
There are a number of select queries within the function. I know the particular query that is the cause of the error within the function. I have fixed the data that was the cause of the error. ( Account ID info was imported from a spread sheet outside of the checking functionality of the user application ). The error wouldn't generally occur except that the spread sheet wasn't sanitised enough. It is now.
I actually spent a number of hours this morning running the queries to no avail. Then a light bulb moment.. The problem was in a different instance of the application, different from what I spent hours checking.
If I had a way of including the invoice_id in the error output raised by MS SQL, it would have been easier to track down.
Judging by my googling, there isn't out-of-the-box SQL solution, but I am happy to be corrected.
You can trace it or do an extended events session and capture what was executed including the parameters that were passed.
It doesn't sound like it can be done in a normal user session, by the application....
Typically you would not do this from an application but technically you can since they can be done using T-SQL.
Filter the trace or session and when you do things like that, you have the trace or session running. It's not something to turn on after an error or fire up from an app right before every execution. But that is a SQL Server out of the box solution. You run traces or sessions to capture needed information. It's usually considered a DBA task and not a development task though if that's what you mean by normal. It does require permissions not typically (shouldn't be) granted to all users.
Sue
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply