Community Spring Cleaning week is here! Join your fellow Maveryx in digging through your old posts and marking comments on them as solved. Learn more here!
The Product Idea boards have gotten an update to better integrate them within our Product team's idea cycle! However this update does have a few unique behaviors, if you have any questions about them check out our FAQ.

Alteryx Designer Desktop Ideas

Share your Designer Desktop product ideas - we're listening!
Submitting an Idea?

Be sure to review our Idea Submission Guidelines for more information!

Submission Guidelines

Datatypes text and ntext in SQL Server will be depracated

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.

18 Comments
Frank
6 - Meteoroid

Above results in this kind of SQL query's:

SELECT  *
FROM     [concept_work_servicedesk].[PostgreSQL].[_http_requests] AS T
WHERE  CONVERT(varchar(MAX), T.vizql_session) = 'AAAAA621343E418B8E186984D3025962-0:1'
 AND       CONVERT(varchar(MAX), completed_at_chr) = '2016-01-25 11:57:46.9910000'

 

Notice the conversion.

blyons
11 - Bolide

I strongly encourage this ASAP. TEXT, NTEXT and IMAGE data types were deprecated since SQL Server 2005 (https://msdn.microsoft.com/en-us/library/ms143729(v=SQL.90).aspx).

stephen81
6 - Meteoroid

This is causing me a lot of extra work. Can this be changed please?

SeanAdams
17 - Castor
17 - Castor

Fully agree with this suggestion - we ran into this just yesterday.

Please can we eliminate the use of Text and nText from Alteryx as SQL output types.

gregh
7 - Meteor

agreed -we too have this problem, and have had to set up specific checks on the database to remedy it.

ARich
Alteryx Alumni (Retired)
Status changed to: Under Review

Hi All,

 

Thanks for the feedback. We're looking into this.

 

Best,

Alex

 

@RichardC

anotherusername
8 - Asteroid

In the interim, how do you force it to remain an NVARCHAR?  Lengths up to 1023 are fine, but 1024 onwards converts to TEXT or NTEXT.

 

Tried searching on the forums but haven't found a solution.

 

(I'm using Netezza, which doesn't have TEXT/NTEXT, so the workflow fails to even write.  If I have an existing DB table defined then it's fine, but if I'm creating a new table, or streaming data into an in-DB tool, then it tries to use TEXT or NTEXT and errors out with "ERROR: Invalid datatype - TEXT")

blyons
11 - Bolide

@anotherusername, It isn't easy. You would need to build your own macro that reads the field types, creates dynamic SQL code using your desired data types instead of Alteryx's, and creates the table.

 

And, FWIW, this Idea is specific to SQL Server. If you want it to apply to Netezza, you might want to post another.

 

MaudyP
Alteryx Alumni (Retired)

@SeanAdams, Can you please tell us what version of SQL Server and what version of Alteryx you are using?

As of Alteryx 11.0, we support the defaulting to VARCHAR for SQL Server 2016 and above.

 

https://docs.microsoft.com/en-us/sql/database-engine/deprecated-database-engine-features-in-sql-serv...

 

blyons
11 - Bolide

I'm sorry, @MaudyP, but I beg to differ. I am running Alteryx Designer 11.7.4.37815, and SQL Server 2016 (13.0.4466.4). I just this simple workflow to test this:

create table test.gif

 

It selects records from a table with this structure:

[RecordId] [bigint] IDENTITY(1,1) NOT FOR REPLICATION NOT NULL,
[AccountNumber] [bigint] NOT NULL,
[Date] [datetime] NOT NULL,
[CommunicationType] [varchar](10) NULL,
[ShortComment] [varchar](70) NULL,
[LongComment] [varchar](max) NULL,
[ResponseId] [bigint] NULL,
[InboundOrOutbound] [char](1) NULL,
[ExternalDocumentAddress] [varchar](900) NULL,
[SourceCode] [varchar](10) NULL,
[AssignedToAccessorId] [bigint] NULL,
[Status] [char](1) NULL,

 

and it created a table with this structure:

[RecordId] [bigint] NULL,
[AccountNumber] [bigint] NULL,
[Date] [datetime] NULL,
[CommunicationType] [varchar](10) NULL,
[ShortComment] [varchar](70) NULL,
[LongComment] [text] NULL,
[ResponseId] [bigint] NULL,
[InboundOrOutbound] [char](1) NULL,
[ExternalDocumentAddress] [varchar](900) NULL,
[SourceCode] [varchar](10) NULL,
[AssignedToAccessorId] [bigint] NULL,
[Status] [char](1) NULL

 

Note the red [text] field.

 

bl