Options

Build failing with "Invalid Object Name" error, but the objects exist

mugojoemugojoe Posts: 3 Bronze 1
in SQL Source Control
I recently linked an existing database to source control. I was able to commit the changes locally and push them to the origin remote. A build was triggered and failed saying objects don't exist, even though they do exist in the database. I did not make any changes to the database, I'm just setting everything up now and pushing the existing database to TFS/Git. It feels like an order of operation issue because the errors are being thrown when trying to create a SPROC, TVF or View, saying tables they are referencing don't exist. They do exist in the database though.
· Share on Twitter

Answers

  • Options
    saulcruzsaulcruz Posts: 19 Bronze 1
    same error here!
    · Share on Twitter
  • Options
    mugojoemugojoe Posts: 3 Bronze 1
    The answer ended up being pretty simple. In my case, there were references in some functions and stored procedures with the database name listed before the schema. For example, Database1.schema.tablename vs. schema.tablename. Depending on how your build is set up, it either spins up a copy of the DB with a temporary name (this is how mine is set up), or goes against a named instance of the database specified in the build. I wanted to continue down the path using the temp DB that was spun up so I removed the hard coded database name from everything that referenced it. This resolved the issue and the build succeeded.
    · Share on Twitter
  • Options
    saulcruzsaulcruz Posts: 19 Bronze 1
    edited January 31, 2018 8:28PM
    interesting, very good point. In my case I'm doing exactly the same, hard-coding the database name, you just saved me a lot of time =)

    I'm also doing the temp db approach, however, what if there are multiple databases and there are dependencies between them, for example I have STORED PROCEDURES that reference tables from another database (staging) ? is there any way we can turn off this check/validation? I guess my question is more related to the use of Invoke-DlmDatabaseSchemaValidation and having cross database joins in my objects
    · Share on Twitter
  • Options
    mugojoemugojoe Posts: 3 Bronze 1
    Unfortunately, I haven't tried what you are talking about and am pretty new at using this tool so I'm unable to provide an answer to your second question. I'm really not sure how this would work because even with a named instance specified in the build, I don't think it would resolve what you are facing. Hopefully someone with more experience can chime in. I will likely face this same issue going forward.
    · Share on Twitter
  • Options
    saulcruzsaulcruz Posts: 19 Bronze 1
    Alright, thanks a lot though
    · Share on Twitter
Sign In or Register to comment.