Invalid object with using Invoke-DatabaseBuild
JohnSterrett
Posts: 11 Bronze 2
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?
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?
Tagged:
Answers
Are you using a SQL Source Control or SQL Change Automation project?
Product Support Engineer
Redgate Software Ltd
Please see our Help Center for detailed guides on how to use our tools
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
Product Manager
Redgate Software
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
Product Support Engineer
Redgate Software Ltd
Please see our Help Center for detailed guides on how to use our tools
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.