MSSQL string field mapping

Hi, why all filed in SQL command are translated as varchar(8000)?

Why a Text field in DataModeller is translated to "image" type when SQL command is executed?

What do you mean by "field in SQL command translated to Varchar(8000)?". Which SQL command? Aurelius doesn't create any field with size 8000. What it does when you don't specify the size of db column is to use the default value in global config, which is 255 by default, not 8000. Unless you have changed it.

About Text field, yes, that is by default, it's considered as a blob by Aurelius.

exec sp_prepare @p1 output,N'@P1 varchar(8000),@P2 int,@P3 int,@P4 int,@P5 int,@P6 int,@P7 int,@P8 varchar(8000),@P9 varchar(8000),@P10 varchar(8000),@P11 varchar(8000),@P12 varchar(8000),@P13 varchar(8000),@P14 varchar(8000),@P15 
varchar(8000),@P16 varchar(8000),@P17 varchar(8000),@P18 decimal(30,6),@P19 varchar(8000),@P20 varchar(8000),@P21 varchar(8000),@P22 varchar(8000),@P23 varchar(8000),@P24 varchar(8000),@P25 varchar(8000),@P26 varchar(8000),@P27 
varchar(8000),@P28 varchar(8000),@P29 varchar(8000),@P30 varchar(8000),@P31 varchar(8000),@P32 varchar(8000),@P33 varchar(8000),@P34 varchar(8000),@P35 varchar(8000),@P36 varchar(8000),@P37 varchar(8000),@P38 varchar(8000),@P39 
varchar(8000),@P40 varchar(8000),@P41 varchar(8000),@P42 varchar(8000),@P43 varchar(8000),@P44 varchar(8000),@P45 varchar(8000),@P46 varchar(8000),@P47 varchar(8000),@P48 varchar(8000),@P49 varchar(8000),@P50 varchar(8000),@P51 
varchar(8000),@P52 varchar(8000),@P53 varchar(8000),@P54 varchar(8000),@P55 varchar(8000),@P56 varchar(8000),@P57 int,@P58 varchar(8000),@P59 varchar(8000),@P60 varchar(8000),@P61 varchar(8000),@P62 varchar(8000),@P63 
varchar(8000),@P64 varchar(8000),@P65 varchar(8000),@P66 varchar(8000),@P67 varchar(8000),@P68 varchar(8000),@P69 varchar(8000),@P70 varchar(8000),@P71 varchar(8000),@P72 varchar(8000),@P73 bit,@P74 varchar(8000),@P75 datetime2,@P76 
varchar(8000),@P77 varchar(8000),@P78 varchar(8000),@P79 varchar(8000),@P80 varchar(8000),@P81 varchar(8000),@P82 varchar(8000),@P83 bit,@P84 varchar(8000),@P85 varchar(8000),@P86 varchar(8000),@P87 varchar(8000),@P88 varchar(8000),@P89 varchar(8000),@P90 varchar(8000),@P91 varchar(8000),@P92 decimal(30,6),@P93 varchar(8000),@P94 varchar(8000),@P95 bit,@P96 bit,@P97 bit,@P98 bit,@P99 bit,@P100 bit,@P101 bit,@P102 bit,@P103 bit,@P104 bit,@P105 bit,@P106 bit,@P107 bit,@P108 varchar(8000),@P109 varchar(8000),@P110 varchar(8000),@P111 varchar(8000),@P112 varchar(8000),@P113 varchar(8000),@P114 varchar(8000),@P115 varchar(8000),@P116 varchar(8000),@P117 varchar(8000),@P118 varchar(8000),@P119 bit,@P120 bit,@P121 int,@P122 bit,@P123 bit,@P124 image,@P125 varchar(8000),@P126 decimal(30,6),@P127 varchar(8000),@P128 bit,@P129 varchar(8000),@P130 varchar(8000),@P131 varchar(8000),@P132 varchar(8000),@P133 bit,@P134 bit,@P135 bit,@P136 bit,@P137 varchar(8000),@P138 varchar(8000),@P139 decimal(30,6),@P140 varchar(8000),@P141 varchar(8000),@P142 bit,@P143 bit,@P144 bit,@P145 bit,@P146 bit,@P147 varchar(8000),@P148 varchar(8000),@P149 bit,@P150 bit,@P151 varchar(8000),@P152 varchar(8000),@P153 varchar(8000),@P154 varchar(8000),@P155 varchar(8000),@P156 decimal(30,6),@P157 decimal(30,6),@P158 varchar(8000),@P159 varchar(8000),@P160 varchar(8000),@P161 varchar(8000),@P162 varchar(8000),@P163 int,@P164 varchar(8000),@P165 bit,@P166 decimal(30,6),@P167 decimal(30,6),@P168 decimal(30,6),@P169 int,@P170 decimal(30,6),@P171 varchar(8000),@P172 datetime2,@P173 bit,@P174 bit,@P175 varchar(8000),@P176 varchar(8000),@P177 varchar(8000),@P178 varchar(8000),@P179 varchar(8000),@P180 varchar(8000),@P181 varchar(8000),@P182 bit,@P183 bit,@P184 bit,@P185 bit,@P186 bit,@P187 varchar(8000),@P188 varchar(8000),@P189 varchar(8000),@P190 varchar(8000),@P191 varchar(8000),@P192 varchar(8000),@P193 varchar(8000),@P194 varchar(8000)',N'UPDATE myTable....

This is the prepare command in MSSQL.

Whan I upadte a Text field with Flush command I got an error: image is incompatible with text.

That is an issue with Data Modeler and SQL Server text fields. For now,  you just manually change the generated properties for those fields from TBlob to string, and add a [Column] attribute specifying the size parameter with a value higher than 65535, this will tell Aurelius the field is a text large object, not binary.

I don't know about the varchar(8000) though, what is that, something from profiler?

Hi Wagner - this was a long ago topic but I found because in our Profiler I found all our REST calls in our TMS App Server were using varchar(8000) for all params. Has anything been learned since then that we could adjust to keep this from happening?



HI @Rhett_Price, TMS Data Modeler now has the customization scripts feature which is great for such flexibility. You can simply create a script now that generates the Aurelius field with the type you want.

One option is still generate those fields as string with size 65536, or add an atribute [DBTypeWideMemo] to those properties so Aurelius know it's a memo field, not binary blob field.

1 Like