Does the TeamCity plugin ignore /Exclude:table?

isme

Hey RG,

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

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



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

[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.

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!

James B

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

[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:

[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.

isme

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.

James B

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...