KB484912: "[ODBC] (10160) String data right truncation" error is returned in Google BigQuery and Snowflake

Description 

When fetching data, the following error is returned: [String data is too big for the output data buffer and has been truncated]. This error is shown when there is a string longer than 4,000 characters. This tech note is applicable for all gateways which exceed the string limit. In this example, we will only cover Google BigQuery and Snowflake.

Why is this happening?

In these cases, Strategy by default allocates memory of maximum length of 4,000 characters while the driver will try to write more than 4,000 characters.
Google BigQuery
[01004:10160: on SQLHANDLE][Strategy][ODBC] (10160) String data right truncation: String data is too big for the output data buffer and has been truncated
Usually this error is seen when you are trying to import a nested table as string variable into Strategy.

"Unable to publish the cube.
demo_array_3
We could not obtain the data because the database connection or the warehouse table has changed, Encountered error: FetchScroll. Error type: Odbc success with info. Odbc operation attempted: SQL FetchScroll.[01004:10160: on SQLHANDLE] [Strategy][ODBC] (10160) String data right truncation: String data is too big for the output data buffer and has been truncated.."
Snowflake
[01004:10160: on SQLHANDLE][Snowflake][ODBC] (10160) Output string data right truncation: string data is too big for the output data buffer and has been truncated
Usually this error is seen when you are trying to import a JSON field as string variable into Strategy.

"Unable to publish the cube.
DEMONSTRATION_VARIANT
We could not obtain the data because the database connection or the warehouse table has changed, Encountered error: FetchScroll. Error type: Odbc success with info. Odbc operation attempted: SQL FetchScroll.[01004:10160: on SQLHANDLE] [Strategy][ODBC] (10160) String data right truncation: String data is too big for the output data buffer and has been truncated.."

Workaround 1

For the Snowflake and BigQuery gateways, Strategy recommends migrating ODBC connections to JDBC when necessary. In JDBC connections, this data truncation problem does not occur because of the way JDBC API works (JDBC API does not require memory pre-allocation).

Workaround 2

• In Windows, open Strategy Developer. 
• Navigate to Administration > Configuration Managers > Database Instances.
  

• Edit the database instance where the error occurs.
• Click the Upgrade button.
• In the Upgrade Database Type dialog, copy the path for the “DB types script file” (usually

  C:\Program Files (x86)\Common Files\Strategy\DATABASE.PDS

  ).
• Open the

  DATABASE.PDS

  file.
• Search for the string:

  <DSSOBJECT TYPE="DBMS" NAME="Snowflake">

  
  Or search for string depending:

  <DSSOBJECT <TYPE="DBMS" NAME="Google BigQuery">

  
  
• Search for the next immediate entry:

  <PROPERTY NAME="Maximum Var Size" VALUE="4000">

• Edit the 4000 value to a larger value than the column length on the database that is being fetched. 
• Save the 

  DATABASE.PDS

  file.
• In the Upgrade Database Type dialog, click Load. 
• Move the Snowflake or Google BigQuery entry from the left panel to the right panel. Confirm you want to replace the previous database type definition. 
• Click OK, and then OK again. 
  

• Restart your Intelligence Server and retry fetching the dataset.