6

I had an issue on an instance of SQL Server I'm supporting and many of the Stack sites have information on the sp_OA* stored procedures,
but most of them basically say "Don't use those; they are deprecated/buggy/leaky".

I'm going to try to implement work arounds for my specific issue, but my question is:

  • when did features such as sp_OACreate and sp_OAMethod become deprecated?

I have to explain this to my Sys Admin and boss in order to justify a rewrite and I haven't come across much just by Googling (although perhaps my search terms are poor).

1 Answer 1

9

While I have been unable to find any official notice of deprecation for the OLE Automation stored procedures (sp_OA*), I believe they are generally regarded as deprecated as of the release of SQL Server 2005. Extended Stored Procedures, on the other hand, were officially deprecated as of SQL Server 2005. Both of those features were obsoleted by the introduction of the CLR Integration feature (i.e. SQLCLR).

When features become deprecated, they are listed in the following documentation:

Deprecated Database Engine Features in SQL Server 2005

That link takes you to the SQL Server 2005 version of the document. You can use the drop-down towards the top to check other versions. No version seems to list either "OLE Automation" or "sp_OA", so it would appear that the feature is not officially deprecated.

Yes, you heard correctly that the OLE Automation stored procedures are buggy and leaky (or at least have been at various times / service pack levels). They are less secure than SQLCLR (the "OLE Automation Stored Procedures" feature is either enabled or disabled; there is no control over who uses the feature if enabled, or how it is used, both of which are controllable -- to a degree -- in SQLCLR).

Also, as far as I have seen, the OLE Automation API (i.e. COM) has not been updated to work with any datatypes introduced as of SQL Server 2005 (i.e. MAX types, XML, DATE, TIME, DATETIMEOFFSET, CLR types, etc.).

You should be using SQLCLR instead of sp_OA* stored procedures for any new code. For existing code that is working, it is probably fine to leave it as is. But if that existing sp_OA* code should break, then it would be better to rewrite as SQLCLR instead of trying to get the sp_OA* approach working.

8
  • 1
    When commands are being depreciated they (Microsoft) usually indicate it in the documentation on BOL, but this shows the feature is still in 2016.
    – user507
    Dec 19, 2016 at 5:14
  • @ShawnMelton Yes, I had said that it was only "regarded" as deprecated, but that no documentation listed it as such, and linked to the deprecation page. Regardless, I just updated to be clearer. Dec 19, 2016 at 6:23
  • Marking this as the answer although, this doesn't bode well for me if they aren't officially deprecated as calls to the sp_OA* methods are crashing the entire SQL Server instance and a rewrite is going to need a significant amount of justification because this has been working as is for over 12 years and it just broke over the weekend.
    – akousmata
    Dec 19, 2016 at 14:47
  • 1
    @akousmata So what changed recently? Service pack upgrade? As I mentioned in the answer, IF it is working, then ok to leave as is. But if any amount of time needs to be spent fixing, then you are better off re-writing as SQLCLR since it has better security, supports newer data types, probably performs better, etc. That and I doubt you will get much support for anything related to sp_OA* procs, so that is a valid business reason to re-write as SQLCLR right there. Dec 19, 2016 at 14:51
  • 1
    On Sunday at 2:00am, the system event logs show a reboot initiated by the user NT SERVICE\SQLIaaSExtension with a reason of Legacy API shutdown. There was also a comment of Microsoft SQL Server Automated Patching needs to restart your machine. None of our stored procs that call the sp_OA* methods have been working since then. I am debating opening a separate question for this, but need to do more research first.
    – akousmata
    Dec 19, 2016 at 15:08

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.