Resolved error “The following system error occurred: No mapping between account names and security IDs was done.”

After getting our new TFS 2012 server built out I found myself unable to grant permissions to the Tfs_Analysis database in SQL Analysis services. When attempting to add a new user of the local Active Directory domain to the TfsWarehouseDataReader role I received the above error.

I found that the role membership contained a user that was unresolvable via Active Directory and showed up as a SID only (no domain\name). Removing that unresolved user allowed me to add new groups to the role from the local AD.

It would appear from this that the logic is revalidating all of the users and groups that are listed in the membership role list when adding/removing members.

Importing DBF Files Into SQL Server 2008

I’m currently working on a new solution to allow geocoding and data augmentation with demographic data. The demographic data set that we are working with is provided to us as several gigabytes of data separated into logical groupings of .DBF files each with 200+ columns of data and an accompanying data dictionary document in Word format.

I needed to upload all of this data into a new SQL Server 2008 R2 database so that we can do some aggregation and other processing against the data. Unfortunately, SQL Server 2008 R2 does not support a direct import of the data found in the .DBF files. One option is to convert the .DBF files into Excel files. Excel handles the conversion easily. Unfortunately, in my case, the width of the tables is simply too wide for the Microsoft Office Data Access Engine to handle properly. When I attempt to import the resulting Excel files into the database I am greeted with a crash error dialog after a lengthy wait.

The second option, which is the one that I had to go with is to use Microsoft Access to do the conversion. Fortunately, Access will open the .DBF files with no problem. I chose to use the import approach as opposed to the linked tables approach. Due to the size of data that I was dealing with, it was actually faster to load the respective tables into Access, and then use the SQL Server upsizing wizard in Access to push the data into a local SQLExpress instance. Once that had completed, I then detached the database, copied the .MDF and .LDF files to the server and reattached.

The only glitch I’ve had with this process is that for some reason Access will fail to copy data into the SQL Server tables. It’s erratic and I’ve not determined why it does this. There are no errors produced during the export process. It creates the table structure with no problem, but occasionally it will fail to copy the data – even though it runs for several minutes. That’s a battle for another time.

Now I’m off to resume my work on the geocoding functionality that will ultimately pull in this data.

Quick Tip on SQL Server 2008 Database Projects

I just imported the scripts from an existing SQL Server 2008 database. All went well, except that some of the stored procedures made reference to objects from system databases, which the newly imported scripts couldn’t resolve.

Database projects support making references to other databases in a few different ways. One way is via .dbschema files that you can generate from your projects. This approach is a loosely-coupled approach and improves reuse across your projects.

In the case of the master or msdb databases, Microsoft ships .dbschema files for these for this exact purpose. Simply add a database reference to your project and browse to the directory where Microsoft ships these files: [Program Files]\Microsoft Visual Studio 10.0\VSTSDB\Extensions\SqlServer\Version\DBSchemas, where Version is the version of SQL Server that you are using (such as 2005 or 2008).

For more info on using database references, here’s the link to MSDN:

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