sp_OAxxx procs
-
I have a developer who wishes to run dot net components using the sp_OAxxxx procs, he says that the issues with com components cannot happen with dot net .. as this is a business critical production server is it safe to run the sp_OAxx procs ?
Any pointers would be useful
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
He's right in some aspects, but not all. Bad code is still bad code. You need to get him to define exactly what he intends to use and do. See if there is an alternative mechanism. The use of sp_OAxxx is usually a no-no from a security perspective as well as a performance and stability one unless you're absolutely sure you know what is being done.
K. Brian Kelley
@kbriankelley -
They want to manipulate xml documents, the current methods are giving some problems.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
The next question is, is there a need to do this from inside SQL Server?
K. Brian Kelley
@kbriankelley -
I have to say that calling .net components from the sp_oa set of SP's isn't something I've attempted since I didn't think you could do it, or at least i wouldn't have thought it would be supported.
Since the OA in sp_oa.. is for OLE Automation (COM) and one of the main ideas of .net was to get away from COM, would this work? Are you creating a COM wrapper around the .net component to be able to get this to work at all??
Please correct me if I'm wrong!
-
As you can probably guess this is not an area I'm totally at home with, :-), however I suspect you're probably right. Our web services people think this is so but I'm having to deal with developers who are, for one reason or another, struggling with deadlines and just possibly poorly thought out methods.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Test it on a non-production system. Baseline the system beforehand. Memory usage, processor utilization, etc. Then put 'em in and try it. Of course, how will they be called? The sp_OA stored procedures are intended for sysadmin use only.
K. Brian Kelley
@kbriankelley -
I have a series of environments to test under thankfully, it does seem that largely the concept is seen as being ok, just can't find anyone actually using it! Sources at microsoft say its fine and how they do things as a run up to Yukon.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Firstly a disclaimer, I haven't used this, so what I'm saying now is based on my interpretation of the documentation.
The sp_OACreate, Destroy etc. methods are COM methods, they lookup classes registered on the machine that support IDispatch which is a COM interface. .NET classes do not support this interface as they use a different method of registering, instanciation etc. However MS provide the ability to generate a COM Interop layer for .NET components. This layer acts like a proxy to the underlying .NET code. The interop layer supports IDispatch and will be registered in the Windows Registry, therefore the standard OLE Automation rules apply. All the calls are routed through the interop layer which does the appropriate data type conversion, casting etc. to and from the managed code.
As far as SQL is concerned it is dealing with a fully fledged COM component.
The sticky issue is that of safety, and has already been mention .NET code is no more safe than COM code. The developers are most likely referring to the tighter security and garbage collection that .NET offers. You are significantly less likely to suffer memory leak problems with .NET code than traditional COM based code, and you can lock down security using the wizards provided with the .NET framework (under administrative tools). However becuase of the way that garbage collection works you may find your normal memory usage increases when using .NET classes (COM tends to peak and trough whereas .NET keeps using memory till it thinks it should clean up).
Under Yukon, .NET classes can be called directly from SQL as it is tied in with the .NET framework, so if you write the code in .NET now, you can just discard the interop layer later on.
At the end of the day, poor programming is language independant, so it's a question of how good are your developers, not what language are they using.
Hope this helps
Tony
-
Thanks, I sometimes wonder if even half the provided tools and methods are ever used. I haven't found anyone calling dot net from sql server, not that this proves too much of course, but with dot net being established for some time now I imagined I'd find people using it. I also admit maybe I should be looking in other forums???
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
Colin
Perhaps the reason you haven't found anyone else doing it is becuase there are better ways. Someone has already mentioned not calling from inside SQL, and this may or may not be suitable in your case. Some people may write extended stored procedures if they have any hardcore processing that needs doing from SQL. If your data is persisted and identifiable, then you could just shell out to an exe, in which case the interop etc becomes irrelevent (though ensure that you want the caller of the T-SQL to have the privelages to call xp_cmdshell).
You said 'half the provided tools probably aren't used'. I would guess they are all used, but only a little, perhaps too little to justify having them(?), still isn't it nice to have options
Tony
-
You may be right, but there again going forward dot net will replace the com or extended procs dll, our developers are moving forward with dot net and don't want to step backward, also when we move to Yukon we will have the components ready for the clr. The step in question is to produce a particular function call from T-SQL.
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/ -
As a developer I understand their enthusiasm, and don't get me wrong, I wasn't saying the .NET solution was the wrong one, just throwing up options.
In your earlier posts you said that the dev' team were running up against deadlines. Perhaps investigating SQL - .NET interop in a quick and dirty way would be the best next step. My early work with the COM interop in the beta and version 1.0 frameworks proved the system to be less than perfect with certain data types, object references etc. but things have come a long way since then (and are going yet further at a rate of knots). As a rule I work mainly with pure managed code solutions wherever possible
I wish you the best of luck in getting it working, and please post back and let us know how it goes, never know when I might be landed with the same request.
Tony
-
Will do my best to remember, Ken Henderson's book on sql internals has some stuff on this, it's a great book even if I don't understand large parts of it <grin >
[font="Comic Sans MS"]The GrumpyOldDBA[/font]
www.grumpyolddba.co.uk
http://sqlblogcasts.com/blogs/grumpyolddba/
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply