SQL72043 and other SSDT errors: how can I find the incorrect code?

Question

So, I've got a project in SSDT (SQL Server Data Tools) in Visual Studio 2012. When I build this project, there are no errors - it builds fine (not even a warning or a message).

However, if I try and publish I get:

The script file could not be found (no more information).

If I schema compare the project with an existing database, all I get is:

Cannot generate deployment plan (again, no more information).

Only when I do 'Run Code Analysis' do I get an error that could possibly mean anything:

SQL72043: 7 elements are in an error state

I know this error can be caused by a missing comma in a table definition or similar trivial errors, but I've been tearing my hair out for hours looking through every line of SQL code in the project to find a problem. Is there any way I can shed more light on what lines of my code are causing the error?

Calabria

There's a couple of suggestions for that error on MSDN here: social.msdn.microsoft.com/Forums/en/ssdt/thread/… , restarting visual studio being the one that worked for the original poster. – Preliminary

As you can probably see from my name, I'm the last poster on that thread - so yes, I've read it and tried everything described. I mean there were only 3 results on Google for that error code (4 now with this question), so I would be pretty silly not to have looked at them all! Having said that, one poster on that thread mentioned 'I found it very useful to turn off the syntax checking' - I can't find an option to do that anywhere in Visual Studio. Any hints on that? – Calabria

sorry I missed that, only read the accepted and proposed answers. What about the option in project settings for extended TSQL verification? Or if you have source control can you do a compare to a last known good build? – Preliminary

What I ended up doing was as follows: 1. Exclude all the .sql files from the project. 2. Add in .sql files one by one. 3. Publish to script to check everything works, fixing problems if it doesn't. 4. Repeat steps 2 & 3 until thoroughly bored, all .sql files are back in the project and everything works... Note: I tried previous approaches with extended T-SQL verification both on and off, but I usually leave it on, assuming that it might one day pick something up. P.S. Sorry about the sarcastic response before - I was getting pretty frustrated with the situation! – Calabria

Try deleting the dbmdl file, worked for me! – Vento

If you found a solution to your problem, please post it as an answer rather than as a comment on your question. – Aphrodisiac

Answer 1

Delete the dbmdl file in your database project.

Atalanti

Answer 2

You can just go to IDE menu 'Project' -> 'Reanalyze Project' and then rebuild your database project to get rid of this error on Publish command.

Spathose

I was getting an error "An unexpected exception has occurred in the model storage engine. To continue, disregard the current results and try the operation again. If this issue persists, contact Microsoft support." when building my SSDT project and this solution worked for me. – Gunny

In VS2015 there are no .dbml files generated, bu the "Reanalyze Project" menu option does the trick. – Pelkey

Answer 3

Deleting the dbml files didn't work for me, but to get the schema compare to work and not recieve the Cannot generate deployment plan error, I switched the source and target and compared the schemas again.

Propaganda