Question

techresearch7777777 on Thu, 05 Apr 2012 23:52:59


Hello, anyone know how this can be done?  Thanks in advance.


Sponsored



Replies

Janos Berke on Fri, 06 Apr 2012 07:48:13


Hi,

Can you provide a bit more explanation please? If you are referring that SQL CLR can call a 32 bit dll in a 64 bit system, then the answer is yes.  If you are refereing that can SQL 32 bit installed on a 64 bit then the answer is also yes, but it is not a good idea.

Janos

Chris Hohmann on Fri, 06 Apr 2012 18:28:14


Actually, the OP posted the question on my behalf. Here are some more details.

We've recently migrated from SQL Server 2000 (32-bit) to SQL Server 2008 R2 (64-bit). We have a stored procedure that calls a 32-bit COM dll via the OLE Automation Services extended procedures (e.g. sp_OACreate). This worked fine in SQL Server 2000 (32-bit), but is failing in SQL Server 2008 R2 (64-bit). Here's a knowledge base article describing the error we are encountering:

http://support.microsoft.com/kb/932872

The article suggests recompiling the dll for 64-bit. Unfortunately, this is a third party dll so I don't think that option is feasible (although I might be wrong). Here are a few approaches I've been considering. I'd appreciate any feedback anyone might have.

1. Abstract out the call to the COM component into an SSIS package and call the 32-bit version of dtexec from within the stored procedure to execute the package.

2. Create a vbscript/powershell script to call the COM component and call the script from within the stored procedure.

3. I had not considered using SQL CLR but now that you mention it that sounds like a promising idea so I'm going to add it to the list. :)

4. Leave the one database that uses the COM dll where it is on SQL Server 2000 (32-bit). This is the least attractive option and I'd very much like to avoid this if at all possible. I'm just listing it here as a fallback option in case everything else fails.

Janos Berke on Fri, 06 Apr 2012 19:10:43


Chris,

You may decide which works for you in terms of security, maintenance and performance. I would try the first 3 and select the one which requires less maintenance overhead, performance is good and secure. Personally I like SQL CLR :)

Janos

Chris Hohmann on Tue, 10 Apr 2012 17:48:54


I ended up going with option 2. I created a powershell script to call the 32 bit COM component and called the script from within the stored procedure. It's working well. The one road bump I ran into was that you need to run powershell with the single threaded apartment command line option (-Sta) when working with COM components. I thought I'd mention it for the benefit of others who may encounter this thread. Thanks for your feedback.

Janos Berke on Tue, 10 Apr 2012 18:17:33


Thanks Chris. This is really a valuable information. 

andytilley on Fri, 09 Nov 2012 18:54:43


I have a very similar problem and found this thread as it mentions CLR:

I have written a .NET class library containing two CLR stored procedures. They both use the MSTR SDK (9.2.1). One of them fires an event, one of them expires the cache. This is implemented using the IDSSServerAdmin and IDSSScheduleAdministrator classes in the SDK. The .NET component has a reference to Interop.M5TypLib.DLL. I registered this with my development SQLServer (which is 32-bit), by means of creating assemblies Interop.M5TypLib and my own library (MSTRCLRLib) - like this:

create assembly "Interop.M5TypLib"

from 'C:\Program Files\Common Files\MicroStrategy\Interop.M5TYPLib.dll' WITH PERMISSION_SET = UNSAFE

create assembly MSTRCLRLib

from 'c:\temp\MSTRCLRLib.dll' WITH PERMISSION_SET = UNSAFE

create assembly "Interop.M5TypLib" from 'C:\Program Files\Common Files\MicroStrategy\Interop.M5TYPLib.dll' WITH PERMISSION_SET = UNSAFE

create assembly MSTRCLRLib from 'c:\temp\MSTRCLRLib.dll' WITH PERMISSION_SET = UNSAFE Works like a charm on SQLServer 32-bit.

My nightmare starts when trying to implement this on SQLServer x64. Both assemblies register fine, but calling my code on the x64 results in:

Retrieving the COM class factory for component with CLSID {2879DA00-9372-11D1-8E23-006008960167} failed due to the following error: 80040154.

Looking up the CLSID in the registry looks like it's trying to instantiate the DSSDataSourceEnumerator class - which would make sense, because that's the first thing the code does. The InProcServer32 for this is M5CLICON.DLL. The error number suggests the DLL isn't registered. But where do I need to register it? It exists on the server. The same SDK is installed on both by 32-bit SQLServer and my 64-bit SQLServer. I actually tried something else: I used corflags to force my component to run 32-bit. That ended in failure too:

An error occurred in the Microsoft .NET Framework while trying to load assembly id 65562. The server may be running out of resources, or the assembly may not be trusted with PERMISSION_SET = EXTERNAL_ACCESS or UNSAFE. Run the query again, or check documentation to see how to solve the assembly trust issues. For more information about this error: 

System.IO.FileLoadException: Could not load file or assembly 'mstrclrlib, Version=0.0.0.0, Culture=neutral, PublicKeyToken=null' or one of its dependencies. The given assembly name or codebase was invalid. (Exception from HRESULT: 0x80131047)

System.IO.FileLoadException: 

   at System.Reflection.Assembly._nLoad(AssemblyName fileName, String codeBase, Evidence assemblySecurity, Assembly locationHint, StackCrawlMark& stackMark, Boolean throwOnFileNotFound, Boolean forIntrospection)

   at System.Reflection.Assembly.InternalLoad(AssemblyName assemblyRef, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.InternalLoad(String assemblyString, Evidence assemblySecurity, StackCrawlMark& stackMark, Boolean forIntrospection)

   at System.Reflection.Assembly.Load(String assemblyString)

Any help appreciated. 

Janos Berke on Fri, 09 Nov 2012 19:11:07


Hi,

Try to install the 32bit edition of the sdk on the 64bit server. 

I hope it helps.

Janos