Interpretation of MSSQL VARCHAR as V_String

[maxksoll]

Hi all,

Within the last days I was confronted with a problem in a production workflow, which was not working as expected. The main problem was, that the input tool was collecting data from a MSSQL database, but some parts of the strings disappeared on the way.

One column was specified as a VARCHAR and contained some special characters (e.g. '…', not '...'). The input tool now drops all characters which are not included in the Latin-1 table, but this does not seem to be a good behaviour from my perspective. This is not the case for NVARCHAR fields, they are automatically imported as V_WString, which contains those special characters.

The solution was to just cast in the SQL query (redesign of the database layout would also work). Shouldn't this be solved a little easier, or are there good reasons to force using a V_String?

Best regards

Max

[Drussek]

In addition to the described problem with reading data, I have the same problem with writing to the Oracle database.

Oracles's VARCHAR can store tail letters (national), same as VARCHAR in MSSQL. The base does not require NVARCHAR for this.

So why is Alteryx unable to read or write these types of characters?

PS. In Oracle I can update inserted values by:

CONVERT("value", 'WE8MSWIN1252', 'UTF8')

And then everything is ok, but it is unprofessional and needs a lot effort and time.

[KylieF]

Thank you for your feedback and idea!  

Please be sure to check out our Submission Guidelines as well as other users ideas to likes and comment on. Likes and feedback through the comments really help us better understand what our users need and are looking for in our products.

[SeanAdams]

hey @maxksoll - I do agree that a slicker way of handling varchar vs. NVarchar would be very useful.   I'm curious if you found this problematic only in ODBC connection or also with native and OleDB?

the other aspect of this which would be helpful to fix is that Alteryx writes to the database as very long text fields by default when creating a new table (used to be Text / NText - now Varchar(max)) which seems to be due to Alteryx having a default size for VString which is a few million characters or so.    This means that any table that's created by Alteryx into SQL should be inspected by a person to check that economical field types have been used.    Given how costly Text / Varchar(max) is - it would be great to at least raise a warning, or preferrably trim to size.

[maxksoll]

Hi @SeanAdams,

sorry for the late reply, I somehow missed the notification.

I only tested it via ODBC, but in my opionion it wouldn't make sense if it would differ.

The default strings are such a pain when it comes to lager tables, but I guess you have to make some compromises with the SQL generation from Alteryx.

Best regards

Max