12-06-2013 11:50 AM
Long story short:
We have a data warehouse that relies on a UDF in SQL Server to generate a new accountid. This UDF uses a SQL CLR assembly that levereges the SLXOLEDB.1 provider. This works fine on our current server, which is Win2003/SQL2005 32-bit.
However, we are migrating to a new server with Win2008 and SQL2008 64-bit. I am hitting a wall when I try to install the CLR on the new server.
If I install the CLR on SQL Server, I get the following error when I try to execute the UDF:
Msg 6522, Level 16, State 1, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "GetNextSLXID":
System.InvalidOperationException: The 'SLXOLEDB.1' provider is not registered on the local machine.
at System.Data.OleDb.OleDbServicesWrapper.GetDataSource(OleDbConnectionString constr, DataSourceWrapper& datasrcWrapper)
at System.Data.OleDb.OleDbConnectionInternal..ctor(OleDbConnectionString constr, OleDbConnection connection)
at System.Data.OleDb.OleDbConnectionFactory.CreateConnection(DbConnectionOptions options, Object poolGroupProviderInfo, DbConnectionPool pool, DbConnection owningObject)
at System.Data.ProviderBase.DbConnectionFactory.CreateNonPooledConnection(DbConnection owningConnection, DbConnectionPoolGroup poolGroup)
at System.Data.ProviderBase.DbConnectionFactory.GetConnection(DbConnection owningConnection)
at System.Data.ProviderBase.DbConnectionClosed.OpenConnection(DbConnection outerConnection, DbConnectionFactory connectionFactory)
at GetNewID.NewID(String Environment, String TableName)
From what I gather, this is because 64-bit SQL server can't locate/use the 32-bit SLXOLEDB provider?
If I compile the assembly for the x86 platform rather than "Any CPU", then SQL refuses to run it because it can't run a 32-bit assembly.
Do I have any way to fix this? Do I need to install the 32-bit version of SQL2008 instead?
02-06-2014 09:52 AM
Since you cannot load the 32 bit SLXOLEDB.DLL in a 64 bit process, the only solution I can think of is to wrap the functionality you need in a 32 bit out of process COM server and call it from the UDF (assuming you can make COM calls).
The following old article talks about similar issues (unrelated to UDF): http://blog.mattmags.com/2007/06/30/accessing-32-bit-dlls-from-64-bit-code/.
02-06-2014 01:00 PM
Thanks for all the tips. Our business partner gave me a stored procedure that generates the IDs (similar to this -> http://customerfx.com/pages/crmdeveloper/2013/12/23/sql-stored-procedure-to-create-saleslogix-table-... and I updated our process to call the sproc instead of the UDF. I almost went with the wrapper approach you suggested, but in the end, it was easier to update the re-write a few lines of code in the process to use the sproc.
I realize this was a hack, but we don't have any sync servers so I really don't care. It was a simple solution to an ugly problem.