What are the challenges you face when working across database platforms? Take the survey
Options

Invalid object with using Invoke-DatabaseBuild

JohnSterrettJohnSterrett Posts: 11 Bronze 1
edited February 12, 2020 1:10AM in SQL Change Automation
I am confused about the following problem. Hopefully, someone can help me. I am having a problem doing a build of an existing schema.  Let's say there is a view called "dbo.View1" and is sudo code is below.

CREATE dbo.View1
AS
Select * from dbo.Table1 a join dbo.Table2 b on a.id = b.id
GO

I get the following error when I run Invoke-DatabaseBuild in PowerShell. The error 'Invalid object name 'dbo.table1' occurred when executing the following SQL."

I can pull these three database objects and put them in a new database and the build on a new database works fine. At the same time with the existing database I can alter or drop/create the view and it runs just fine. Somehow there is a setting or something that is making the build process not find the existing table.  This pattern is repeated several times in my build errors so it makes me go down the path that there might be some setting I am missing.

Anyone have an idea of why this might not be working or what I should look at next on why I cannot get a build with this error?

Answers

  • Options
    Hi John,

    Are you using a SQL Source Control or SQL Change Automation project?

    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    Hi Sergio,

    Its SQL Source Control and not SCA. 

    @ERROR <> 0 SET NOEXEC ON" is issued for the start of any batch statement generated by Invoke-BuildDatabase.  When an error occurs, all additional statements are compiled instead of executed. This is why I was seeing that the table didn't exist even though a create table.

    Is it possible, to have this disabled with a parameter at run-time so we could then flush out all the build errors in a build instead of just only getting the first error?  I believe this forces every other call that depends on an object to say that the object is invalid because the build just compiles instead of executing the build scripts. 

    Regards,
    John

  • Options
    @JohnSterrett - If you want to list invalid objects you can try using SQL Prompt's Find Invalid Objects feature. Alternatively, try the InvalidObjects SQL Cop test.
    David Atkinson
    Product Manager
    Redgate Software
  • Options
    Hi @JohnSterrett

    There isn't a parameter to disable this.

    However, that particular example you have posted tends to build correctly. 

    Are all the objects in the database that's being built or do you have external dependencies?
    If that's the case please see the following: https://www.red-gate.com/hub/product-learning/sql-change-automation/deploying-cross-database-dependencies
    Sergio
    Product Support Engineer
    Redgate Software Ltd
    Please see our Help Center for detailed guides on how to use our tools
  • Options
    JohnSterrettJohnSterrett Posts: 11 Bronze 1
    edited February 18, 2020 7:32PM
    @Sergio R

    I am having issues with the internal objects in the database. That said this database has over 9k user objects as well so things are a bit sluggish. Build process locally with PowerShell take over 10 minutes to complete.

    Its a database schema with lots of legacy objects left around referencing objects that have changed so I expect breaks. I just wish this tool would allow me to flush them all out at once. I think the messaging could greatly be improved for cases where a build fails. It would beat having to run an extended event to catch failures to find more detailed information about the failures during the build.

    The invalid database object message is a false positive. SET NOEXEC ON explains why I am seeing that even though I wish I could pass a flag in PowerShell to disable that. 

    @David Atkinson
    Thank you for the tip. SQL Prompt invalid objects times out. I will look at the SQL cop test. Thank you so much for providing those.

    Update: Third time was a charm for SQL Prompt Invalid Object search. I was able to get my list I needed.

Sign In or Register to comment.