topic Re: right truncation over odbc connection in QlikView

right truncation over odbc connection

amirkachlon — Mon, 27 Jul 2015 16:04:46 GMT

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

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

Re: right truncation over odbc connection

marcus_sommer — Tue, 28 Jul 2015 08:11:32 GMT

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

Re: right truncation over odbc connection

sasiparupudi1 — Tue, 28 Jul 2015 08:37:24 GMT

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

Re: right truncation over odbc connection

amirkachlon — Tue, 28 Jul 2015 09:17:50 GMT

can you elaborate on what it actually do?

thanks !

Re: right truncation over odbc connection

sasiparupudi1 — Tue, 28 Jul 2015 09:25:51 GMT

please see the following document

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

Re: right truncation over odbc connection

sasiparupudi1 — Tue, 28 Jul 2015 09:28:17 GMT

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

Re: right truncation over odbc connection

sasiparupudi1 — Thu, 30 Jul 2015 06:08:44 GMT

Did you manage to resolve this problem?

Sasi

Re: right truncation over odbc connection

amirkachlon — Mon, 03 Aug 2015 09:20:12 GMT

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

Re: right truncation over odbc connection

amirkachlon — Wed, 19 Aug 2015 09:06:31 GMT

OK found a solution - changed the connection to oledb

works great for two weeks now

thanks for your help all

Regards,

Amir

Re: right truncation over odbc connection

QlikPYee — Wed, 24 Feb 2021 00:47:15 GMT

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