Question Re: Simple SQL in SSMS returns blank rows in Technology Q&A

Simple SQL in SSMS returns blank rows

rwong_gnb — Tue, 10 Dec 2019 20:46:47 GMT

Hello,

I created a linked server connection to our SAP B1 HANA server in my SSMS environment and have been using it for various queries for a while - never had any issues. This week I typed in this simple query into SSMS:

SELECT * FROM OPENQUERY([LINKEDSERVERCONNECTION], 'SELECT T0."ItemCode", T0."UserText" FROM DATABASE.OITM T0;')

What I got was 99% blank cells with a seemingly random few cells with actual ItemCodes. (see following screenshot)

The column UserText IS showing properly though - when I scroll through I can recognize values but the corresponding ItemCode is blank when it should be the correct part number.

Now when I remove T0."UserText" from the query then all the ItemCodes show up:

So it seems that adding T0."UserText" to the query causes some strange problem returning data.

While few ItemCodes have something in UserText compared to all the other ItemCodes, those that do have data can sometimes have quite lengthy strings in UserText (sometimes 100+ characters). However, I do not believe there are enough instances of this to cause what I would believe to be a shortage of resources on the HANA server - but then I'm not a HANA systems expert.

If I query ItemCode and UserText in SAP B1's Query Wizard, I can get everything to display correctly. I need it to work in SSMS however.

Does anyone have any idea what could be causing SSMS' troubles displaying the query with UserText?

Thanks!

Randy

Re: Simple SQL in SSMS returns blank rows

Johan_Hakkesteegt — Wed, 11 Dec 2019 07:13:59 GMT

Hi Randy,

Could you please test this:

SELECT * 
FROM OPENQUERY([LINKEDSERVERCONNECTION], 
     'SELECT T0."ItemCode"
           , CAST(T0."UserText" AS NVARCHAR(MAX)) AS UserText
      FROM DATABASE.OITM T0;')

Regards,

Johan

Re: Simple SQL in SSMS returns blank rows

rwong_gnb — Wed, 11 Dec 2019 14:49:01 GMT

Hi Johan,

Thanks for the help! I tried the query and got this error:

OLE DB provider "MSDASQL" for linked server "DATABASE" returned message "[SAP AG][LIBODBCHDB DLL][HDBODBC] General error;260 invalid column name: MAX: line 1 col 54 (at pos 53)".
Msg 7321, Level 16, State 2, Line 1
An error occurred while preparing the query "SELECT T0."ItemCode", CAST(T0."UserText" AS NVARCHAR(MAX)) AS "UserText" FROM DATABASE.OITM T0;" for execution against OLE DB provider "MSDASQL" for linked server "LINKEDSERVERCONNECTION".

which is weird - I tried copying

SELECT T0."ItemCode", CAST(T0."UserText" AS NVARCHAR(MAX)) AS "UserText" FROM DATABASE.OITM T0;

directly into SAP's query builder and got the same error too.

I tried changing MAX to 4000 (anything higher than that and I get errors - see below) and was able to get it to work - maybe B1 doesn't use MAX for NVARCHAR cast lengths?

On a side note, I thought the max length for NVARCHAR in HANA was 5000 but when I use 5000 the query runs for half a second and then delivers an error:

OLE DB provider "MSDASQL" for linked server "DATABASE" returned message "Requested conversion is not supported.".
Msg 7341, Level 16, State 2, Line 1
Cannot get the current row value of column "[MSDASQL].UserText" from OLE DB provider "MSDASQL" for linked server "DATABASE".

In the end, I can live with 4000 since our users realistically max out around 1000 - there's a paranoid part of me that wants MAX to work to cover all possible character lengths though.

Re: Simple SQL in SSMS returns blank rows

Johan_Hakkesteegt — Thu, 12 Dec 2019 07:26:48 GMT

Hi Randy,

You can try casting it to NTEXT:

CAST(T0."UserText" AS NTEXT) AS UserText

Regards,

Johan

Re: Simple SQL in SSMS returns blank rows

rwong_gnb — Mon, 16 Dec 2019 14:20:38 GMT

That works perfect! Thanks!