Forum Discussion

edhans's avatar
edhans
Community Champion
7 years ago

Native Query for SQL Server is different for 2008 vs 2017 - TRIM statement is a mess

We have SQL Server 2008 at work which is our production server. I have SQL 2017 dev edition on my laptop for testing. The production database has been copied to my machine, so that is exact. (Done vi...
Need Help
v-piga-msft's avatar
v-piga-msft
Resident Rockstar
7 years ago

Hi edhans ,

 

Based on your scenario, I have made a test for SQL Server 2008 and 2017 with different version of Power BI Desktop.

 

For SQL Server 2008, it will always show without NCHAR().

 

For SQL Server 2017, it will show with NCHAR() on the latest version of Power BI Desktop which is similar to your test result.

 

However, it won't show with NCHAR() for the version earlier like below by my test.

 

So I'm afraid that may be caused by the version of Power BI.

 

Best  Regards,

Cherry

edhans's avatar
edhans
Community Champion
7 years ago

Doesn't that seem like a bug? For example, the Person.Address table in the AdventureWorks2017 database. Power BI Desktop and Excel both produce this if I trim the AddressLine1 field.

select trim(concat(nchar(32), nchar(160), nchar(5760), nchar(8192), nchar(8193), nchar(8194), nchar(8195), nchar(8196), nchar(8197), nchar(8198), nchar(8199), nchar(8200), nchar(8201), nchar(8202), nchar(8239), nchar(8287), nchar(12288), nchar(9), nchar(10), nchar(11), nchar(12), nchar(13), nchar(133), nchar(8232), nchar(8233)) from [_].[AddressLine1]) as [AddressLine1]
from 
(
    select [$Table].[AddressLine1] as [AddressLine1]
    from [Person].[Address] as [$Table]
) as [_]

However, in SSMS, while the above works, I can just use this query:

select trim([AddressLine1]) as [AddressLine1]
from 
(
    select [$Table].[AddressLine1] as [AddressLine1]
    from [Person].[Address] as [$Table]
) as [_]

Why would Power Query generate all of that NCHAR() stuff?