SSDT, Publishing, and Referenced Databases
Had this catch me today in a series of databases that all work together. In order to resolve all of the cross-database dependencies, I’ve added database references to the project. That takes care of all of the “Database.dbo.Object” references outside of the database. The problem is that within the current database, you will get errors without that reference defined.
In my haste to get the projects working, I added a database reference to the current project. I would build my project and reference the generated *.dacpac files when publishing. The problem is that every time I built my project, the referenced dacpac file would overwrite the generated dacpac file.
Results from this problem – I noticed that my “DeployType” variable wasn’t referenced anywhere, which struck me as odd because I use it in my Pre and Post Deploy scripts. I also started noticing that my dacpac file in my build folder was out of date, even after a successful build.
Workaround – do what I have done otherwise – replace all instances of CurrentDatabaseName.dbo. with just dbo. in all SQL files in the project. This also includes the various forms that this can take:
Databasename.dbo.
Databasename..
[Databasename].[dbo].
etc
Once I replaced all of those references and rebuilt the project successfully, my publish actions started working again. My Deploy scripts were included. I didn’t get mysterious warnings about my variables not existing.
There may be a different way to work around self-referenced database objects within SSDT, but until I come across that, the workaround is to remove them from the code and not to just add a reference to the current database through a dacpac file.
About Author
paschott
I’ve been working with SQL Server for quite some time. Along the way, I’ve learned quite a few things and realized that I won’t ever know everything about SQL Server. I intended to keep growing and learning to be able to do my job well and share my experiences with others. I currently work for a Health-related non-profit based in Boise as a Database Architect.
how did you go about replacing all references to the database name without opening all the files in the project?
Tony, I usually do a Ctrl+Shift+H to open the global find/replace, limit to the current solution/project, files of type *.sql, and look for DBName.dbo. and replace with just dbo. (and variations thereof).
I know that I could set a global “ignore this warning/error number” as well but am trying to separate these in some way. There are likely better ways to do this, but this is the one I've adopted so far in lieu of time to research something better.