ODBC connection is not showing the fields NVARCHAR(MAX) and VARCHAR(MAX) of the tables in IDT

[Former Member]

I created an ODBC connection using a service account. ODBC connection is for MS SQL Server 2012. When I see the structure of my tables are not showing the fields type NVARCHAR(MAX) and VARCHAR(MAX). When I can see all the fields when I used OLEDB driver. The problem is that I need to use service account to connect to the DB. I am on SAP BI 4.1 SP5

I saw the solution of this http://service.sap.com/sap/support/notes/1422831 but it's an old version of SAP BI. Any suggestion ?

[Former Member · Accepted solution]

BO doesn't like varchar(max) fields, presumably because it doesn't know how big they might be.

The workaround that I've been using is to create a view and take the first 8000 characters of the max column, while including the nolock hint to improve performance::

CREATE VIEW vw_table as

SELECT

col1, col2, left(col3,8000) as col3, col 4

FROM

dbo.table WITH (NOLOCK)

While it's an overhead in the grand scheme of things, it's an easy enough one to implement.

I've worked on a project before now where I've had to create views for all tables because I wasn't allowed direct access to the original schema. Quite an experience!

Regards,

Mark

[Former Member · Accepted solution]

Hi Mark,

     I checked some fields and they have limits. These fields are not showing in the ODBC connection. Do you have any idea ?

[Former Member · Accepted solution]

What limits do they have? If they are defined as MAX then BO won't care if there are internally determined limits. Similarly BO, certainly XI3, has never liked SQL Server custom data types, no matter what size they are, e.g if you set up a small custom char format like for phone numbers, it won't like it.

[Former Member · Accepted solution]

The problem was fixed as soon the account received direct access.