Can't add database reference to VS2012 project

Question

I have a SQL Server CLR trigger project that was created in VS2008. Opening the project in VS2012 does not present any problem, but on build of the solution, I get SQL71501 errors, with Trigger: [...] has an unresolved reference to object [...].

Based on my reading, this is due to a missing database reference in the project. When I try to add a database reference, I get the Add Database Reference dialog that gives me three options:

• Database projects in the current solution ** this option is grayed out/disabled
• System database (only shows system DBs)
• Data-tier Application (.dacpac) ** there are no options to select as this was not how I created the project.

Further reading suggested that the reason there are no database projects to select for the first option, is because no Data Connections have been added via the Server Explorer. In my case there are certainly Data Connections present, and while my project is open, I can quite happily browse the database, look at data etc.

I thought it might have something to do with the Target Framework, so I have tried targeting 3.5 and even 2, but the same problem occurs.

I feel like I'm missing something fundamental, but just can't quite work it out. Any help would be GREATLY appreciated.

Effectually

Answer 1

I've seen this dialog not enable the 'OK' button because the Database Variable that it defaults is invalid (in my case it had a '.' in it).

The clue to this being the error is that the text in the 'Example Usage' field contains the error message - it's just hard to see as it's dark grey on light grey.

Editing the Database Variable name fixes this.

Rubiaceous

Removing the dots worked for me as well. With that variable you can create cross-database references: [$(ServerVariable)].[$(DatabaseVariable)].[Schema].[ObjectDefinition] – Odometer

Answer 2

I found a workarroud for this, isn't optimal but at least it works:

try this - open sql server object explorer - create a new connection to your server - right click on the database and select create new the project... - the wizard will create the project with all the references and connection string attached to it.

cheers!

Achondroplasia

Answer 3

I changed my answer

The problem is you cannot assume an insert is a single row and you can really only reference the primary key as a single value inside the trigger

Precarious