Deploy not working Correctly

Question

1. Hello, I used the Import Database and Objects, I imported the SOURCE database, I meant the newest one with new tables, fields, etc.

2. I created a database comparisson on the schema comparisson folder.

On the source I choosed Database project and on the target the old database that needs to be updated with new fields, etc.

Then I clicked Deploy and take a look at the result.

Questions:

1. Why it generated a CREATE TABLE, if that table already exists, I need that it Generates an Alter table with an ADD COLUMN as the schema comparisson did

/*
Deployment script for EPM.Aguas.ROP.DBComparisson
*/

GO
SET ANSI_NULLS, ANSI_PADDING, ANSI_WARNINGS, ARITHABORT, CONCAT_NULL_YIELDS_NULL, QUOTED_IDENTIFIER ON;

SET NUMERIC_ROUNDABORT OFF;


GO
:setvar DatabaseName "EPM.Aguas.ROP.DBComparisson"
:setvar DefaultDataPath ""

GO
USE [master]

GO
:on error exit
GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL
    AND DATABASEPROPERTYEX(N'$(DatabaseName)','Status') <> N'ONLINE')
BEGIN
    RAISERROR(N'The state of the target database, %s, is not set to ONLINE. To deploy to this database, its state must be set to ONLINE.', 16, 127,N'$(DatabaseName)') WITH NOWAIT
    RETURN
END

GO
IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

GO
PRINT N'Creating $(DatabaseName)...'
GO
CREATE DATABASE [$(DatabaseName)]
    ON 
    PRIMARY(NAME = [DB_COMPARENUEVA], FILENAME = '$(DefaultDataPath)$(DatabaseName).mdf', MAXSIZE = UNLIMITED, FILEGROWTH = 1024 KB)
    LOG ON (NAME = [DB_COMPARENUEVA_log], FILENAME = '$(DefaultDataPath)$(DatabaseName)_log.ldf', MAXSIZE = 2097152 MB, FILEGROWTH = 10 %) COLLATE SQL_Latin1_General_CP1_CI_AS
GO
EXECUTE sp_dbcmptlevel [$(DatabaseName)], 90;


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ANSI_NULLS ON,
                ANSI_PADDING ON,
                ANSI_WARNINGS ON,
                ARITHABORT ON,
                CONCAT_NULL_YIELDS_NULL ON,
                NUMERIC_ROUNDABORT OFF,
                QUOTED_IDENTIFIER ON,
                ANSI_NULL_DEFAULT ON,
                CURSOR_DEFAULT LOCAL,
                RECOVERY FULL,
                CURSOR_CLOSE_ON_COMMIT OFF,
                AUTO_CREATE_STATISTICS ON,
                AUTO_SHRINK OFF,
                AUTO_UPDATE_STATISTICS ON,
                RECURSIVE_TRIGGERS OFF 
            WITH ROLLBACK IMMEDIATE;
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_CLOSE OFF 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET ALLOW_SNAPSHOT_ISOLATION OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET READ_COMMITTED_SNAPSHOT OFF;
    END


GO
IF EXISTS (SELECT 1
           FROM   [master].[dbo].[sysdatabases]
           WHERE  [name] = N'$(DatabaseName)')
    BEGIN
        ALTER DATABASE [$(DatabaseName)]
            SET AUTO_UPDATE_STATISTICS_ASYNC OFF,
                PAGE_VERIFY NONE,
                DATE_CORRELATION_OPTIMIZATION OFF,
                DISABLE_BROKER,
                PARAMETERIZATION SIMPLE 
            WITH ROLLBACK IMMEDIATE;
    END


GO
IF IS_SRVROLEMEMBER(N'sysadmin') = 1
    BEGIN
        IF EXISTS (SELECT 1
                   FROM   [master].[dbo].[sysdatabases]
                   WHERE  [name] = N'$(DatabaseName)')
            BEGIN
                EXECUTE sp_executesql N'ALTER DATABASE [$(DatabaseName)]
    SET TRUSTWORTHY OFF,
        DB_CHAINING OFF 
    WITH ROLLBACK IMMEDIATE';
            END
    END
ELSE
    BEGIN
        PRINT N'The database settings for DB_CHAINING or TRUSTWORTHY cannot be modified. You must be a SysAdmin to apply these settings.';
    END


GO
USE [$(DatabaseName)]

GO
IF fulltextserviceproperty(N'IsFulltextInstalled') = 1
    EXECUTE sp_fulltext_database 'enable';


GO

GO
/*
 Pre-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be executed before the build script.	
 Use SQLCMD syntax to include a file in the pre-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the pre-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO

GO
PRINT N'Creating dbo.Tabla1...';


GO
CREATE TABLE [dbo].[Tabla1] (
    [idCampo1] INT        NOT NULL,
    [Campo1]   NCHAR (10) NOT NULL,
    [Campo3]   NCHAR (10) NULL
);


GO
PRINT N'Creating dbo.PK_Tabla1...';


GO
ALTER TABLE [dbo].[Tabla1]
    ADD CONSTRAINT [PK_Tabla1] PRIMARY KEY CLUSTERED ([idCampo1] ASC) WITH (ALLOW_PAGE_LOCKS = ON, ALLOW_ROW_LOCKS = ON, PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF, STATISTICS_NORECOMPUTE = OFF);


GO
PRINT N'Creating AutoCreatedLocal...';


GO
CREATE ROUTE [AutoCreatedLocal]
    AUTHORIZATION [dbo]
    WITH ADDRESS = N'LOCAL';


GO

GO
/*
Post-Deployment Script Template							
--------------------------------------------------------------------------------------
 This file contains SQL statements that will be appended to the build script.		
 Use SQLCMD syntax to include a file in the post-deployment script.			
 Example:      :r .\myfile.sql								
 Use SQLCMD syntax to reference a variable in the post-deployment script.		
 Example:      :setvar TableName MyTable							
               SELECT * FROM [$(TableName)]					
--------------------------------------------------------------------------------------
*/

GO

GO

GO
ALTER DATABASE [$(DatabaseName)]
    SET MULTI_USER 
    WITH ROLLBACK IMMEDIATE;


GO

---

MCPD ENTERPRISE APPLICATIONS DEVELOPER http://wantmvp.blogspot.com/

Answer 1

Hi Luis,

It seems that you are getting the schema comparison tool and our deployment a bit mixed together.  When you do a schema compare and choose a source and target, it will generate results for you in the preview pane and you click the "Write Updates" in the schema comparison window to apply the changes. 

When trying to do the deploy, you probably do not have a database connection specified in your project properties and you are deploying to a SQL script, thus it treats it as a new database.  Try setting your database connection and deploying to database in your deploy options. 

I think this is the same answer to one of your other posts, so please let us know it both questions are answered.

Thanks,
Jill

Answer 2

Do you have the project set to "Always recreate Database"?

Answer 3

And do you have a target connection specified in the Deploy properties, otherwise you always and only get a new deployment script from Deploy.

But the fact that this fragment is in your script

IF (DB_ID(N'$(DatabaseName)') IS NOT NULL) 
BEGIN
    ALTER DATABASE [$(DatabaseName)]
    SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
    DROP DATABASE [$(DatabaseName)];
END

Implies that Brian is correct that it seems that Always Recreate is turned on.

---

GertD @ www.DBProj.com

Answer 4

Hi. Alwas recreate was set to False, it wasnt turned on.

But. As you said in the project options there was no target database selected, after I configured this and click on Deploy, the generated script looks fine  because it generated an ALTER TABLE with and ADD COLUMN


ALTER TABLE [dbo].[Tabla1]
    ADD [Campo3] NCHAR (10) NULL;

GO

---

MCPD ENTERPRISE APPLICATIONS DEVELOPER http://wantmvp.blogspot.com/