Datatypes text and ntext in SQL Server will be depracated

[Frank]

In a fututre release from SQL Server the datatypes text, ntext and image will be deprecated. It is already a bad datatype because you cannot use it as a "normal" character string. No equal to sometinh else in T-SQL on a text datatype.

As far as I know Alteryx defaults to text (my source is a PostgreSQL database) when creating the table in SQL Server. The datatype in Alteryx is Vstring. Instead of text or ntext it would be zo much better to use varchar(MAX) or nvarchar(MAX) when creating the table. Not only for compatiblity and later use in T-SQL (if any), but it is faster as well. Data from a varchar(MAX) column is stored in the same page as the record, as log as it fits.

[MaudyP]

@blyons, thank you for the information and specific example.  We will be taking a closer look.

[anotherusername]

Thanks @blyons - was hoping there might be a format override control hiding somewhere in the settings that I hadn't seen.

I've found a way around my immediate issue - but will try a newer ODBC connector and post a new forum message if there are still issues with >1023 chars in Netezza.

Cheers :)

[blyons]

Good thought about the connector. For the record, my earlier test is using OleDB, SQL Server Native Client 11.0, in case that is of any help.

[MaudyP]

@blyons, thank you for the information.

[anotherusername]

Have updated to as-new-a driver package as I can get my hands on (NetezzaSQL 7.02.01.37, which seems to house ODBC Driver version: 03.51).

But the error remains (I didn't really expect the ODBC driver to fix it).  

Sorry - don't want to hijack this thread - but so @MaudyP - how would I lodge this as a defect?  Else should it be a new 'Idea' ?  Alternatively, is it possible to access/set these format defaults?

Error: Data Stream In (3): Error creating table "AYX1802166e6cc31accc519e14f0c8f619eb0af8f": ERROR: Invalid datatype - TEXT
CREATE TABLE "AYX1802166e6cc31accc519e14f0c8f619eb0af8f" ("Field1" text) DISTRIBUTE ON RANDOM

I can provide a workflow which will replicate the error (if you have any Netezza box to connect to).

Impacts V_String and V_WString for any length >1023, preventing the streaming-in of TEXT/NTEXT data into Netezza from offline to in-DB tools (though it's OK if writing to a pre-existing table with VARCHAR column).

I'm using Alteryx v11.3.

Cheers.

[anotherusername]

FYI - I lodged notification of the Netezza issue re: >1023 long strings to the generic support mailbox and was confirmed on 28 Feb that it was being worked on as a bugfix for an upcoming release, although don't have any ref for the fix apart from the e-mail lodgement [ ref:_00DE0JJZ4._50044qMBQs:ref ]

[amks]

Any news on this issue? It has been quite a while since this was updated.

[blyons]

I just did the same test I documented earlier in this thread, using Alteryx 2020.4.5. I did 2 tests, using the latest Microsoft drivers (OLE DB Driver 18.5 for SQL Server, and ODBC Driver 17 for SQL Server).

Interestingly enough, even though this thread is still marked "under review," both tables were created with the "LongComment" field as varchar(max).

I think this should be marked "completed" or "released" or "resolved" or whatever is appropriate!

Thank you, Alteryx!