2

I've created a new SQL 2008 database project in Visual Studio 2010 and filled it with the contents of a local SQL Express database. When I try to build the database project I get this error: SQL03006: Column: [dbo].[table1].[geog] has an unresolved reference to Sql Type [dbo].[geography]

I've done some searching and it may be missing a reference to Microsoft.SqlTypes.dbschema, but I can't find that anywhere: http://www.incyclesoftware.com/blog/post/2009/07/07/Resolve-references-Error-TSD03006-IN-VSTS-DB-GDR.aspx

Is the datatype really not supported out of the box or am I missing something?

Mathieu Diepman
  • 151
  • 1
  • 11

3 Answers3

3

Found it; in the table or stored procedure the datatype needs to be prefixed with [sys] like this: 

CREATE TABLE [dbo].[Location] (
    [objectId]  BIGINT            NOT NULL,
    [latitude]  FLOAT             NOT NULL,
    [longitude] FLOAT             NOT NULL,
    [geog]      [sys].[geography] NULL,
    [geom]      [sys].[geometry]  NULL
);
Mathieu Diepman
  • 151
  • 1
  • 11
  • Thanks! Worked for me in Visual Studio 2012 - Table Design. My geom column showed as sys.geometry under CLR Types. – Riaan Apr 15 '13 at 09:57
2

I have had the same issue after introducing Sql Type geometry to a DB Project (DB project version in my case was set to "2005" while I was comparing to a SQL 2008 schema)

I resolved the issue by changing the Database Project Version (Properties -> Project Settings -> Project Version) to 2008.

After that when you right-click on References (for the Database Project) or the project itself, you should see an option "Add SQL Server 2008 CLR Types". This will add the reference that you need and will resolve the issue.

Here is what adding the reference generated in the proj XML for me: 

<ItemGroup>
    <ArtifactReference Include="$(VSTSDBDirectory)\Extensions\SqlServer\2008\DBSchemas\Microsoft.SqlTypes.dbschema">
         <HintPath>..\..\..\..\..\Program Files (x86)\Microsoft Visual Studio 9.0\VSTSDB\Extensions\SqlServer\2008\DBSchemas\Microsoft.SqlTypes.dbschema</HintPath>
   </ArtifactReference>
</ItemGroup>
pgk
  • 2,025
  • 1
  • 14
  • 15
1

I never got the "Add SQL Server 2008 CLR Types" option, but I was able to fix this problem by editing my .dbproj file and changing the LoadSqlClrTypes value to True:

<LoadSqlClrTypes>True</LoadSqlClrTypes>
Jason Barile
  • 936
  • 1
  • 6
  • 17