right truncation over odbc connection

[amirkachlon]

Hi all,

While im reading from sql server I get this Error:

QVX_UNEXPECTED_END_OF_DATA: SQL##f - SqlState: 01004, ErrorCode: 0, ErrorMsg: [Microsoft][ODBC SQL Server Driver]String data, right truncation

It's tricky because on the second attempt it wont show up and it will run successfully.

I suspect it's something related to the meta data of the fields, so I monitored the meta data and found out that when some field length changed this query failed and it's

the first scenario I need to resolve - how i can promise success reading this query on meta data change?

the second scenario and more complicated is when there is no change in the metadata of fields and still I get this error with no visible reason.

the query I'm running in my qvd is a simple preceding load , notice I use TEXT() function on a field type

EntityId

varchar

no

5

because qlikview engine converts it to numeric and some data losts in the process.

the query:

Customers:

LOAD TEXT(EntityId) as TextEntityId,

  EntityId as NumEntityId, // old version

// NumEntityId, // new Version

  EntityId,

    EntityName,

    EntityHebrewName,

    Corporate,

    MasterAccount,

    PayorId,

    CompanyId,

    VatId,

    Remarks,

    InternetAddress,

    StockSymbol,

    FoundationDate,

    Owners,

    CreateDate,

    CreatedBy,

    LastUpdateDate,

    LastUpdateUser,

    SalesRepId,

    CollectorId,

    Logo,

    SecurityCode,

    IsForImpMatch,

    SumTipul,

    SumComm,

    SumComm1000,

    "broker_id",

    updated,

    "segment_code",

    "fix_segment_code",

    "cust_type",

    "sector_sub_code",

    "service_level",

    groupID,

    "cust_sub_type",

    "broker_limit_cd",

    "Adjusted_Name",

    "Customer: blocked cust",

    "Customer: zip code",

    "Customer: statement",

    "Customer: representative : intl sales man: code",

    "Customer: representative: domestic sales man: code",

    "Customer: representative: intl rep",

    "Customer: representative: intl RepId",

    "Customer: representative: domestic rep",

    "Customer: representative: import rep",

    "Customer: representative: collector",

    "Customer: representative: implant",

    "Customer: team: export",

    "Customer: team: is TOP Executive",

    "Customer: team: domestic",

    "Customer: team: import",

    "Customer: team: import: main complex id",

    "Customer: team: import: main complex",

    "Customer: team: import: sub complex id",

    "Customer: team: import: sub complex",

    "Customer: team: import: classifier full name",

    "Customer: team: import: inspector full name",

    "Customer: team: header: export",

    "Customer: team: header: export_id",

    "Customer: team: header: export id",

    "Customer: team: header: import",

    "Customer: team: header: domestic",

    "Customer: terms: usd rate",

    "Customer: city code",

    "is_fedex_dim_import_special",

    "is_fedex_dim_export_special",

    "is_fedex_dim_drop_special",

    "is_dim_import_special",

    "is_dim_export_special",

    "is_dim_drop_special",

    "is_export_SpecialEconomy",

    "is_import_SpecialEconomy",

    "collect_no",

    "cosmos_account",

    "Drop_ICPC";

SQL SELECT *

FROM FedexDWH.dbo."Customers_BI_qlikview";

store Customers into [.\1_Raw\Customers.qvd];

drop table Customers;

thanks for your help

Amir

[marcus_sommer]

I think it's a "feature" from odbc-driver. Maybe you tried to replace them with an oledb-driver. Further helpful could be to use  Incremental Load or to split the load into several packages (horizontally and vertically) to increase the speed, mimimize potential problems (network problems, timeouts, other accesses ...) and by problems to find the causes easier.

- Marcus

[sasiparupudi1]

add Regional=No to your connection string passed to DBI->Connect. e.g. "dbi:ODBC:DSN=mydsn;UID=dbuser;PWD=dbpassword;Regional=No;"

[amirkachlon]

can you elaborate on what it actually do?

thanks !

[sasiparupudi1]

please see the following document

http://www.easysoft.com/support/kb/kb00990.html

[sasiparupudi1]

as far as my understanding goes, this setting is used for display purposes  only and should be turned of for any data processing.

Below is MS help

Use regional settings when displaying currency, number, dates and times

Specify that regional settings should be used for currency, numbers, dates, and times

HTH

[sasiparupudi1]

Hi

Did you manage to resolve this problem?

Sasi

[amirkachlon]

Hi,

Well I find difficulty translating my qlikview odbc connection to the extened connection string format, so I didnt change manually the regoinal settings in the connection string.

But checking my odbc connection settings I noticed that regoinal settings is off,

is it enough to say that I'm not using regoinal setting on my odbc connection string?

If not can you suggest a way to get the fuul connection string I use in my qvw file?

thanks for help

Amir

[amirkachlon · Accepted solution]

OK found a solution -  changed the connection to oledb

works great for two weeks now

thanks for your help all

Regards,

Amir

[QlikPYee]

Please see my post here for another solution to this issue in QlikView/Qlik Sense: https://community.qlik.com/t5/QlikView-App-Dev/QVX-UNEXPECTED-END-OF-DATA/td-p/1691409