1

I am trying to use ReadyRoll project for automated deployments. the previous project that I created had the following IF TYPE_ID(N'[dbo].[abc_PrintType]') IS NULL, in the first migration script that was generated after importing the database. I want to know which option to check in the properties of the project to generate the same line of code,

GO
PRINT N'Print types'
GO
IF TYPE_ID(N'[dbo].[abc_PrintType]') IS NULL
CREATE TYPE [dbo].[abc_PrintType] AS TABLE
(...)
Gaz Winter
  • 2,924
  • 2
  • 25
  • 47
dsingh23
  • 185
  • 3
  • 14

1 Answers1

2

The option you're looking for is Add object existence checks.

This can be enabled in your project by adding the following code under the <Project> node within the .sqlproj file:

<PropertyGroup> <!-- "Add object existence checks" SQL Compare option --> <SyncOptionIncludeExistenceChecks>True</SyncOptionIncludeExistenceChecks> </PropertyGroup>

The next time you import a change, the generated script will include the IF EXISTS... style guard clauses.

More information on how to configure this can be found in the ReadyRoll documentation: https://documentation.red-gate.com/pages/viewpage.action?pageId=42539778

Dan Nolan
  • 4,733
  • 2
  • 25
  • 27
  • Thank you for the solution. Isn't there any other way to check this option? – dsingh23 Dec 07 '16 at 00:55
  • I enabled the same in .sqlproj file. The problem is my previous Migrations won't run since all the stored procs, tables are already present in the target database. There were changes to my target database and I wanted to import the same. After doing a Refresh, the project says 'Build & Deploy Required' and it won't let me deploy the project because the objects are already present in the target DB. – dsingh23 Dec 07 '16 at 01:30
  • When I view the object differences, my ReadyRoll project doesn't show that particular table. However, when I deploy the project I get an error that the following table already exists. – dsingh23 Dec 07 '16 at 01:34
  • 1
    If you're deploying to a database for the first time, you'll need to set a baseline in your project. This involves putting your migrations into a version 1.0.0 folder and switching on semver as described in the docs: https://documentation.red-gate.com/display/RR1/Working+with+existing+databases#Workingwithexistingdatabases-Preparingthedatabaseprojectforyourfirstdeploymentbaseline – Dan Nolan Dec 07 '16 at 01:46
  • I already deployed the database once. After the changes in the target database, I am deploying it for the second time. – dsingh23 Dec 07 '16 at 01:48
  • It sounds like there might be one or more script missing from the 1.0.0 folder, as it shouldn't be trying to execute a script containing objects that are already present in your target database. If you open the script that causes the deployment error and click *Mark as Deployed*, does this resolve the issue? – Dan Nolan Dec 07 '16 at 01:53
  • I am not using SemVer as of now. I can try that first and see if I get the error. – dsingh23 Dec 07 '16 at 16:36
  • I have 2 changes pending for import since they have some error. I deleted those 2 stored procs from my ReadyRoll project. When I deploy through Octopus, it gives me the following error, **The type 'dbo.abc_Type' already exists, or you do not have permission to create it.** – dsingh23 Dec 08 '16 at 23:20
  • I am using SemVer. I added the 2 following 2 properties in the.sqlproj file as well `False` and `True` The only thing that is not checked in the Properties is 'Set the baseline..'. Also, this script has no 'Mark as deployed option'. – dsingh23 Dec 08 '16 at 23:23
  • 1
    Given that it's difficult to tell what the state of your environment currently is, I'd suggest starting again: 1. Take a backup of the __MigrationLog table in the DB that you're trying to deploy in Octopus and drop it from the database, if it exists 2. Set the baseline in the project as described in the following document: https://documentation.red-gate.com/display/RR1/Working+with+existing+databases#Workingwithexistingdatabases-Preparingthedatabaseprojectforyourfirstdeploymentbaseline 3. Commit changes to source control, build and deploy the new package to Octopus – Dan Nolan Dec 09 '16 at 00:48
  • I have no idea where I am going wrong when I have followed everything. I started this project again as t was giving me issues in the first place. – dsingh23 Dec 09 '16 at 02:01
  • I'm sorry to hear that. If you're able to provide a copy of your project and contained assets (and an empty copy of your database, if possible), please send it onto support@red-gate.com and we can try and help you get un-stuck. – Dan Nolan Dec 09 '16 at 02:35
  • Ok, I can try doing that. – dsingh23 Dec 09 '16 at 04:59
  • So, I did as per your suggestion. Now, when I deploy through Octopus, it gives me the following error again, The type 'dbo.abc_Type' already exists, or you do not have permission to create it. Should I go ahead and delete the database that was earlier created for this? – dsingh23 Dec 09 '16 at 17:28
  • Just to confirm, there are 3 databases, **Target DB, Shadow DB and the DB created by Octopus**. I deleted the **__MigrationLog** table from the target DB, deleted my shadow DB, committed all the changes to source control, build through TeamCity and deployed via Octopus. – dsingh23 Dec 09 '16 at 17:58
  • Does the error occur if you delete the target database or deploy to a new database on the same server? I'm wondering if you have some objects in your **Model** database there. – Dan Nolan Dec 12 '16 at 03:51
  • I did not delete the target database. I am using an existing database. I just deleted the shadow database that was created by ReadyRoll and the __MigrationLog table from the target DB. The DB that was deployed while using Octopus and Readyroll still exists. Should I be deleting that one as well? – dsingh23 Dec 12 '16 at 17:32
  • But I have never had any issues with the Database being there that was created by Octopus. It was always overridden. – dsingh23 Dec 12 '16 at 17:34
  • Unfortunately, I cannot send a copy of the project. I can start a new project for another database that I have to automate through Octopus and ReadyRoll. Are there any points that you want me to keep in mind? other than what is mentioned in the documentation for the product? – dsingh23 Dec 12 '16 at 19:23
  • The documentation should include everything you need. The baseline section in the above article contains the key steps. Please let me know if you change your mind about sending us the project, otherwise it may be difficult to assist further. – Dan Nolan Dec 12 '16 at 22:06
  • 1
    I was able to figure out the issue. I created the second project since the first one was a mess. The database that was created by Octopus with the first project was still there with all the objects in that database. Therefore, even when I set the baseline in the second project, it was giving me the error that the object still exists. I deleted the first database that was created from the db server and took a backup of the same. – dsingh23 Dec 13 '16 at 17:21