I observed this same behaviour with an NVARCHAR(Max) column .

After researching this issue, I suspect the issue is that the ODBC driver may be returning a size of 0 & Connect doesn't expect that this is a valid value. (Admittedly this is sheer conjecture)

https://msdn.microsoft.com/en-us/library/ms130896.aspx
Also see : https://msdn.microsoft.com/en-us/library/jj219209.aspx

The error appears in Heidi SQL as a dialog displaying "mariaDB_3306:Error", "SQL Error (1939): Engine CONNECT failed to discover table `dbo`.myTableName` with 'CREATE TABLE whatever (...."

Its probably not releveant but I was using Microsoft SQL Server 2014 - 12.0.4213.0 (X64) with ODBC drivers Microsoft "Sql Server Native Client 11.0" version # 2011.110.6518 & also with ODBC drivers Microsoft "ODBC Driver 11 for SQL Server" 2014.120.4219.00.

If Connect does not support these data types, it should at least offer to truncate or skip them by honoring the variables: connect_type_conv & connect_conv_size

As a workaround - a view can be created in the source database that casts unsupported data types to a supported data type (ie CAST to a varchar with a defined size).

Waiting for a fix, did you try to set the connect_type_conv variable to skip?

Otherwise, the turnaround is to manually define the columns in the create table statement. Then you can give any length to the corresponding column or not define it at all (what does skip) if it does not work.

Here is what I did:

create table test_exec (

command varchar(128) not null,

number int(5) not null flag=1,

message varchar(255) flag=2)

ENGINE=CONNECT TABLE_TYPE=ODBC CONNECTION='DSN=ConnectEngineSQLServer;Database=master' OPTION_LIST='Execsrc=1';

select * from test_exec where command = 'create table dbo.mst (a varchar(max) not null)';

select * from test_exec where command = "insert into mst values('One')";

select * from test_exec where command = "insert into mst values('Two')";

select * from test_exec where command = "insert into mst values('Three')";

CREATE TABLE test_table ENGINE=CONNECT TABLE_TYPE=ODBC TABNAME='mst' DBNAME='dbo' 

CONNECTION='DSN=ConnectEngineSQLServer;Database=master';

select * from test_table; /* Ok */

show create table test_table;

CREATE TABLE `test_table` (

 `a` varchar(8192) NOT NULL

) ENGINE=CONNECT DEFAULT CHARSET=latin1 CONNECTION='DSN=ConnectEngineSQLServer;Database=master' `TABLE_TYPE`='ODBC' `TABNAME`='mst' `DBNAME`='dbo';

Looking at what happened in debug mode, I saw that SQL SERVER returns the description of the a column as TEXT with a length of max 32 bit integer.

CONNECT, not supporting the TEXT type changed it to VARCHAR (according to the connect_type_conv variable set to yes) with a length corresponding to the connect_conv_size variable.

i mean if you create table in SQL SERVER like this :

create table a (b varchar(max))
go

engine connect don't work with this kind of field (easy to reproduce)

What do you mean by "don't work"? Have you checked the values of the connect_type_conv variable?