SQL Select adds invalid spaces to data.

[avantime]

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?

[jpenuliar]

can you post your code?

[Anonymous]

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

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

[#4]

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

Does QlikView connection fails at the end?

[Anil_Babu_Samineni]

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

[avantime]

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:

ArtNR	ProdNR
02311	02311
08522	08522

Instead I get:

ArtNR	ProdNR
2311	231      1
8522	852      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]

Have a look at Verbatim Variable:

Qlikview SET Verbatim = 1 by RFB 184 - YouTube

hope this helps

[avantime]

Tried it already.. Doesn`t work.

[avantime · Accepted solution]

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.