Solr, DataImportHandler, UUID and SQL Server

I’ve recently been setting up Apache Lucene/Solr to index static PDF files and also import data to the collection from MS SQL Server.

After successfully indexing PDF files and providing them with a unique id via UUID I wanted to import several SQL tables that each had a ID column called ‘id’. These tables would obvioulsy have overlapping ID’s at some stage so I wanted to use UUID on these documents as well.

I struggles to find much documentation on Solr, SQL Server and UUID, but after successfully setting up UUID via http://wiki.apache.org/solr/UniqueKey, you also need to the UpdateRequestHander on the dataimport handler as well. Therefore the following code:

<requestHandler name=”/dataimport” class=”org.apache.solr.handler.dataimport.DataImportHandler”>
<lst name=”defaults”>
<str name=”config”>db-data-config.xml</str>
</lst>
</requestHandler>

changed to this

<requestHandler name=”/dataimport” class=”org.apache.solr.handler.dataimport.DataImportHandler”>
<lst name=”defaults”>
<str name=”config”>db-data-config.xml</str>
<str name=”update.chain”>uuid</str>
</lst>
</requestHandler>

then auto creates unique id’s when importing on mass from SQL Server tables.

SQL Server: create failed for user/ User, group, or role already exists in the current database

If experiencing the following errors in SQL Server:

Create failed for User ‘xyz’. (Microsoft.SqlServer.Smo)

User, group, or role ‘xyz’ already exists in the current database. (Microsoft SQL Server, Error: 15023)

The above error can sometime occur when migrating a SQL Server Database from one staged environment to another. If both those environments have the same users present you can get issues when trying to add users from the new staged environments to the recently restored DB that the users are already present within.

A solution I found is to map the DB users to the accounts already within the DB. This is achieved by using the stored procedure sp_change_users_login

USE AdventureWorks
GO
EXEC sp_change_users_login 'Update_One', 'xyz', 'xyz'
GO

The above will take the specified user {parameter 2} and map to the login user {parameter 3}.
Further information and other actions are available here: http://msdn.microsoft.com/en-us/library/ms174378(v=sql.105).aspx

Note: MSDN state this feature will be removed in a “future version”.