Database Project Troubleshooting

Question

We have put together a database project in VS2010 / SQL 2008 R2 / Server 2008 R2. Our goal is to be able to upgrade our client's databases via our installer. We have it working for the most part, but when something goes wrong the tool isn't much help.

As for the database being modeled, it's ugly - there are around 7000 objects and each client's database is "sort of" the same, each version is going to have some differences and the clients have added some of their own stored procs for example. Basically there is 10 years of build up that we are trying to get in some sort of workable shape. If we were starting from scratch I would just use migrations of one sort or the other and be done with it but that's not the case unfortunately.

It took some effort getting the settings just right but we've got it going ok. I'm not sure if dbproj  is the answer long term but it seemed to be the best option at this stage. Auto migrations didn't seem the right way to go with everything slightly different and hand coding manual sql upgrade scripts would take forever and accuracy would be a big problem.

We've got things set up to deploy with vsdbcmd. However, as you can imagine there are going to be things that happen, such as bad data, or constraint violations not being able to be processed by the engine. That part is ok, it's what we expected, but what makes the task magnitudes harder is that the error messages are sometimes ok, sometimes ridiculous, making troubleshooting extremely difficult.

As an example, recently I used vsdbcmd.exe /import to generate a .dbschema (we do this to initially test that the target db can even be modelled). After a few tweaks, it passed that, but then the /deploy operation fails (with /dd- ie only trying to generate the delta script). The error message was something cryptic along the lines of "Table xyz failed because of a dependency on xyz.hind" followed by a bunch of numbers). I can get the exact error message at work if needed but that's not the main point. The main point is that if I do a sql compare in VS2010 then do "Write Changes" I sometimes end up with a usable error message, in this case there was a null constraint violation in a table. Why didn't the vsdbcmd.exe output tell me that? This makes it unusable for an automated deployment, which is our end goal - we can't expect support staff to have VS2010 (premium no less) to debug issues that could be resolved if the command line tool would spit out the information it obviously has in hand.

In some cases it's worse. In VS2010 I received an error that "a varchar value could not be converted to a date format due to out of range error". Great, easy to fix, except that VS2010 decided to withhold the information about which table or anything else that would help me know where to look. Let's see I only have a few thousand of them...

So, I'm looking for any suggestions. I really hope that I'm just being dense, I find it difficult to believe that "hope and pray" followed by "find the needle in the haystack" is the intended work flow.

Any help is greatly appreciated.

Answer 1

Hello,

I am not sure if your installer is hiding any output from you but you can log the output of the deployment script by putting something like this in your deployment script:

!!mkdir c:\TempSqlcmdOutput
:out c:\TempSqlCmdOutput\testoutput.txt

I have not tested it but you could slip something like this into the pre-deployment script and then you should have a complete log of the deployment script execution during installation.

I nicked this code from here: http://msdn.microsoft.com/en-us/library/aa833281.aspx

Hope that helps a bit.

Gareth.

Answer 2

Oh I forgot to mention.  Whenever I get deployment errors in VS2010 and the error is shown in the output window, if I double-click the error message it takes me to the line in the deployment script that caused the problem.  We have used that in the past numerous times to help with debugging deployment issues.

Answer 3

Hello,

I am not sure if your installer is hiding any output from you but you can log the output of the deployment script by putting something like this in your deployment script:

!!mkdir c:\TempSqlcmdOutput
:out c:\TempSqlCmdOutput\testoutput.txt

I have not tested it but you could slip something like this into the pre-deployment script and then you should have a complete log of the deployment script execution during installation.

I nicked this code from here: http://msdn.microsoft.com/en-us/library/aa833281.aspx

Hope that helps a bit.

Gareth.

Thanks, I will look into that, but one problem is that the errors I'm getting happen during the process of creating the deployment script - for example when I use the /dd- pararmeter to create the script but not make the attempt to run it.

Answer 4

Yeah, that's the way it usually is for me too, buy yesterday I ran into a situation where that didn't work, but again it was during creation of the deployment script, not during the execution of it.

Answer 5

Hello juraitwaluzka,

Based on your description, I got to know that you are working with a very big and complex database project. And as far as I know that once you have something wrong with one database object, this may lead to couples of other errors. And for your scenario, I personally suggest you to first build or deploy your database project in the VS2010 IDE. As the VS2010 IDE is more flexible for us to navigate to the line which causes the problem. Once you correct all the basic errors in VS2010 IDE, you can then choose the vsdbcmd tool to deploy or build the database project.

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us

Answer 6

I don't really care about the convenience of navigation, if I know table x has an issue I can find it easy enough. The issue for me is that vsdbcmd gives a meaningless error message whereas VS2010 tells (for the most part) what the problem was. vsdbcmd basically only says "there was a problem but as to why I'll keep that secret to myself".

So to clarify - VS2010 (and I presume MSBuild on a machine with VS2010 installed) will give meaningful error messages, vsdbcmd will not, is that correct?

Answer 7

Hi juraitwaluzka,

I am afraid that the answer is YES. Here is the MSDN article about command-line reference for vsdbcmd, however, I can't find one option is used to enable the vsdbcmd tool to give us one meaningful error message:

http://msdn.microsoft.com/en-us/library/dd193283.aspx

I think you can consider to submit one feature request on the Microsoft UserVoice site here:

http://visualstudio.uservoice.com/forums/121579-visual-studio

Thanks.

---

Vicky Song [MSFT]
MSDN Community Support | Feedback to us