Qlik Community

New to QlikView

Discussion board where members can get started with QlikView.

avantime
Contributor II

SQL Select adds invalid spaces to data.

Hi,

I have an issue, i have a simple SQL select script, extracting about 16m rows of data.

If i select only one row of data, everything is OK. If I select the entire contents of the database, I get corrupt results with missing leading zeros, spaces added randomly, etc.

Any idea why this happens?

1 Solution

Accepted Solutions
avantime
Contributor II

Re: SQL Select adds invalid spaces to data.

Solved it.

Changed my code into:

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=xxxxxx;Data Source=xxxxxx;Extended Properties="DSN=xxxxxxx;SERVER=xxxxxx;PORT=xxxxx;DATABASE=xxxxxxx;AUTHENTICATION METHOD=0;UID=xxxxxxx;STATIC CURSORS=0;QUERY TIMEOUT=1;UNICODE SQLTYPES=0"];

LOAD

Text(ArtNR),

Text(ProdNR);

SELECT

MDB.ArtNR,

MDB.ProdNR

FROM MainDatabase MDB;

All loads OK Now.

View solution in original post

8 Replies
Partner
Partner

Re: SQL Select adds invalid spaces to data.

can you post your code?

Highlighted
dean44811
New Contributor

Re: SQL Select adds invalid spaces to data.

you could add RTRIM() and LTRIM() to fields with leading and trailing spaces. 

REPLACE() works well to replace characters in the string.  

Not applicable

Re: SQL Select adds invalid spaces to data.

Where is SQL Server located, is it on same network from where you are extracting?

Does QlikView connection fails at the end?

Re: SQL Select adds invalid spaces to data.

What do you mean that? What is the key of random numbers. Can you elaborate more

Before develop something, think If placed (The Right information | To the right people | At the Right time | In the Right place | With the Right context)
avantime
Contributor II

Re: SQL Select adds invalid spaces to data.

Hi, sorry for the late reply, been out of town!

The extract script is very simple, something like this:

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=xxxxxx;Data Source=xxxxxx;Extended Properties="DSN=xxxxxxx;SERVER=xxxxxx;PORT=xxxxx;DATABASE=xxxxxxx;AUTHENTICATION METHOD=0;UID=xxxxxxx;STATIC CURSORS=0;QUERY TIMEOUT=1;UNICODE SQLTYPES=0"];

SELECT

MDB.ArtNR,

MDB.ProdNR

FROM MainDatabase MDB;

The normal output should be:

ArtNRProdNR
0231102311
0852208522

Instead I get:

ArtNRProdNR
2311231      1
8522852      2

I repeat, this problem does not occur if I select just one ArtNR at a time.

The server I am querying is on the same network.

Frank_Hartmann
Honored Contributor II

Re: SQL Select adds invalid spaces to data.

Have a look at Verbatim Variable:

Qlikview SET Verbatim = 1 by RFB 184 - YouTube

hope this helps

avantime
Contributor II

Re: SQL Select adds invalid spaces to data.

Tried it already.. Doesn`t work.

avantime
Contributor II

Re: SQL Select adds invalid spaces to data.

Solved it.

Changed my code into:

OLEDB CONNECT TO [Provider=MSDASQL.1;Persist Security Info=False;User ID=xxxxxx;Data Source=xxxxxx;Extended Properties="DSN=xxxxxxx;SERVER=xxxxxx;PORT=xxxxx;DATABASE=xxxxxxx;AUTHENTICATION METHOD=0;UID=xxxxxxx;STATIC CURSORS=0;QUERY TIMEOUT=1;UNICODE SQLTYPES=0"];

LOAD

Text(ArtNR),

Text(ProdNR);

SELECT

MDB.ArtNR,

MDB.ProdNR

FROM MainDatabase MDB;

All loads OK Now.

View solution in original post