SQL CLR that uses the 32-bit SLXOLEDB provider on x64 SQL Server

[pgaule]

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.
System.InvalidOperationException:
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 System.Data.OleDb.OleDbConnection.Open()
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?

[ChrisGrant]

Try compiling you app as x86.

Hope this helps.

[mcessna]

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/.

Thanks

Mike

[pgaule]

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.

[mcessna]

Ok, glad you find a simpler solution.

Thanks

Mike