locked
Build engine creating deployment script with objects in wrong order RRS feed

  • Question

  • I have created a build in TFS 2010. It creates a deployment script, but the functions are being altered before the views.

    A column in the view changed. A function that references the view also has been changed. Since the function is being altered first, we get a deployment error because the function now references a non-existent column.

    I have pored over the forums and google for hours and cannot find a similar issue.

    How can I make sure that tables and views get altered/created before objects that refrence them?

    Friday, October 28, 2011 4:40 AM

All replies

  • Hello bsstrickland,

    Thanks for your post.

    Based on your description, I got to know you are using TFS build server to build and deploy your database project. I think VS can deal with the updates of the view and function with the correct order. And I did not see this issue before.

    And for three things I want to clarify with you:

    1). If you do not use TFS to deploy your database project, you just use VS to deploy it for you, will you get the same issue?

    2). Please share me the complete error messages you got. And it is better if you can also share me the build logs.

    3). Please give me the detailed steps how to reproduce it.

    Thanks,


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Friday, October 28, 2011 9:29 AM
    Moderator
  • We use TFS build server to build and deploy a database build. The source is a dbschema file and the target is a database. The project is set to  build and deploy. It builds okay with no errors, but when it actually tries to deploy is where we get the issue.

    The error message is " *** SQL01268○\\zitstrick\Build Staging\POC-SCv2.4-DBSync with prod rollback\POC-SCv2.4-DBSync with prod rollback_20111027.6\Rollback.sql○(27460,0)○.Net SqlClient Data Provider: Msg 207, Level 16, State 1, Procedure SB_TransInfo, Line 123 Invalid column name 'AutoCash'.

    After doing troubleshooting, I realized that the reason is because the function that references the view is being changed in the deploy script before the view is updated. So, it updates the function first but errors because the new column it references isn't created yet. Later in the script is when the view gets changed. The view has to be updated first, otherwise we get this error.

    This happens if I deploy the build from Visual Studio or it happens via a build definition on the TFS build server.

    Here is the command to start the build:

    vsdbcmd /a:Deploy /dd+ /cs:"Data Source=MyServer;Integrated Security=true;Initial Catalog=win" /model:win_prod.dbschema /p:TargetDatabase=win /script:Rollback.sql /p:IgnoreRoleMembership=True /p:IgnorePermissions=True /p:IgnoreLoginSids=True /p:IgnoreExtendedProperties=True /p:IgnoreUserSettingsObjects=True /p:IgnoreAuthorizer=True /p:IncludeTransactionalScripts=True /p:VerifyDeployment=True /p:DisableAndReenableDdlTriggers=False /p:GenerateDeployStateChecks=false /p:IgnoreColumnCollation=True /p:IgnoreColumnOrder=True /p:DeployDatabaseProperties=False /dsp:sql /p:ScriptDatabaseCollation=False /p:BlockIncrementalDeploymentIfDataLoss=False /p:GenerateDropsIfNotInProject=True

    I have been using TFS build for years and have never come across this.

     

    I tried pasting the build log, but it kept erroring when I click Submit.

    Friday, October 28, 2011 3:23 PM
  • Hello bsstrickland,

    Sorry for the late response.

    Do you mean you get this issue both when you deploy your database project with the TFS build machine and deploy locally? And if you also get the same issue when build locally, I think your issue is not related to TFS build.  

    And I deployed my database project by using TFS build machine, and just as you mentioned above, I do not get the same issue. I can have my databae project deployed successfully.

    Could you please kindly offer me some detailed information how to reproduce it?

    Thanks.


    Vicky Song [MSFT]
    MSDN Community Support | Feedback to us
    Get or Request Code Sample from Microsoft
    Please remember to mark the replies as answers if they help and unmark them if they provide no help.

    Tuesday, November 1, 2011 8:28 AM
    Moderator
  • Happens when I build locally and with a TFS build.

    1. Create a db project with a view (ViewA) and a function (FunctionA). Set project to "Drop items not in TFS" and deploy database.
    2. ViewA has 2 fields: ColA and ColB
    3. FunctionA references ViewA.ColA (Select ViewA.ColA*10)
    4. Deploy to a db server.
    5. Now change the name of ColA to ColZ in the view so that ViewA.ColA has been renamed to ViewA.ColZ.
    6. Also change FunctionA to reference ViewA.ColZ (Select ViewA.ColZ*10) now instead of ViewA.ColA.
    7. Deploy to the same db server as above.

    The error appears.

    Open the build script and notice that the FunctionA is being updated first so that it is referencing ColZ (which has not been created yet).

    Gives an error that ColZ does not exist (and it doesn't...yet)

    Tuesday, November 1, 2011 9:25 PM
  • Bumping in hopes of someone being able to answer.

    Tuesday, November 22, 2011 4:50 PM
  • I ran into this with VS2010 as well.

    I ended up building the DB in 2 steps, tables and views first, then the procs/functions second.

    Not a very elegant solution but it worked for my DB.


    Thanks, Matt
    Tuesday, November 29, 2011 4:24 PM
  • How do you build in 2 steps?
    Tuesday, December 27, 2011 10:15 PM
  • You can create two separate database projects, one referencing another. Leave database name in the reference empty.

    Create all tables and views in first project and then functions in second project.

    Thursday, January 19, 2012 3:20 AM