Just Whatever

For the past few days a co-worker Geoff and I have been working on setting up database object creation scripts in a SQL Express 2008 environment, and we ran into two issues where the script generation tools built into SQL Server Management Studio have some unexpected behaviors that were tripping up the process. In one case there was a silent failure to perform a particular scripting task and in the other there was a loud failure that seemingly was unrelated to the configuration of the scripts we were attempting to generate. In both cases we were able to solve the issues and move forward, but not without some head scratching and trial-and-error problem solving effort.

Scenario 1: The script wizard indicates it successfully scripted all objects in the database but actually does not script one or more of the table objects. The constraints, etc. are scripted, and the wizard reports that it successfully scripted the object in question, but when you examine the T-SQL output, the CREATE of the table is not there. And of course when you run the script it fails at the point where it attempts to apply constraints to tables that do not exist.

The problem is that the object name and references to it throughout the various scripts and relationships were not always in the same case. Even if your SQL Server instance is not configured to be case sensitive, the scripting engine apparently is configured that way, at least when it is trying to figure out dependent objects of associative tables. The solution that Geoff came up with was to find and replace all instances of the table name so that they were in the same case, run the script at that point to get the objects updated, then re-run the whole thing from the start to capture all the changes correctly in the final script that gets checked into source control. Hats off to Geoff and his hunch on this one; he was right on the money.

Scenario 2: The script generator throws a "full-text index" scripting not supported error, even though you have set that setting to *not* script full-text indexes. This arises when you choose which database objects to script by checking their boxes in a list of all the items in the database. Interestingly, the error is not thrown when you indicate at the beginning of the scripting process that you want the tool to generate scripts for all the objects in the database and you don't choose them individually. It also does not occur if you go the route of scripting the table directly from the object browser, which made it all the more elusive to track down.

My hunch is that either there are two different scripting processes at work in this scenario, or there is some kind of additional licensing-related check that happens when you take a certain path that is not present in the other methods. The generator acted as if a setting of false on the full-text index option meant that we wanted it scripted and did not consider that it should just be skipped altogether (that's just a hunch, I didn't confirm it).

What all this tells me is nothing I didn't already know: working with SQL Server means delving into a sometimes arcane, often darkly lit environment where it seems like the only way to get through successfully is to keep experimenting and following hunches. Even when you have what you think is a pretty clear error message it often turns out to be the case that other peoples' solutions are not for the same problem you are having (even though the message is the same). Once you have a few of these experiences, though, they come in handy down the road and you won't forget them the next time you run across the same situation.