Need CLR replacements for OLE Automation procedures

Question

Need CLR replacements for OLE Automation procedures

Marios Philippopoulos

SSC Guru

Points: 57190
More actions
May 20, 2009 at 8:35 am

#384621

Hi,
Anyone have CLR code for replacing the functionality offered by the following procedures?
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop
Also, currently on our SQL-2000 instance, these procedures are being used for making web-service calls. Does anyone have CLR code for this sort of task that they are willing to share?
Thanks for any advice!
__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 1

These call into COM which is UNSAFE in SQLCLR. Usually you can get away from COM completely and into a managed provider for what you want to do which would then be an EXTERNAL_ACCESS assembly in SQLCLR which is much better.

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 2

Jonathan Kehayias (5/21/2009)
These call into COM which is UNSAFE in SQLCLR. Usually you can get away from COM completely and into a managed provider for what you want to do which would then be an EXTERNAL_ACCESS assembly in SQLCLR which is much better.

Thank you, is it relatively easy to construct an EXTERNAL_ACCESS assembly for web-service calls?

Any links you might recommend?

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Jonathan Kehayias One Orange Chip Points: 26778 More actions · Answer 3

What kind of service are you trying to call? There are generally 2 types, standard .NET webservices that have a ASMX that you can reference as a service, or services like the Google Maps API which you hit with a parameterized request and it returns an XML response. How you call them is very different.

RBarryYoung SSC Guru Points: 143329 More actions · Answer 4

Marios Philippopoulos (5/20/2009)
Hi,
Anyone have CLR code for replacing the functionality offered by the following procedures?
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop
Also, currently on our SQL-2000 instance, these procedures are being used for making web-service calls. Does anyone have CLR code for this sort of task that they are willing to share?

Not only do these call into COM, they are generic "Call By Name" methods for COM objects. Even without the CLR restrictions, you would be hard pressed to impement something this generic in .Net that could be considered even remotely safe. Mind you, it's not impossible, but it seems extremely difficult.

More reasonable would be to identify explicitly which .Net classes and services you need to have available and code just for them.

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 5

Jonathan Kehayias (5/22/2009)
What kind of service are you trying to call? There are generally 2 types, standard .NET webservices that have a ASMX that you can reference as a service, or services like the Google Maps API which you hit with a parameterized request and it returns an XML response. How you call them is very different.

Thanks, I'll find out and will post an update.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Marios Philippopoulos SSC Guru Points: 57190 More actions · Answer 6

RBarryYoung (5/22/2009)
Marios Philippopoulos (5/20/2009)
Hi,
Anyone have CLR code for replacing the functionality offered by the following procedures?
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop
Also, currently on our SQL-2000 instance, these procedures are being used for making web-service calls. Does anyone have CLR code for this sort of task that they are willing to share?
Not only do these call into COM, they are generic "Call By Name" methods for COM objects. Even without the CLR restrictions, you would be hard pressed to impement something this generic in .Net that could be considered even remotely safe. Mind you, it's not impossible, but it seems extremely difficult.
More reasonable would be to identify explicitly which .Net classes and services you need to have available and code just for them.

Thanks. Yes, I think this is in fact one of the strengths of SQL-CLR, ie. convert from a catch-all functionality such as OLE Auto or xp_cmdshell, to a much more specialized functionality, such as web-service calls or listing the contents of a folder. In terms of system security it is much more preferable giving app users just the access they need and not more, and by providing more specialized functionality to users, that goal can be achieved.

__________________________________________________________________________________
SQL Server 2016 Columnstore Index Enhancements - System Views for Disk-Based Tables[/url]
Persisting SQL Server Index-Usage Statistics with MERGE[/url]
Turbocharge Your Database Maintenance With Service Broker: Part 2[/url]

Solomon Rutzky SSCoach Points: 16430 More actions · Answer 7

Marios Philippopoulos (5/20/2009)
Hi,
Anyone have CLR code for replacing the functionality offered by the following procedures?
sp_OACreate
sp_OADestroy
sp_OAGetErrorInfo
sp_OAGetProperty
sp_OAMethod
sp_OASetProperty
sp_OAStop
Also, currently on our SQL-2000 instance, these procedures are being used for making web-service calls. Does anyone have CLR code for this sort of task that they are willing to share?

Hi there. Just to clarify here: the CLR Integration feature itself (i.e. the ability to run .NET code from within SQL Server, commonly known as SQLCLR) is the replacement for all of those sp_OA* procedures. It would make little to no sense to attempt to use SQLCLR to provide a similar paradigm whereby one can create any Object they choose, get and set properties individually, through separate T-SQL statements, etc. I can think of a way to do that, but it would be horribly inefficient and error-prone when compared to doing it properly.

With regards to making Web Service calls: at this point (7.5 years later), there are plenty of examples on various sites on how to code your own. Or, if one is ok with a generic approach -- generic WebRequest that requires building the request manually and parsing the response XML -- then there is a pre-done SQLCLR function called INET_GetWebPages in the SQL#[/url] library (which I wrote, and this particular function is only available in the Full version, not in the Free version) that does just this. It supports POST requests, customizing HTTP headers, text or binary responses, etc.

Take care,

Solomon..

SQL# — https://SQLsharp.com/ ( SQLCLR library ofover 340 Functions and Procedures)
Sql Quantum Lift — https://SqlQuantumLift.com/ ( company )
Sql Quantum Leap — https://SqlQuantumLeap.com/ ( blog )
Info sites — Collations • Module Signing • SQLCLR