Need CLR replacements for OLE Automation procedures

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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]

  • Jonathan Kehayias

    One Orange Chip

    Points: 26672

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • Marios Philippopoulos

    SSC Guru

    Points: 57030

    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: 26672

    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.

    Jonathan Kehayias | Principal Consultant | MCM: SQL Server 2008
    My Blog | Twitter | MVP Profile
    Training | Consulting | Become a SQLskills Insider
    Troubleshooting SQL Server: A Guide for Accidental DBAs[/url]

  • RBarryYoung

    SSC Guru

    Points: 143327

    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: 57030

    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: 57030

    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: 16129

    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 Lifthttps://SqlQuantumLift.com/ ( company )
    Sql Quantum Leaphttps://SqlQuantumLeap.com/ ( blog )
    Info sitesCollations     •     Module Signing     •     SQLCLR

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

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