Error: Unsupported column type - Empty

notbenjaminnotbenjamin Posts: 22
edited December 4, 2013 4:32AM in Deployment Manager
Hi there

I'm not sure if this is the right forum for this but I'll post it here on the off chance you're able to point me in the right direction.

I'm trying to integrate teamcity into our deployment manager setup. I've added a database project following the tutorials provided by yourselves and I'm using the team city plugin with a SQL Automation license.

The build fails when I run it with the following error message. Are you able to advise why this is occurring? I can't find anything through google fu that has helped!

[09:01:22][Generating creation script] SQL Compare Command Line V10.3.8.406
[09:01:22][Generating creation script] ==============================================================================
[09:01:22][Generating creation script] Copyright ¸ Red Gate Software Ltd 1999-2012
[09:01:22][Generating creation script]
[09:01:23][Generating creation script] Registering data sources
[09:01:43][Generating creation script] Creating mappings
[09:01:43][Generating creation script] Comparing
[09:01:43][Generating creation script]
[09:01:44][Generating creation script] Applying Command Line Items
[09:01:44][Generating creation script]
[09:02:01][Generating creation script] Error: Unsupported column type - Empty
[09:02:03][Generating creation script] ##teamcity[buildStatus status='FAILURE' text='Generating creation script failed with exit code 126']
[09:02:04]sqlCI error occurred: RedGate.SQLCI.Engine.Error: Generating creation script failed with exit code 126[/code]
· Share on Twitter

Comments

  • James BJames B Posts: 1,124 Silver 4
    Hi,
    I've just pinged you an email to get a little more information off you to start looking into this.

    Thanks!
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi James

    Thanks for your email.

    I've been doing some more on this today and yesterday - it would appear that its to do with the user defined types in our databases - I'm experiencing a similar issue to the above now when deploying a database through Deployment Manager.

    The data type '' does not exist or you do not have permission.

    Running the upgrade script manually, I found that the upgrade script has generated the following code to add a user defined table type:

    sp_addtype N'FieldValueTableType', '', 'NOT NULL'

    Reading up on this, Microsoft suggest that sp_addtype should not be used for creating table types, but CREATE TYPE should be used instead

    I have a feeling this may be the issue I've been having all along.

    Any ideas on how to fix this?
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    I've certainly seen issues in the past around UDTT, but upon looking in our bugtracker, the ones we know about look to be resolved. You may be hitting something slightly different...

    Is it possible to obtain a copy of the files so I can try to reproduce the problem here?
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi James

    Thanks for getting back to me!

    Let me know exactly what you need and I'll clear it with my manager
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    I think ideally the source files that Compare is running off (is it perhaps a copy of your DB in a source control repo, stored as scripts?) We don't need any data.

    Feel free to reply directly to the support ticket email you should have received if you need to clarify anything.
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi James

    Scripting through SSMS gives us the following:
    USE [VALIDATOR2]
GO

/****** Object:  UserDefinedTableType [dbo].[FieldValueTableType]    Script Date: 10/22/2013 16:03:28 ******/
CREATE TYPE [dbo].[FieldValueTableType] AS TABLE(
	[FieldName] [varchar](100) NOT NULL,
	[FieldValue] [varchar](100) NOT NULL
)
GO


    Pulling the file from the Source Control check in gives us this:
    EXEC sp_addtype N'FieldValueTableType', '', 'NOT NULL'
GO

    If you need anything more please let me know
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    Hmm, I created a new database and ran the 1st (SSMS) script you posted to create the object. I then linked my DB to a source control repository and committed the objects. Both the file in the Working Base and the file in the repository are still formatted as per the former method, not the latter.

    What version of SQL Source control are you on? Also, if you look in your repo, there is a file in the root called "RedGateDatabaseInfo.xml" - if you open that up in Notepad, what is the "DatabaseVersion" tag set to?
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    I'm on 3.5.1.69 but the original check in was done by someone who is currently on 3.4

    Database Version set to 10[/i]
    · Share on Twitter
  • James BJames B Posts: 1,124 Silver 4
    The first recommendation would be to ensure all users are on the same version, otherwise odd behaviour can result (especially if the polling query we use changed between versions).
    10 in the XML file would refer to SQL 2008; is that the version you're using?
    Systems Software Engineer

    Redgate Software

    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi James

    Apologies for the delay in replying on this - I'd been moved onto other projects.

    I'm back on this now and am still receiving this error (on two databases now) - both use User Defined Data Types and User Defined Table Types

    In answer to your last question - I am using SQL 2008.
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    I've had a quick look through the code and it looks like if the target is SQL 2000 or earlier, we script out using "sp_addtype", otherwise, we script out using "CREATE TYPE".

    To get you back up and running, can you delete the objects from your source control system, and then re-commit them. This should mean they get committed as "CREATE TYPE", which should hopefully not cause any problems.

    Let me/us know how this goes.
    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Thanks David

    Did that and it committed into source control as create type so that looks good (haven't tried the second database yet, will do that once I get the first one working)

    The integration build on team city is throwing up a new error now tho
    [10:50:23]Generating 1.0.1 upgrade script (24s)
[10:50:23][Generating 1.0.1 upgrade script] Starting: E:\TeamCityBuildServer\buildAgent2\plugins\RedGateSQLServer\assets\SC\SQLCompare.exe /include="Identical" /include="StaticData" /scripts1="E:\TeamCityBuildAgent2\temp\buildTmp\afcqpijf.uj2\db\state" /o="default,ignoretSQLt" /scripts2="E:\TeamCityBuildAgent2\temp\buildTmp\l4btndwm.ikc\db\state" /scriptfile="E:\TeamCityBuildAgent2\temp\buildTmp\afcqpijf.uj2\db\upgrades\1.0.1\UpgradeScript.sql" /abortOnWarnings="High"
[10:50:23][Generating 1.0.1 upgrade script] Automation License: activated, edition: standard, serial number: 048-001-216039-419B
[10:50:23][Generating 1.0.1 upgrade script] SQL Compare Command Line V10.3.8.406
[10:50:23][Generating 1.0.1 upgrade script] ==============================================================================
[10:50:23][Generating 1.0.1 upgrade script] Copyright ¸ Red Gate Software Ltd 1999-2012
[10:50:23][Generating 1.0.1 upgrade script] 
[10:50:24][Generating 1.0.1 upgrade script] Registering data sources
[10:50:31][Generating 1.0.1 upgrade script] Creating mappings
[10:50:31][Generating 1.0.1 upgrade script] Comparing
[10:50:31][Generating 1.0.1 upgrade script] 
[10:50:31][Generating 1.0.1 upgrade script] Applying Command Line Items
[10:50:31][Generating 1.0.1 upgrade script] 
[10:50:45][Generating 1.0.1 upgrade script] Error: The given key was not present in the dictionary.
[10:50:47][Generating 1.0.1 upgrade script] ##teamcity[buildStatus status='FAILURE' text='Generating 1.0.1 upgrade script failed with exit code 126']
[10:50:48]sqlCI error occurred: RedGate.SQLCI.Engine.Error: Generating 1.0.1 upgrade script failed with exit code 126
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    Okay, that looks like a bug; so that I can debug it can you please send me the two things it's comparing when it crashes:
    1. The package of your database with version number 1.0.1
    2. The latest copy of the scripts folder

    If you email them to me at david.simner@red-gate.com I'll take a look.

    Thanks!

    David
    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi David

    I've got the package ready for you - can you confirm what you mean by the scripts folder? Just want to make sure I send you the right thing

    Thanks!
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    Cool :)

    By scripts folder I mean whatever the build server checks out from source control.
    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Cheers David

    I've emailed them to you
    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi David

    I'm struggling a little with the second database that has this issue - I can't find any reference to sp_addtype in the packages or source control at all, yet I'm getting the same issue!
    Error: Unsupported column type - Empty

    Any ideas?
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    Thanks for sending the files through. I've been able to reproduce the issue on SQL Compare version 10.3.8.406 (which from your copy/pasted output of TeamCity is the version you're using).

    It looks like the issue is fixed in SQL Compare version 10.5.0.378, in that I can no longer reproduce it on that version.

    If you upgrade the TeamCity plugin, it will mean you start using the latest version of SQL Compare. You can download it from: http://www.red-gate.com/dynamic/product ... k/download (We now distribute the TeamCity plugin via the SQL Automation Pack, so once you've installed it, you can get to the newest version of the TeamCity plugin in the top-right corner of the "What's included?" page.)
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    Let me know what happens with the second database after you've upgraded the plugin.

    If it's still failing, then send me the snippet, and I'll tell you what files I need to debug that problem...
    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Thanks David - has fixed it for the first database.

    Still getting it on the second database
    Generating creation script (45s)
[15:51:40][Generating creation script] Starting: E:\TeamCityBuildServer\buildAgent2\plugins\RedGateSQLServer\assets\SC\SQLCompare.exe /include="Identical" /include="StaticData" /scripts1="E:\TeamCityBuildAgent2\temp\buildTmp\hejhd3bz.yth\db\state" /o="default,ignoretSQLt" /scripts2="E:\TeamCityBuildAgent2\temp\buildTmp\x2vsp1m2.tyg" /scriptfile="E:\TeamCityBuildAgent2\temp\buildTmp\hejhd3bz.yth\db\CreationScript.sql" /abortOnWarnings="High"
[15:51:40][Generating creation script] Automation License: activated, edition: standard, serial number: 048-001-216039-419B
[15:51:40][Generating creation script] SQL Compare Command Line V10.5.0.378
[15:51:40][Generating creation script] ==============================================================================
[15:51:41][Generating creation script] Copyright ¸ Red Gate Software Ltd 1999-2013
[15:51:41][Generating creation script] 
[15:51:41][Generating creation script] Registering data sources
[15:51:55][Generating creation script] Creating mappings
[15:51:55][Generating creation script] Comparing
[15:51:55][Generating creation script] 
[15:51:55][Generating creation script] Applying Command Line Items
[15:51:55][Generating creation script] 
[15:52:25][Generating creation script] Error: Unsupported column type - Empty
[15:52:26][Generating creation script] ##teamcity[buildStatus status='FAILURE' text='Generating creation script failed with exit code 126.  For generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare']
[15:52:26]sqlCI error occurred: RedGate.SQLCI.Engine.Error: Generating creation script failed with exit code 126.  For generic information about this exit code, see: http://www.red-gate.com/sqlCI/ExitCodes/SQLCompare
[15:52:26]A detailed error report will be sent to Red Gate.
[15:52:26]These reports go into our issue tracking system, and help us fix bugs.
[15:52:26]For information about what's contained in the report, see: http://redg.at/RY3TiS
[15:52:26]Sending error report to Red Gate: Preparing Report...
[15:52:26]Sending error report to Red Gate: Connecting to Server...
[15:52:29]Sending error report to Red Gate: Transferring...
[15:52:29]Sending error report to Red Gate: Finished.  Our reference number is: {6c102998-4ff9-46f9-bec3-e53d9259ba8c}
[15:52:29]Process exited with code -532462766
[15:52:29]Publishing internal artifacts
[15:52:29][Publishing internal artifacts] Sending build.finish.properties.gz file
[15:52:29]Build finished
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    It's awesome to hear that it's now working on the first database :)

    The second database is failing at the stage where it generates the creation script, so to debug it I only need a copy of the latest scripts folder (the thing TeamCity is checking out). If you email that across, then I can take a look at why it's not working.
    · Share on Twitter
  • notbenjaminnotbenjamin Posts: 22
    Hi David

    I've emailed it to you

    Thanks for your continued help on this one!
    · Share on Twitter
  • DavidRGDavidRG Posts: 60 Bronze 4
    For the sake of completeness of this forum thread.

    We've followed up by email, and our internal reference for a static data table having a user-defined type as one of the fields of its primary key, i.e. the "Error: Unsupported column type - Empty" bug is SC-6650
    · Share on Twitter
Sign In or Register to comment.