? Parameter Usage Throwing Errors in Published

[mcquerry-dillon]

Hello,

Currently I'm using two ? parameters in my query to refine by the date given from the end user. They work as long as I'm running the report locally within PowerBI Report Builder but once it's published I get a lengthy error:

Beginning:

"A data source used by this report returned an error. Received error payload from gateway service with ID 1840020: Async operation 66a1ce6f-8e18-47b9-98ba-778eaa6b1d75 is faulted with [0]Microsoft.PowerBI.DataMovement.Pipeline.Diagnostics.DataAccessException: An exception encountered while accessing the target data source GatewayPipelineErrorCode=DM_GWPipeline_Gateway_DataSourceAccessError"

End:

" An exception encountered while accessing the target data source ERROR [22001] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]String data, right truncated. Error in parameter 1."

The middle of the error message looks like a bunch of end of stack traces, not sure how relevant that is. My query is:

select stuff
from pub.transactions
inner join pub.parts on pub.transactions.part=pub.parts.part and pub.transactions.domain = pub.parts.domain
where (pub.transactions.date >= ? and pub.transactions.date <= ?)

Any help is appreciated.

[v-stephen-msft]

Hi @mcquerry-dillon ,

Based on the error message you've provided, it appears that the issue arises after the report is published and not while running locally. The key part of the error message is:

ERROR [22001] [DataDirect][ODBC Progress OpenEdge Wire Protocol driver]String data, right truncated. Error in parameter 1.

This error typically indicates that the string data being passed to the first parameter of your query is longer than the target database field can accept, resulting in truncation.

Here are a couple of steps you can take to troubleshoot and resolve this issue:

1.Verify Parameter Data Types and Lengths:

Ensure that the data types of the parameters in your query match the data types expected by the database fields.
Check if there are any length restrictions on the fields you are comparing your parameters to, and ensure that the values passed from Power BI do not exceed these limits.

2.Review the Data Source Configuration:

In the Power BI service, navigate to the settings of the dataset that is causing the issue.
Select the Gateway connection to ensure that the data source is configured correctly and that the credentials are valid.
If you are using an on-premises data gateway, make sure it is up to date and properly configured to handle the parameters. Troubleshooting the on-premises data gateway

3.Test the Query with Hardcoded Values:

As a test, replace the ? parameters with hardcoded values that you know are within the acceptable range for the database fields.
Publish the report with these hardcoded values to see if the issue persists. If the report runs successfully, the issue is likely with the parameter values being passed from Power BI.

Best Regards,

Stephen Tao

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.