none
Upgrading from SQLNCLI11 to MSOLEDBSQL RRS feed

  • Question

  • We are in the process of upgrading our sql server database to sql server 2017, after we upgraded some of our SSIS packages stopped working where we encountered the following issue: 
    SSIS Error Code DTS_E_OLEDBERROR.  An OLE DB error has occurred. Error code: 0x80004005.
    An OLE DB record is available.  Source: "Microsoft SQL Server Native Client 11.0"  Hresult: 0x80004005  Description: "The metadata could not be determined because statement 'REVERT --Check if SSB is enabled in this database' in procedure 'sp_send_dbmail' does not support metadata discovery."
    After some research it looks like we need to change our provider from SQLNCLI11 to MSOLEDBSQL. My question is if we upgrade to MSOLEDBSQL is this backward compatible with anything that needs SQLNCLI11? And if we decide to stick with SQLNCLI11 is there any resolution/workaround for this error?
    Tuesday, September 17, 2019 7:18 PM

Answers

  • I would not expect that MSOLEDBSQL to resolve the issue.

    Instead you need to add the WITH RESULT SETS clause to the stored procedures you are calling, so that SSIS can get the information about the result sets.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Linda_L Monday, September 23, 2019 3:07 PM
    Tuesday, September 17, 2019 9:13 PM

All replies

  • Hi Linda_L,

    The latest MSOLEDBSQL is absolutely backward compatible with anything that used SQLNCLI11.

    "...Important

    The previous Microsoft OLE DB Provider for SQL Server(SQLOLEDB) and SQL Server Native Client OLE DBprovider (SQLNCLI) remains deprecated and it is not recommended to use either for new development work..."

    Microsoft OLE DB Driver for SQL Server

    Another useful link: Released: Microsoft OLE DB Driver for SQL Server

    Tuesday, September 17, 2019 7:29 PM
  • I would not expect that MSOLEDBSQL to resolve the issue.

    Instead you need to add the WITH RESULT SETS clause to the stored procedures you are calling, so that SSIS can get the information about the result sets.


    Erland Sommarskog, SQL Server MVP, esquel@sommarskog.se

    • Marked as answer by Linda_L Monday, September 23, 2019 3:07 PM
    Tuesday, September 17, 2019 9:13 PM
  • Hi Linda_L,

    Please refer to the similar issue:

    Issue using Temp Tables in Stored procedures.

    Best Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Wednesday, September 18, 2019 9:08 AM
  • Hi Linda_L,

    Please remember to click "Mark as Answer" the responses that resolved your issue.

    This can be beneficial to other community members reading this thread. 

    Thank you.

    Regards,

    Mona


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com

    Monday, September 23, 2019 9:41 AM
  • Thanks for everyones responses. So, unfortunately MSOLEDBSQL didn't actually resolve my issue. I'm gong to try adding the WITH RESEULT SETS clause to my stored procs and see what happens.
    Monday, September 23, 2019 3:07 PM
  • Adding the WITH RESULT SETS clause in the stored procedure call worked. However I'm not sure why this works? Can someone explain?

    Also how does  my ssis package work fine with SQLOLEDB.1 but not with MSOLEDBSQL. Why do I need to add the WITH RESULT SETS clause in my ssis package to get it to work with MSOLEDBSQL? Are there plans to fix this is a future release of MSOLEDBSQL?

    Monday, September 23, 2019 4:58 PM