Resolve reference warning to object of master database in database projects

2009, Jul 08 3 mins read

If you use some stored procedure or objects that reside into master database, when you compile your database project you will end with warning like this.

1
C:\DEVELOP\myproject\TRUNK\SRC\COMMON\SQLSERVER\REPMANAGEMENT.DATABASE\SCHEMA OBJECTS\PROGRAMMABILITY\STORED PROCEDURES\LOG.GETFEEDER.PROC.SQL(45,6)Warning TSD04151: Procedure: [Log].[GetFeeder] contains an unresolved reference to an object. Either the object does not exist or the reference is ambiguous because it could refer to any of the following objects: [dbo].[sp_executesql] or [Log].[sp_executesql].

This happens because I used the sp_executesql in my stored procedures, and sp_executesql is one of the stored that resides into Master database. To avoid these warnings you must add a reference to all objects that are in master schema.

This kind of reference is not a simple one, but is a Database reference. You can find a reference of the master db in directory %program files%/Microsoft Visual STudio 9.0/VSTSDB/Extensions/SqlServer/SqlVersion/DBSchemas

The dbschema of master database is really big, almost 8 megabytes, and it can slow down your project compilation, because visual studio needs to analyze it to retrieve definition of all objects of master db. Now, since the original master.dbschema contains definition of thousands of db objects, you can create a shrinked version of it that contains only the definition of those object you really reference into your project.

Reducing the file is really simple, you must create a copy of the original file, then you can delete all elements contained in this newly copied file, the result is a content like this one.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
<?xml version="1.0" encoding="utf-8"?>
<DataSchemaModel FileFormatVersion="1.0" SchemaVersion="1.0" DspName="Microsoft.Data.Schema.Sql.SqlDsp.Sql100DatabaseSchemaProvider" CollationLcid="1033" CollationCaseSensitive="False">
    <Header>
        <CustomData Category="ModelCapability">
            <Metadata Name="ModelCapability" Value="Default" />
        </CustomData>
        <CustomData Category="DBSchema">
            <Metadata Name="DatabaseType" Value="master" />
            <Metadata Name="SqlServerVersion" Value="10.00" />
            <Metadata Name="Author" Value="Microsoft Corp." />
        </CustomData>
        <CustomData Category="DatabaseVariableLiteralValue">
            <Metadata Name="Name" Value="master" />
        </CustomData>
    </Header>
    <Model>
    </Model>
</DataSchemaModel>

This is the minimum skeleton of a dbschema file, with all elements node removed, now you begin adding all the elements you need from the original file. As an example since I used the sp_executeSql I goes into the master.dbschema, look for its definition and directly copied into the new reduced file.

 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
<Model>
        <Element Type="ISqlExtendedProcedure" Name="[sys].[sp_executesql]">
            <Property Name="IsQuotedIdentifierOn" Value="False" />
            <Relationship Name="Owner">
                <Entry>
                    <References ExternalSource="BuiltIns" Name="[sys]" />
                    <Annotation Type="SqlModelBuilderResolvableAnnotation" Name="[sys]">
                        <Property Name="TargetTypeStorage" Value="ISqlObjectOwner" />
                    </Annotation>
                </Entry>
            </Relationship>
            <Annotation Type="AliasedToDbo" />
            <Annotation Type="GloballyScoped" />
        </Element>

In the end I have a dbschema file with 6 elements, (all those I need) and I can reference it without the risk of slowing down the database project.

Alk.

Tags: Visual Studio Database Edition