locked
Database deploy fails with "Procedure could not be imported but one or more of these objects exist in your source."

    Question

  • We are using VS 2010 SP1 along with TFS 2010 SP1.  We have our build process template setup to build our TF Database project and automatically deploy it to our database.  Everything compiles and the database deploy script gets generated properly, but when it actually tries to run the deployment script against the database, it often fails with the following error:

    -------------------------------------------------------------------

    Database.dbschema: Procedure could not be imported but one or more of these objects exist in your source.
    Database.dbschema: Errors occurred while modeling the target database.  Deployment can not continue.

    -------------------------------------------------------------------

    If we simply re-run the build enough times it will eventually succeed, so we know that the problem isn't with our code.

    We are using a MSBuild task in our TFS Build Workflow to do the generating of the deploy script, and deployment to the databases.  Below are the arguments that we pass to MSBuild, using a target of "Deploy":

    "/p:TargetDatabase=" + DatabaseName + "  /p:""TargetConnectionString=" + ClientConnectionString + """ /p:DeployToDatabase=true" + " /p:DeployScriptFileName=" + DatabaseName + "DeployScript.sql"

    This is a real annoyance, as it takes a little over an hour to complete a successful build (we have a very large solution), and it easily wastes a day if we need to re-run the build 4 times before it actually passes.  Any ideas or suggestions would be greatly appreciated.  Thanks.

    -- Edit -- As I mention below, this same error also occurs when manually running from MSBuild and not using the TFS Build Workflow at all -- Edit --


    - Dan - "Can't never could do anything"
    • Edited by deadlydog Wednesday, September 14, 2011 4:25 PM
    Tuesday, September 13, 2011 11:02 PM

All replies

  • Do you have any dependencies in your database that will clear when the build proceeds step by step ?

    -Jens


    Jens K. Suessmeyer http://blogs.msdn.com/Jenss
    Wednesday, September 14, 2011 6:11 AM
    Moderator
  • No, and I forgot to mention that some employees get this same error sometimes when manually deploying to the database using MSBuild from the command prompt (not using the TFS Build Workflow at all).  So just opening up a "cmd" command prompt, launching the Visual Studio Command Prompt (2010) using "%ProgramFiles(x86)%\Microsoft Visual Studio 10.0\VC\bin\vcvars32.bat", and then entering the following command to deploy the DB project to the database:

    MSBuild /target:Deploy /property:TargetDatabase=DatabaseName;TargetConnectionString="Data Source=SQLServer001;Integrated Security=True;Pooling=False" /property:DeployToDatabase="true" Database\Database.dbproj

    So I am pretty sure this is actually a problem with MSBuild itself, not the TFS Build Process Workflow, so I hope this is still the correct forum for this issue.  One thing worth mentioning is that we haven't seen this problem yet when deploying directly from within Visual Studio, so hopefully that provides a clue as to what might be causing the problem or a possible work around.  Thanks.


    - Dan - "Can't never could do anything"

    • Edited by deadlydog Wednesday, September 14, 2011 4:36 PM
    Wednesday, September 14, 2011 4:23 PM
  • Scratch that, a co-worker just reproduced this when doing a "Deploy Solution" through visual studio, so this problem isn't just specific to MSBuild (although I think VS uses MSBuild in the back-end anyways, but I'm not certain).

    - Dan - "Can't never could do anything"
    Thursday, September 15, 2011 10:17 PM
  • A little more info on this.  The actual compilation of the .dbproj project succeeds.  It looks like the operation fails during the generation of the deploy script, not while executing the generated script. I also turned the verbosity of the output to Diagnostic, but it still doesn't tell me which stored procedure is causing this script generation to fail.

    Any thoughts/suggestions would be greatly appreciated.


    - Dan - "Can't never could do anything"
    • Edited by deadlydog Friday, September 16, 2011 11:20 PM
    Friday, September 16, 2011 9:59 PM
  • We're still having this problem.  A hint may be that this problem occurs much more often in our build workflow when deploying to our databases (about 11 of them) in parallel (using a parallel foreach loop).  It still occurs when deploying to our databases in serial, but not as often.  Any ideas or suggestions at all would really be appreciated, as this problem is really killing our deployment to the Test environment process :(
    - Dan - "Can't never could do anything"
    Wednesday, October 05, 2011 3:09 AM
  • Hi Dan,

    Have you tried a Profiler trace? I know you stated you think the operation fails during the generation of the deploy script, not while executing the generated script. How did you come to that conclusion, exactly?

    Thanks!

    Cathy Miller

    Microsoft Online Community Support

    Wednesday, October 05, 2011 6:48 PM
  • Hi Cathy, thanks for responding!

    I can tell that the error occurs during the generation of the script a couple of ways:

    1 - The deployment script to be run against the database never gets created on the file system.

    2 - The msbuild log shows the build failing before the deployment script is generated.

    Here is the msbuild log when this failure occurs:

    Build started 10/4/2011 6:58:35 PM.
    Project "C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj" on node 1 (Deploy target(s)).
    Database.dbschema : Deploy error SQL01272: Procedure could not be imported but one or more of these objects exist in your source. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Database.dbschema : Deploy error TSD01234: Errors occurred while modeling the target database.  Deployment can not continue. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Done Building Project "C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj" (Deploy target(s)) -- FAILED.
    
    Build FAILED.
    
    "C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj" (Deploy target) (1) ->
    (DspDeploy target) -> 
      Database.dbschema : Deploy error SQL01272: Procedure could not be imported but one or more of these objects exist in your source. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Database.dbschema : Deploy error TSD01234: Errors occurred while modeling the target database.  Deployment can not continue. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    
        0 Warning(s)
        2 Error(s)
    
    Time Elapsed 00:04:27.46
    
    


    and here is what the msbuild log looks like when everything runs successfully:

    Build started 10/4/2011 7:59:20 PM.
    Project "C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj" on node 1 (Deploy target(s)).
    Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[GetOrderEntryProductsForRefund2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[iQmetrix_AR_GetSalesReportByInvoice2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[iQmetrix_AR_GetSalesReportByInvoiceTemp]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[iQmetrix_AR_GetSalesReportByInvoiceTest]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[GetOrderEntryProductsForRefund] might introduce run-time errors in [dbo].[iQclerk_SI_GetRefundSummaryReport2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[GetOrderEntryProductsForRefund] might introduce run-time errors in [dbo].[iQmetrix_DR_GetSaleInvoice2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Deployment script generated to:
      C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Binaries\Database\AutomationPP430DeployScript.sql
      
      Dropping DF__iQclerk_O__UnitC__1CE03A5A...
      Dropping DF__iQclerk_O__Store__73891F0C...
      Dropping DF__iQclerk_O__SOCCo__7B95207F...
      Dropping DF__iQclerk_O__Seria__039F8B44...
      Dropping DF__iQclerk_O__Contr__7AA0FC46...
      Dropping DF__iQclerk_O__Store__747D4345...
      Dropping DF__iQclerk_O__SOCCo__7C8944B8...
      Dropping DF__iQclerk_O__Seria__01B742D2...
      Dropping DF__iQclerk_O__Store__6E254B71...
      Dropping FK1_OEPATA_ProductWasOrdered...
      Dropping FK1_OEAPA_OrderEntryExists...
      Dropping FK3_OEAPA_StoreExists...
      Dropping FK2_OEAPA_ProductExists...
      Starting rebuilding table [dbo].[iQclerk_OrderEntriesAndProductsArchive]...
      : 
      (6 row(s) affected)
      
      Caution: Changing any part of an object name could break scripts and stored procedures.
      Caution: Changing any part of an object name could break scripts and stored procedures.
      Creating UN1_OEAPA_UniqueOrderEntryIDAndPriority...
      Creating UNI_OEAPA_UniqueOrderEntryIDAndGlobalProductIDAndPriority...
      Creating FK1_OEPATA_ProductWasOrdered...
      Creating FK1_OEAPA_OrderEntryExists...
      Creating FK3_OEAPA_StoreExists...
      Creating FK2_OEAPA_ProductExists...
      Creating CH1_OEAPA_QuantityValid...
      Creating CH2_OEAPA_UnitPriceValid...
      Creating CH3_OEAPA_PriorityValid...
      Creating CH4_OEAPA_ListPriceValid...
      Creating CH5_OEAPA_SerialNumberValid...
      Altering [dbo].[iQclerk_IA_InsertQuantityAdjustmentProductDecrease]...
      Altering [dbo].[iQclerk_PO_CorrectSerialNumberThatShouldNotHaveBeenReceived]...
      Altering [dbo].[iQclerk_PO_CorrectSerialNumberThatWasReceivedWithWrongSerialNumber]...
      Altering [dbo].[iQclerk_SI_CancelSalesOrder]...
      Altering [dbo].[iQplanner_HR_GetTimeSheetApproval]...
      Creating [dbo].[iQcontact_FollowUp_GetFollowUpsToBeSent]...
      Altering [dbo].[iQclerk_OE_GetQtyRemaining]...
      Altering [dbo].[iQclerk_SI_GetOrderEntryTotal]...
      Altering [dbo].[GetOrderEntryProductsForRefund]...
      Altering [dbo].[GetOrderEntryProductsForInvoice]...
      Creating [dbo].[iQclerk_OrderEntriesAndProductsArchive].[Priority].[MS_Description]...
      Creating [dbo].[iQclerk_OrderEntriesAndProductsArchive].[UnitPrice].[MS_Description]...
      Creating [dbo].[iQclerk_OrderEntriesAndProductsArchive].[ListPrice].[MS_Description]...
      Creating [dbo].[iQclerk_OrderEntriesAndProductsArchive].[StoreID].[MS_Description]...
      Creating [dbo].[iQclerk_OrderEntriesAndProductsArchive].[StoreInStoreInvoicePrice].[MS_Description]...
      Creating [dbo].[iQclerk_OrderEntriesAndProductsArchive].[MagSwipeData].[MS_Description]...
      : 
      (6 row(s) affected)
      
      : 
      (1 row(s) affected)
      
      : 
      (1 row(s) affected)
      
    Done Building Project "C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj" (Deploy target(s)).
    
    Build succeeded.
    
    "C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj" (Deploy target) (1) ->
    (DspDeploy target) -> 
      Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[GetOrderEntryProductsForRefund2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[iQmetrix_AR_GetSalesReportByInvoice2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[iQmetrix_AR_GetSalesReportByInvoiceTemp]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[iQclerk_OrderEntriesAndProductsArchive] might introduce run-time errors in [dbo].[iQmetrix_AR_GetSalesReportByInvoiceTest]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[GetOrderEntryProductsForRefund] might introduce run-time errors in [dbo].[iQclerk_SI_GetRefundSummaryReport2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
      Database.dbschema : Deploy warning TSD00560: If this deployment is executed, changes to [dbo].[GetOrderEntryProductsForRefund] might introduce run-time errors in [dbo].[iQmetrix_DR_GetSaleInvoice2]. [C:\Builds\28\RQ4TeamProject\Release.RQ4.Updater.4.3\Sources\Database\Database.dbproj]
    
        6 Warning(s)
        0 Error(s)
    
    Time Elapsed 00:06:10.84
    
    


    I have not tried using a Profiler Trace (I just looked up what that is now).  I believe the profiler trace would only be useful if the error occurred while actually deploying against a database, correct?  I don't think it will give me any information about this error, since it happens while generating the deployment script.

    I would really appreciate any ideas/thoughts/suggestions that you have, as this is a major problem for us.  Thanks Cathy.


    - Dan - "Can't never could do anything"
    Thursday, October 06, 2011 2:36 PM
  • I'll note too that I found this separate, but related post as well which doesn't seem to have a solution.

    Somebody mentioned that they were getting this error when they had open transactions on the database that they were deploying to, and rolling back the transaction fixed the problem.  I have confirmed though that we do not have any open transactions on our databases during deployment.

    Somebody else mentions that they had a complex stored procedure that was valid in SQL, but that Visual Studio Data Dude didn't like, so they adjusted the stored procedure and the problem went away.  Our solution is huge with thousands of stored procedures, and the error message doesn't give any clue as to what stored procedure is causing the problem.  Also, I don't believe this is our problem, because if it was, why would the deploy work sometimes and not others.

    Again, any help is welcome and appreciated.  Thanks.


    - Dan - "Can't never could do anything"
    Friday, October 07, 2011 6:26 PM
  • We get this error sometimes as well, especially when your SQL server is acting up somehow and it is slow. We do have a huge database project with thousand of stored procedures as well.

    The error happens when the deployment process is trying to come up with a deployment plan. What's happening is that the deployment engine is trying to build the target schema from your target database to compare it with the source. It runs a single query to get all the stored procedure code from your target and if you have thousands that query may take a while. My guess is this query times out. So using the profiler on your target database would be useful to see if this is true. On the other hand, if it is true, I don't know what you can do to speed up that query.

    Friday, October 14, 2011 7:38 PM
  • Hmmm, this would explain why the problem happens much more frequently when deploying to multiple databases in parallel, since they would all be querying the database server at the same time, increasing the likelyhood of a timeout.  If this is the case, is there some way to increase the timeout length so larger database projects can avoid this problem?
    - Dan - "Can't never could do anything"
    Friday, October 14, 2011 7:51 PM
  • That query should not timeout; thousands of stored procedures is a lot, but should be nothing in terms of records in a table to query. If you run profiler you could catch that query and try to run it manually to see what's happening. Probably there is something else that makes the query run slow (if that is actually the problem) and you can find a solution once  you know the culprit.
    Friday, October 14, 2011 8:51 PM
  • Friday, October 14, 2011 9:37 PM
  • The registry settings that they specify to change (HKEY_CURRENT_USER\Software\Microsoft\VisualStudio\10.0\VSTSDB\Database) don't exist on our SQL Server box or on our TFS Build server, so I guess that's not an option.  Also, because we are deploying to the databases by using MSBuild on our TFS Build server, changing the Visual Studio option that they mention will not help either :(

    We still haven't tried a Profiler Trace yet though, so that'll be my next step.


    - Dan - "Can't never could do anything"
    Tuesday, October 25, 2011 7:24 PM
  • Hi Dan,

    Have you already turned on logging for VSDB?  You would need to change TraceToLogFile"=dword:00000001.  Following blog provides detailed description: 

    http://blogs.msdn.com/b/gertd/archive/2008/08/17/diagnosing-problems.aspx

     

    John

     

    Thursday, October 27, 2011 11:34 PM
  • Hi Dan,

    Have you already turned on logging for VSDB?  You would need to change TraceToLogFile"=dword:00000001.  Following blog provides detailed description: 

    http://blogs.msdn.com/b/gertd/archive/2008/08/17/diagnosing-problems.aspx

     

    John

     

    Thursday, October 27, 2011 11:35 PM
  • Hi Dan,

    Have you already turned on logging for VSDB?  You would need to change TraceToLogFile"=dword:00000001.  Following blog provides detailed description: 

    http://blogs.msdn.com/b/gertd/archive/2008/08/17/diagnosing-problems.aspx

     

    John

     

    Thursday, October 27, 2011 11:35 PM