How to get the Error Logs of Store Procedure.

[GA1657527506]

I have a Stored Procedure in GCP Server and it was called by using tbigquerySQLRow and it was running successfully.

But if the error in SP again it runs successfully. Kindly suggest what we have to do and how to get the error logs of GCP server.

[GA1657527506]

[Anonymous]

Hello,

Maybe you could use the subjob solution.

As there is no 'die on error' option on tBigqueryRow component, to get your request works, you need move tBigqueryRow to a child job, and use a tRunJob to call the child job, uncheck the box 'die on error' on tRunJob.

In the child job, use tLogCather component to capture the exception and log them into a file or database.

Best regards

Sabrina

[Anonymous]

BigQuery components provide after variables, for example: ERROR_MESSAGE and STATISTICS the latter is a JSON with a lot of details. As it contains the cost of each and every step in a stored procedure I believe it might be helpful for your issue as well.

[GA1657527506]

Thank you Sabrina for your response.

I have tried on this way but in the error of the store procedure also it shows as a successful. Kindly suggest me.

[GA1657527506]

Thank you for your response.

I have tried by using Global ERROR_MESSAGE of tBigQuerySQLRow and which is connected to tDie component in Run if condition.

Run if Condition is ((String)globalMap.get("tBigQuerySQLRow_1_ERROR_MESSAGE"))!=""

But for the successful SP also run if condition is executed and fails the Job.

Kindly suggest me.

[Anonymous]

Hello,

What's kind of error in the store procedure? Java exception?

Could you please elaborate your case with an example with input and expected output values?

Best regards

Sabrina

[GA1657527506]

Hi,

i was checked on the same issue in two ways.

one is Service account of Authentication mode, in this mode we can get the error message and job got failed.

In OAuth Access Token mode it was shows the error but job is not failed.

Also the value of (String)globalMap.get("tBigQuerySQLRow_1_ERROR_MESSAGE") is Null.

please find the attached screen shots for your reference.

Kindly suggest me to resolve the issue by using Access Token mode.

[Anonymous]

Hello,

Based on the error message it seems that you're trying to execute SQL query inside your stored procedure. That SQL query is null so it errors out.

I'd also suggest to upgrade your studio patch level as you're on R2021-06. We've introduced the following feature:

BigQuery : support Multiple resultsets stored Procedure executions

This was released in R2021-07 and also contains the statistics JSON after variable which might be helpful for your investigation. (I'd suggest to upgrade to R2022-07 but any version newer than R2021-07 should help.)

Regards,

Balázs