ODBC Import String Length

[holger_b]

Dear forum,

currently I am setting up an import from an MSSQL database which contains string values with up to 3339 characters length. Unfortunately they are chopped off after 256 characters in the TI import... What I did so far is, cut the string in pieces and re-assemble it in the data tab, but this is ugly, and it is not flexible, i.e. bound to fail if the string is longer than expected. Any other ideas?

Code: Select all

select sDimension, sElement, sAttribute, sAttributeType, 
SUBSTRING(sValue,1, 256) as v_sString1,
substring(sValue,  257, 256) as v_sString2,
substring(sValue,  513, 256) as v_sString3 ...
FROM tbl_attributes

This is TM1 9.5.2, SQL-Server 2008 R2, ODBC-Driver SQLSRV32.DLL Version 6.1.7601.17514.

Holger

[tomok]

I agree it's ugly but it wouldn't break if you would check the length first, divide by 256 and round up to next even number, and then do a loop of that length, breaking the string and re-assembling.

[rmackenzie]

currently I am setting up an import from an MSSQL database which contains string values with up to 3339 characters length. Unfortunately they are chopped off after 256 characters in the TI import...

Looking at this MSDN article it would seem that a datatype of NVARCHAR can store strings with lengths upto 4000. Perhaps you are doing this already and have hit a barrier introduced by the ODBC driver and not the database itself? If so, it looks like the chunking solution is the most obvious, unfortunately.

[holger_b]

Well, the MSSQL table stores the complete strings in an nvarchar (max) column, so no problem from that side. Just now I believe it is the ODBC driver: I tried a solution with additional columns, each of them holding 256 characters of the string, but I end up with lost columns in the TI.

I will post here what becomes of it.

[holger_b]

It turned out that it was an ODBC driver problem. Our client uses SQL Server 2008 R2, the ODBC driver was SQLSRV32.DLL Version 6.01.7601.17514.

The error does not occur with SQLNCLI10.DLL. You can download the latest version as a separate installer package for example here: http://www.microsoft.com/en-us/download ... x?id=26728 (make sure of the proper version for your OS and DB).