May 3, 2012 at 2:44 pm
I'm running SQL 2008R2 and W2k 2008R2. When I call a COM object through a stored proc using sp_OACreate I get the error "Class Not Registered". I've been looking for answers and several threads have stated that it might be an issue between 32/64 bit. The application dll I'm calling is installed as 32 bit and I'm executing this proc through Management Studio.
If the issue is in fact a 32/64 bit problem is there a way to execute it in a 32 bit environment?
TIA.
May 3, 2012 at 3:06 pm
I'm about 100% sure you are screwed. I am assuming it is an in-process dll? If so I don't think you can use it.
CEWII
May 4, 2012 at 6:38 am
Thank you for the encouraging words... lol!
I was kinda expecting that answer. I've been researching it and it seems to be the overall opinion for most scenarios.
I can't tell you if it's an in process dll or not b/c I don't know exactly what you mean by that or how to tell if it is, however I've executed sp_OACreate under the different contexts (1,4 and 5) in regards to in/out of process and none work.
Thanks for the reply though. If anyone else has any workarounds that would be fantastic!
May 4, 2012 at 6:50 am
It seems you can execute configure SSIS jobs to run in 32 bit runtime environment however I'm not running a job. I'm executing this directly from a proc.
Would it be possible to execute a proc that executes a SSIS job and gets the output from that SSIS job?
May 4, 2012 at 8:36 am
AVB (5/4/2012)
Thank you for the encouraging words... lol!
Sorry man..
I can't tell you if it's an in process dll or not b/c I don't know exactly what you mean by that or how to tell if it is, however I've executed sp_OACreate under the different contexts (1,4 and 5) in regards to in/out of process and none work.
Is it an EXE or a DLL, since a com object can be either. I'm guessing DLL which would make it in-process.
It seems you can execute configure SSIS jobs to run in 32 bit runtime environment however I'm not running a job. I'm executing this directly from a proc.
Would it be possible to execute a proc that executes a SSIS job and gets the output from that SSIS job?
Keeping in mind that you can never have more than one instance of a job running at any one point that could be tricky.
BUT you are onto something. It would be possible to execute the 32-bit flavor of the DTExec CLI using a command shell. However I am not recommending using xp_cmdshell which I believe should be disabled. I prefer to use SQLCLR. To that end I would point you to one of my CodePlex projects http://nclsqlclrcmdexec.codeplex.com/[/url]. You could have more than one copy running at a time but I have to say there are some performance costs associated with this. So if it was something that is executed a lot I think we might have to rethink.. But the package could be passed an ID that is used directly or to access a table to give additional parameters, then do something and write to a table. That could work. but I stress this is not a very scalable solution.
CEWII
May 4, 2012 at 8:44 am
Thanks Elliot for your feedback. It is a dll so it's in process. There are many methods that this dll supports... VB,C#, Access, API calls etc... I'm just trying to find a way to do it using SQL. : )
This is going to be called a lot and needs to process very quickly so that option probably won't be viable but I'll be happy to explore that option anyway.
May 4, 2012 at 8:58 am
I know SQL integrates with many of the .net languages so I wonder if something like this would work....
http://msdn.microsoft.com/en-us/library/ms190790.aspx
I have no experience with this so I'm not sure if it's even possible to do what I want to do.
May 4, 2012 at 3:55 pm
While .Net can call COM objects it often has a problem where the architecture is not the same as the caller (x86 vs x64). In SQLCLR this is actually more difficult since all of the assemblies have to be imported into SQL before they can be used in most cases. What I mean is that there are certain core libraries that you have available in SQLCLR that don't require importation. But there are MANY that do. I also don't think you can make the COM object wory, although old see http://social.msdn.microsoft.com/forums/en-US/sqlnetfx/thread/eaa2a257-e994-452b-855e-7d6841b15d42
I'm assuming a re-write of this COM object into .net is not an option? If so then if you compile it as MSIL it will be processor independent.
I was thinking about a workaround though. About the only way around the startup costs of the SSIS package is to keep it running and have it poll a queue and then take actions on the queue, this isn't as responsive as I'd like but it would work. The pacakge would be started by a job and would simply loop until it either died or was killed.
There is a way using Service Broker using External Activation but I got to say I have looked at it and its kind of complex even for an experienced developer.
I'll think about it some more..
CEWII
October 16, 2014 at 8:35 am
It's easy to call 32-bit dll methods from 64-bit SQL server.
Unregister all dlls need to be called but register its dependencies.
Go to control panel->administrative tools->component services->my computer->Com+ applications
right click -> New -> Next -> Create empty application ->choose Server application (set name a.e. "my dlls") -> Next -> choose This user and enter sql server account creds -> next->next->next->Finish
right click "my dlls" ->Properties->Security->uncheck Enforce access checks for this app->OK
open "my dlls" folder and select components -> right click -> new ->component->next->install new->add and select dll-next-finish
repeat if needed
Check ability to create object
sp_configure 'show advanced options', 1;
GO
sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
sp_configure 'Ole Automation Procedures', 1;
GO
RECONFIGURE;
GO
declare @hr int
declare @name varchar(255)
declare @object int
DECLARE @output varchar(255);
DECLARE @source varchar(255);
DECLARE @description varchar(255);
---Creating object
exec @hr = sp_OACreate 'mylib.myobj',@object OUT
----ERRRRRRRRRRRRRR
PRINT 'OLE Automation Error Information';
EXEC @hr = sp_OAGetErrorInfo @object, @source OUT, @description OUT;
IF @hr = 0
BEGIN
SELECT @output = ' Source: ' + @source
PRINT @output
SELECT @output = ' Description: ' + @description
PRINT @output
END
ELSE
BEGIN
PRINT ' sp_OAGetErrorInfo failed.'
RETURN
END;
-----------------
select @object
-------Killing obj
exec sp_OAdestroy @object
---- Read errs on Messages tab
-----EOF
Doesn't grant rights to sp_oacreate for users!!!! Use certificate users and signed wrapper-procedures for calling methods.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply