Red Gate forums :: View topic - Error: Unsupported column type - Empty
Return to www.red-gate.com RSS Feed Available

Search  | Usergroups |  Profile |  Messages |  Log in  Register 
Go to product documentation
Deployment Manager
Deployment Manager forum

Error: Unsupported column type - Empty

Search in Deployment Manager forum
Post new topic   Reply to topic
Jump to:  
Go to page 1, 2  Next
Author Message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Thu Oct 17, 2013 10:12 am    Post subject: Error: Unsupported column type - Empty Reply with quote

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]
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Mon Oct 21, 2013 12:14 pm    Post subject: Reply with quote

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

Thanks!
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Oct 22, 2013 2:59 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Tue Oct 22, 2013 3:22 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Oct 22, 2013 3:23 pm    Post subject: Reply with quote

Hi James

Thanks for getting back to me!

Let me know exactly what you need and I'll clear it with my manager
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Tue Oct 22, 2013 3:25 pm    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Oct 22, 2013 4:11 pm    Post subject: Reply with quote

Hi James

Scripting through SSMS gives us the following:

Code:
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:
EXEC sp_addtype N'FieldValueTableType', '', 'NOT NULL'
GO


If you need anything more please let me know
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Tue Oct 22, 2013 4:41 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Oct 22, 2013 4:56 pm    Post subject: Reply with quote

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]
Back to top
View user's profile Send private message
james.billings



Joined: 16 Jun 2010
Posts: 1144
Location: My desk.

PostPosted: Tue Oct 22, 2013 5:02 pm    Post subject: Reply with quote

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?
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Nov 26, 2013 10:56 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
DavidSimner



Joined: 04 Feb 2010
Posts: 49

PostPosted: Tue Nov 26, 2013 11:07 am    Post subject: Reply with quote

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.
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Nov 26, 2013 11:54 am    Post subject: Reply with quote

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:
[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
Back to top
View user's profile Send private message
DavidSimner



Joined: 04 Feb 2010
Posts: 49

PostPosted: Tue Nov 26, 2013 11:57 am    Post subject: Reply with quote

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
Back to top
View user's profile Send private message
notbenjamin



Joined: 25 Sep 2013
Posts: 18

PostPosted: Tue Nov 26, 2013 12:46 pm    Post subject: Reply with quote

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!
Back to top
View user's profile Send private message
Display posts from previous:   
Reply to topic All times are GMT + 1 Hour
Go to page 1, 2  Next
Page 1 of 2

 
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum


Powered by phpBB © 2001, 2005 phpBB Group