DLM build fails for views but not store proc?

10raw

DLM build fails while building a view which script contains database reference while calling an object but sproc does not fails even though they have similar database reference call. For example

FROM databasename.schemaname.table in a view fails but

FROM databasename.schemaname.table in sproc does not fail.

If I remove databasename from view script to change it to schemaname.table in view then it builds too.

AlexYates

Don't use 3 part naming? Seems to be unnecessary assuming the table is on the same database. 3 part naming just makes your life harder.

You can work around it if you must. Use the -TargetDatabase switch.
https://documentation.red-gate.com/sca3/reference/powershell-cmdlets/invoke-databasebuild

Reason for error is SQL Change Automation will create a scratch DB with a guid for a name to test your code. Your 3 part naming obviously cries. If you use -TemporaryDatabaseName it will ensure SQL Change Automation uses your specified DB for the build.

This is a pain though as you can now only run one build at a time.

AlexYates

Sprocs are only compiled at run time so dependency issues aren't uncovered simply by building/deploying the DB.

10raw

Thank you Alex for the response, it clears up the confusion little bit. So does that mean views dependency issues are all covered by building only and if so how can I build views whose scripts has database name reference. They fails in build. Is there any tweak I can do anywhere so that DLM does not see them as an invalid objects.

For example if I have a view script

CREATE VIEW sample1 AS SELECT * FROM 
databasename.schemaname.tablename;

Then I am getting build error:
WARNING: The error 'Invalid object name databasename.schemaname.tablename;' occurred when executing
the following SQL:

and if I remove the databasename from the above script then it works.

Please suggest me some idea as we have more than 500 views script with similar problem.

Thank you for your response again.

10raw

Thank you for the clarifications.  Last but not least, for the main question , Does views get compiled at build/deploy?

AlexYates

Yes - if a view tries to read from a table that doesn't exist the build should fail.

AlexYates

Ah - now the order of this thread is messed up and the accepted answer is the answer to the wrong question! (-: