Fixing Database Import Issues
After importing a database into SSDT, you need to fix the errors reported by the build. There are generally fall into three groups:
- Warning: Unresolved reference to object ...
We deal with each error type in detail below.
Although only a warning, your really need to sort out issues with unresolved references as they indicate a whole raft of bad practice that went before.
The above warning was generated by the following badly coded stored proc:
CREATE PROCEDURE [dbo].[MySP]
AS
begin
select * from OtherDatabase..MyOtherTable
endThere are four problems with this stored proc:
- Professional code should never, never use * to select all columns from a table.
- Professional code should never, never use .. to indicate the dbo schema.
- Code in one database should not refer to tables and objects in another database as this practice can soon get out of hand, creating too many dependencies between a set of databases. However, it is common for a data mart database to reference the staging database. This is accecptable and straight forward to fix so that
- Professional code should never, never use the dbo schema. Full stop. It is such a shame that early code samples delivered by Microsoft used the dbo schema as corporate America followed the patten. Consider changing the database to use schemas. This is not as hard as you may think, as it is a simple search and replace in SSDT. Schemas provide far tighter control over security and provide an easy way to divide up your code into logical groupings.
SSDT Projects utilize SQLCMD variables to provide dynamic substitution when publishing a database.
You enter the variable name and values and during build, the values will be substituted. If there are no local values, the default value will be used. By entering these variables in project properties, they will automatically be offered in publishing and are stored in publishing profiles. You can pull in the project values of the variables into publish via the Load Values button.
