What are the challenges you face when working across database platforms? Take the survey
Options

Found a table valued column

markdmarkd Posts: 3 Bronze 1
edited July 9, 2015 5:10AM in SQL Prompt
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

Comments

  • Options
    Aaron LAaron L Posts: 596 New member
    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.
  • Options
    markdmarkd Posts: 3 Bronze 1
    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
  • Options
    Aaron LAaron L Posts: 596 New member
    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.
Sign In or Register to comment.