Qlik Community

QlikView Scripting

Discussion Board for collaboration on QlikView Scripting.

Announcements
QlikView Fans! We’d love to hear from you.
Share your QlikView feedback with the product team… Click here to participate in our 5-minute survey.
Rules, plus terms and conditions, can be found here.
amirkachlon
New Contributor III

right truncation over odbc connection

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

EntityIdvarcharno5

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

1 Solution

Accepted Solutions
amirkachlon
New Contributor III

Re: right truncation over odbc connection

OK found a solution -  changed the connection to oledb

works great for two weeks now

thanks for your help all

Regards,

Amir

8 Replies

Re: right truncation over odbc connection

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
Honored Contributor III

Re: right truncation over odbc connection

add Regional=No to your connection string passed to DBI->Connect. e.g. "dbiSmiley SurprisedDBCSmiley Very HappySN=mydsn;UID=dbuser;PWD=dbpassword;Regional=No;"

amirkachlon
New Contributor III

Re: right truncation over odbc connection

can you elaborate on what it actually do?

thanks !

sasiparupudi1
Honored Contributor III

Re: right truncation over odbc connection

please see the following document

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

sasiparupudi1
Honored Contributor III

Re: right truncation over odbc connection

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
Honored Contributor III

Re: right truncation over odbc connection

Hi

Did you manage to resolve this problem?

Sasi

amirkachlon
New Contributor III

Re: right truncation over odbc connection

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

Regional.PNG

amirkachlon
New Contributor III

Re: right truncation over odbc connection

OK found a solution -  changed the connection to oledb

works great for two weeks now

thanks for your help all

Regards,

Amir

Community Browser