SQL Server Data Tools

Sometimes you have objects, which you placed inside master, in order to share them between databases. This raises the question how to get a database project to resolve references to these objects? Additionally you may need to rely on the global name resolution capabilities of master, so that you do not have to prefix the object with a database name. This only applies for user defined procedures, but there are causes where you need this. Lets see how we can make this work.

For this little experiment we assume you have a database project (Database1.sqlproj), which contains a procedure that references objects in master; one which is declared inside system master.dacpac and one that we added as a shared procedure, named “dbo.sp_version”. Our contrived test object inside our database project looks like this:

create proc dbo.mytestproc
as
exec dbo.sp_who
exec dbo.sp_version

In order to resolve sp_who you need to add a database reference to master, using the “Add Database Reference” dialog like this:

This resolves the sp_who reference, but since sp_version is not defined in the system level master.dacpac you will see a warning like this:

Your first inclination should be to use a database reference, which allows you to augment a namespace by adding additional objects.

To setup this up you need to perform the following steps::

1. Add a second database project to your solution, lets name it MasterExt.sqlproj.
2. Add the procedure with the name dbo.sp_version to the MasterExt project

   create proc dbo.sp_version
   as
   select	1 as major,
   		1 as minor,
   		1 as build,
   		1 as patch
   

3. Next we need to add a reference from the Database1 project to the MasterExt project or to the MasterExt.dacpac, which ever you prefer. If these are truly objects which you are sharing amongst many databases, I personally prefer the dacpac file reference approach, as it allows to centrally control the shared objects inside a project project and while I do not have to share the source representation. Besides that file based database references are slightly faster and more efficient as the do not have to be compiled when you rebuild the solution. Adding the database reference to your project looks like this:
   
   Make sure you choose: “Different database, same server” as the database location and set the database name to master. Also make sure to remove the database variable name, this needs to be blank, in order to trigger a literal database name reference (I know very obvious).
4. Rebuild your solution
5. You will still see the warning
   

   Warning    1    SQL71502: Procedure: [dbo].[mytestproc] has an unresolved reference to object [dbo].[sp_version].

6. The reason you still get the warning is due to global name resolution, more about this in a bit. Lets first check if the reference works, by changing the call in to an explicit database referenced call by changing
   exec dbo.sp_version into exec master.dbo.sp_version

   create proc dbo.mytestproc
   as
   exec dbo.sp_who
   exec master.dbo.sp_version

7. Rebuild the solution
8. Now the warning is gone!

Why is that? As suggested earlier, global name resolution comes in to play. In order to get to the answer we have to crack open the master.dacpac file. A dacpac file is a so called Open Packaging Convention or OPC file for short, see http://en.wikipedia.org/wiki/Open_Packaging_Conventions for details. Bottom-line, OPC files are ZIP files with some specific structure and content. In order to digest a *.dacpac file, we can rename the file extension from *.dacpac to *.zip and we extract its content.

Lets crack open the system provided master.dacpac file which lives in:

First we will create a directory we can work in and make a copy of the system provided master.dacpac file by performing the following steps:

Next expand the master.zip file using your favorite ZIP utility, if you do not have one you can use the build in ZIP extraction functionality in Windows Explorer. I am using WinRAR to expand in to the c:\dac\master directory.

Now lets check the content of the directory:

The result looks like this:

The [Content_Types].xml file is a standard OPC file which defines the content types used inside the instance of the OPC file, in this case the only content type used will be XML.

<?xml version="1.0" encoding="utf-8"?>
<Types xmlns="http://schemas.openxmlformats.org/package/2006/content-types">   <Default Extension="xml" ContentType="text/xml" />
</Types>

The model.xml file contains the actual model payload which describes the objects inside a database and the origin.xml describes where the model information is coming from in terms of version and dates. It also contains a checksum of the model.xml, to make sure the model.xml has not been tempered with.

<?xml version="1.0" encoding="utf-8"?>
<DacOrigin xmlns="http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02">   <PackageProperties>     <Version>3.0.0.0</Version>     <ContainsExportedData>false</ContainsExportedData>   </PackageProperties>   <Operation>     <Identity>0cb584bd-a5db-4740-8cba-3bf24dbf9aff</Identity>     <Start>2012-09-13T15:17:48.3677799-07:00</Start>     <End>2012-09-13T15:17:48.5408231-07:00</End>     <ProductName />     <ProductVersion>0.0</ProductVersion>     <ProductSchema>http://schemas.microsoft.com/sqlserver/dac/Serialization/2012/02</ProductSchema>   </Operation>   <Checksums>     <Checksum Uri="/model.xml">EED727619D660CBB007A092429C44C627CCEFE26738B47E453F8A93CF35906F0</Checksum>   </Checksums>
</DacOrigin>

Now that we have exposed the model, lets open it up in our favorite XML editor, I will be using Notepad++. Once we have opened up the model.xml, lets search for sp_who, so we can compare this with sp_version which resides in the MasterExt.dacpac file.

Model definition of sp_who

<Element Type="SqlProcedure" Name="[sys].[sp_who]">       <Annotation Type="AliasedToDbo" />       <Annotation Type="GloballyScoped" />       <Relationship Name="Parameters">         <Entry>           <Element Type="SqlSubroutineParameter" Name="[sys].[sp_who].[@loginame]">             <Relationship Name="Type">               <Entry>                 <Element Type="SqlTypeSpecifier">                   <Relationship Name="Type">                     <Entry>                       <References ExternalSource="BuiltIns" Name="[sys].[sysname]" />                     </Entry>                   </Relationship>                 </Element>               </Entry>             </Relationship>           </Element>         </Entry>       </Relationship>       <Relationship Name="Schema">         <Entry>           <References ExternalSource="BuiltIns" Name="[sys]" />         </Entry>       </Relationship>     </Element>

If we repeat the same steps used to extract the information from master.dacpac for our own MasterExt.dacpac, we can retrieve the mode definition for sp_version, so we can compare them.

<Element Type="SqlProcedure" Name="[dbo].[sp_version]">
	<Property Name="BodyScript">
		<Value><![CDATA[
select	1 as major,
		1 as minor,
		1 as build,
		1 as patch]]></Value>
	</Property>
	<Property Name="IsAnsiNullsOn" Value="True" />
	<Relationship Name="Schema">
		<Entry>
			<References ExternalSource="BuiltIns" Name="[dbo]" />
		</Entry>
	</Relationship>
	<Annotation Type="SysCommentsObjectAnnotation">
		<Property Name="Length" Value="94" />
		<Property Name="StartLine" Value="1" />
		<Property Name="StartColumn" Value="1" />
		<Property Name="HeaderContents" Value="create proc dbo.sp_version&#xD;&#xA;as" />
	</Annotation>
</Element>

If you compare the two model elements, two things are very different. The master.dacpac fragment does not contain BodyScript element. Since the master.dacpac reflects the shape of objects always deployment by SQL Server, you only need to be able to bind to the shapes and you do not need it to deploy them, therefore the actual body scripts are omitted.

The second part which is different is that there are two annotations in the master.dacpac <Annotation Type=”AliasedToDbo” /> and <Annotation Type=”GloballyScoped” /> which do not show up in the MasterExt.dacpac or in any other dacpac for that matter. These two annotations reflect the special aspects of the master database regarding name resolution. GloballyScoped refers to the fact that an object can be access without having to specify a database when you are not in the particular database context where the object itself resides. AliasesToDbo refers to the fact that certain objects show up in the sys and dbo namespace, but it you look in the internal system catalog, you will only find the objects in the sys schema.

Since the way we call sp_version relies on the global namespace, in order to make this work, we would have to annotate the sp_version element with the GloballyScoped annotation. There is no way to do this inside the project, so we have to edit the model.xml of the MasterExt.dacpac.

The update model element for sp_version now looks like this:

<Element Type="SqlProcedure" Name="[dbo].[sp_version]">
	<Annotation Type="GloballyScoped" />
	<Property Name="BodyScript">
		<Value><![CDATA[
select	1 as major,
		1 as minor,
		1 as build,
		1 as patch]]></Value>
	</Property>
	<Property Name="IsAnsiNullsOn" Value="True" />
	<Relationship Name="Schema">
		<Entry>
			<References ExternalSource="BuiltIns" Name="[dbo]" />
		</Entry>
	</Relationship>
	<Annotation Type="SysCommentsObjectAnnotation">
		<Property Name="Length" Value="94" />
		<Property Name="StartLine" Value="1" />
		<Property Name="StartColumn" Value="1" />
		<Property Name="HeaderContents" Value="create proc dbo.sp_version&#xD;&#xA;as" />
	</Annotation>
</Element>

Now that we updated the model.xml, lets put it back together inside a .dacpac, by zipping up the content and renaming the file extension from .zip to .dacpac.

After we done this we can rebuild the project, this assumes the database reference to MasterExt.dacpac is still present and points to the right file location!

Once we rebuild we will get the another error “The reference to <filename.dacpac> is not not support”.

The dacpac file is “not supported” because the checksum inside the orgin.xml no longer corresponds with the actual checksum of the model.xml payload. In order to address this we have to calculate the checksum of the model.xml. Turns out the checksum is a simple SHA256 based hash.  I created a small utility, named dacchksum.exe to help you update the signature. The usage is very simple:

This tells you the persisted checksum and the calculated one based on the model.xml payload are different. Adding the /Update or/u flag will update the persisted checksum with the updated value.

I hope this is useful, enjoy,
@DataDude

Follow @DataDude