Does the TeamCity plugin ignore /Exclude:table?

Automated deployment for web applications and databases

Does the TeamCity plugin ignore /Exclude:table?

Postby isme » Mon Dec 09, 2013 8:25 pm

Hey RG,

I'm trying to package my database structure without tables.

I set the /Exclude:tables option in the Red Gate build step.

Image

When TeamCity validates the database creation script, the build log shows that tables are still created.

Code: Select all
[19:10:42]Creating [Customer].[tbCustomer]
[19:10:42]Creating primary key [PK_tbCustomer] on [Customer].[tbCustomer]
[19:10:42]Creating [Customer].[tbCustomerPermission]
[19:10:42]Creating primary key [PK_tbCustomerPermission] on [Customer].[tbCustomerPermission]
[...]


Indeed, the CreationScript.sql in the NuGet package contains a bunch of CREATE TABLE statements.

Code: Select all
PRINT N'Creating [Customer].[tbCustomer]'
GO
CREATE TABLE [Customer].[tbCustomer] (...)
GO
[...]
PRINT N'Creating primary key [PK_tbCustomer] on [Customer].[tbCustomer]'
GO
ALTER TABLE [Customer].[tbCustomer] ADD CONSTRAINT [PK_tbCustomer] PRIMARY KEY ...
GO


The option works on my workstation, so why not in TeamCity?

Am I missing something?

Thanks!
Iain Elder, Skyscanner
isme
 
Posts: 83
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby james.billings » Tue Dec 10, 2013 4:37 pm

I'm just having a quick look at this myself- I do see something similar however the tables aren't in the original creation:

Code: Select all

[15:29:55][Generating creation script] Starting: C:\\TeamCity\\buildAgent\\plugins\\RedGateSQLServer\\assets\\SC\\SQLCompare.exe /include=\"Identical\" /include=\"StaticData\" /scripts1=\"C:\\TeamCity\\buildAgent\\temp\\buildTmp\\ogcy0gcr.e0o\\db\\state\" /o=\"default,ignoretSQLt\" /scripts2=\"C:\\TeamCity\\buildAgent\\temp\\buildTmp\\yimiyhwz.spo\" /scriptfile=\"C:\\TeamCity\\buildAgent\\temp\\buildTmp\\ogcy0gcr.e0o\\db\\CreationScript.sql\" /options=\"itst,iwn\" /abortOnWarnings=\"None\" /Exclude=\"table\"
[15:29:55][Generating creation script] SQL Compare: activated, edition: professional, serial number: 507-001-185110-EC2A
[15:29:56][Generating creation script] SQL Compare Command Line V10.5.0.378
[15:29:56][Generating creation script] ==============================================================================
[15:29:56][Generating creation script] Copyright ¸ Red Gate Software Ltd 1999-2013
[15:29:56][Generating creation script]
[15:29:56][Generating creation script] Registering data sources
[15:30:02][Generating creation script] Creating mappings
[15:30:03][Generating creation script] Comparing
[15:30:03][Generating creation script]
[15:30:03][Generating creation script] Applying Command Line Items
[15:30:03][Generating creation script]
[15:30:03][Generating creation script] Retrieving migration scripts
[15:30:03][Generating creation script] Checking for identical databases
[15:30:03][Generating creation script] Creating SQL
[15:30:05][Generating creation script] Inserting static data SQL into deployment script
[15:30:05][Generating creation script] Saving SQL
[15:30:05][Generating creation script]
[15:30:05][Generating creation script] Summary Information
[15:30:05][Generating creation script] ===============================================================================
[15:30:05][Generating creation script] DB1 = state
[15:30:05][Generating creation script] DB2 = yimiyhwz.spo
[15:30:05][Generating creation script]
[15:30:05][Generating creation script] Object type     Name                                           Records DB1 DB2
[15:30:05][Generating creation script] -------------------------------------------------------------------------------
[15:30:05][Generating creation script] Role            db_datareader                                          ==  == 
[15:30:05][Generating creation script] Role            db_accessadmin                                         ==  == 
[15:30:05][Generating creation script] Role            db_ddladmin                                            ==  == 
[15:30:05][Generating creation script] Role            db_owner                                               ==  == 
[15:30:05][Generating creation script] Role            db_securityadmin                                       ==  == 
[15:30:05][Generating creation script] Role            db_backupoperator                                      ==  == 
[15:30:05][Generating creation script] Role            db_denydatareader                                      ==  == 
[15:30:05][Generating creation script] Role            db_denydatawriter                                      ==  == 
[15:30:05][Generating creation script] Role            public                                                 ==  == 
[15:30:05][Generating creation script] Role            db_datawriter                                          ==  == 
[15:30:05][Generating creation script] Schema          db_owner                                               ==  == 
[15:30:05][Generating creation script] Schema          db_accessadmin                                         ==  == 
[15:30:05][Generating creation script] Schema          db_ddladmin                                            ==  == 
[15:30:05][Generating creation script] Schema          db_datareader                                          ==  == 
[15:30:05][Generating creation script] Schema          db_securityadmin                                       ==  == 
[15:30:05][Generating creation script] Schema          db_backupoperator                                      ==  == 
[15:30:05][Generating creation script] Schema          db_datawriter                                          ==  == 
[15:30:05][Generating creation script] Schema          db_denydatareader                                      ==  == 
[15:30:05][Generating creation script] Schema          INFORMATION_SCHEMA                                     ==  == 
[15:30:05][Generating creation script] Schema          db_denydatawriter                                      ==  == 
[15:30:05][Generating creation script] Schema          guest                                                  ==  == 
[15:30:05][Generating creation script] Schema          sys                                                    ==  == 
[15:30:05][Generating creation script] Schema          Widgets                                                >>     
[15:30:05][Generating creation script] Schema          dbo                                                    ==  == 
[15:30:05][Generating creation script] View            [dbo].[vwMoods]                                        >>     
[15:30:05][Generating creation script] View            [dbo].[vwColors]                                       >>     
[15:30:05][Generating creation script] View            [dbo].[vwContacts]                                     >>     
[15:30:05][Generating creation script] View            [dbo].[WidgetPriceList]                                >>     
[15:30:05][Generating creation script] View            [dbo].[vwMood]                                         >>     
[15:30:05][Generating creation script] View            [dbo].[View_1]                                         >>     
[15:30:05][Generating creation script] StoredProcedure [dbo].[SelectOne]                                      >>     
[15:30:05][Generating creation script] Assembly        [SQLAssembly]                                          >>     
[15:30:05][Generating creation script] ExtendedPropert       MS_Description                                   >>     
[15:30:05][Generating creation script] -------------------------------------------------------------------------------


You'll see no tables are mentioned... but then when we verify:

Code: Select all
[15:30:05][Validating creation script: Executing creation script] Creating [dbo].[colors]
[15:30:05][Validating creation script: Executing creation script] Creating primary key [PK_colors] on [dbo].[colors]
[15:30:05][Validating creation script: Executing creation script] Creating [dbo].[vwColors]


It does seem to be creating a table... also worth mentioning that the final package pushed through to Deployment Manager contains tables too.

I'll see if I can fathom it out.
james.billings
 
Posts: 1120
Joined: Wed Jun 16, 2010 11:10 am
Location: Red Gate

Postby isme » Mon Jan 20, 2014 2:54 pm

Hi James,

Jonthan Hickford explained that the compare options field is currently ignored in the packaging step.

He documented a workaround on the deployment manager website in an article called Customer Specific Database Deployments.

We followed his example. Our workaround is to filter objects before the 'Red Gate' step in a PowerShell step that invokes sqlcompate.exe with a scpf filter.

Would you be able to confirm whether the 'additional switches' functionailty will be fully implemented?

Thanks for investigating.
Iain Elder, Skyscanner
isme
 
Posts: 83
Joined: Tue Jun 12, 2012 1:49 pm
Location: Edinburgh

Postby james.billings » Mon Jan 20, 2014 2:55 pm

Adding full support for the SQL Compare options is pretty high up on the list right now AFAIK (i.e. second/third thing in the list, last time I saw) so I'm hoping that this will appear soon...
james.billings
 
Posts: 1120
Joined: Wed Jun 16, 2010 11:10 am
Location: Red Gate


Return to Deployment Manager

Who is online

Users browsing this forum: No registered users and 0 guests