Found a table valued column

markd

I see this warning in the log for many of our columns which are actually a custom user-defined data type of Decimal(28,18). This used to work in previous versions of SQL Prompt, but not in this version. Any suggestions?

Here is the log entry:
07 Jul 2015 14:03:00,356 [10] WARN . - Found a table valued column Qty! type 257 for column Qty

SQL Prompt 6.5.0.336
SSMS 2014 12.0.2000.8

Aaron L

Hi Mark,

I'm looking into this now but I'm struggling to reproduce it on my local server. For some reason Prompt thinks your datatype is a table valued type, to help narrow down why this is the case:

1. Which version of SQL server is the database on?
2. If you run

   SELECT * FROM sys.types WHERE user_type_id = 257
   

   Against your database what do you get returned?
3. Could you send through the creation script for the user defined type (I tried with "CREATE TYPE DecType FROM Decimal(28,18)" but I'm wondering if I might need something else to recreate it)

Thanks!
Aaron.

markd

SQL Server version:
10.50.6000

sys.types result:
<name>MeasureType</name>
<system_type_id>106</system_type_id>
<user_type_id>257</user_type_id>
<schema_id>13</schema_id>
<max_length>13</max_length>
<precision>28</precision>
<scale>18</scale>
<is_nullable>1</is_nullable>
<is_user_defined>1</is_user_defined>
<is_assembly_type>0</is_assembly_type>
<default_object_id>0</default_object_id>
<rule_object_id>0</rule_object_id>
<is_table_type>0</is_table_type>

Data type script:
CREATE TYPE [Internal].[MeasureType] FROM [DECIMAL](28, 18) NULL


Thanks again, please let me know if you need anything else

Mark

Aaron L

Thanks for the extra info Mark!

As the Prompt query uses "is_table_type" to determine if the datatype is a table valued column I was hoping it was erroneously returning 1 for you but it seems like that's not the case.

Unfortunately we're still having difficulty recreating the same behaviour here. So there must be something extra that we're missing.
Would it be possible to create a new database on the same server with your custom datatype, along with a table containing a single column using it to see if it causes the same warning? Hopefully this will help tell us if it's something on the database or server. If you have access to any other servers it'd also be really helpful to try the same thing on a different server too.

Thanks!
Aaron.