Error: Unsupported column type - Empty

Automated deployment for web applications and databases

Moderators: Mike Upton, justin.caldicott, Sean.newham, csmith, chirayu, DavidSimner, david.conlin

Error: Unsupported column type - Empty

Postby notbenjamin » Thu Oct 17, 2013 10:12 am

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]
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby james.billings » Mon Oct 21, 2013 12:14 pm

Hi,
I've just pinged you an email to get a little more information off you to start looking into this.

Thanks!
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby notbenjamin » Tue Oct 22, 2013 2:59 pm

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?
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby james.billings » Tue Oct 22, 2013 3:22 pm

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?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby notbenjamin » Tue Oct 22, 2013 3:23 pm

Hi James

Thanks for getting back to me!

Let me know exactly what you need and I'll clear it with my manager
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby james.billings » Tue Oct 22, 2013 3:25 pm

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.
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby notbenjamin » Tue Oct 22, 2013 4:11 pm

Hi James

Scripting through SSMS gives us the following:

Code: Select all
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:

Code: Select all
EXEC sp_addtype N'FieldValueTableType', '', 'NOT NULL'
GO


If you need anything more please let me know
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby james.billings » Tue Oct 22, 2013 4:41 pm

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?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby notbenjamin » Tue Oct 22, 2013 4:56 pm

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]
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby james.billings » Tue Oct 22, 2013 5:02 pm

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?
james.billings
 
Posts: 1144
Joined: Wed Jun 16, 2010 11:10 am
Location: My desk.

Postby notbenjamin » Tue Nov 26, 2013 10:56 am

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.
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby DavidSimner » Tue Nov 26, 2013 11:07 am

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.
DavidSimner
 
Posts: 49
Joined: Thu Feb 04, 2010 6:05 pm

Postby notbenjamin » Tue Nov 26, 2013 11:54 am

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

Code: Select all
[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
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Postby DavidSimner » Tue Nov 26, 2013 11:57 am

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
DavidSimner
 
Posts: 49
Joined: Thu Feb 04, 2010 6:05 pm

Postby notbenjamin » Tue Nov 26, 2013 12:46 pm

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!
notbenjamin
 
Posts: 18
Joined: Wed Sep 25, 2013 1:22 pm

Next

Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests