Changing the SQL provider from SQLOLEDB.1 to SQLNCLI.1 causes the application to fail when accessing data through a stored procedure

I am maintaining a legacy application written in MFC / C ++. The database for the application is in SQL Server 2000. We recently applied some new functionality and found that when we change the SQL provider from SQLOLEDB.1 to SQLNCLI.1, some code that tries to retrieve data from the table using a stored procedure exits building.

The table in question is quite simple and was created with the following script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UAllergenText](
    [TableKey] [int] IDENTITY(1,1) NOT NULL,
    [GroupKey] [int] NOT NULL,
    [Description] [nvarchar](150) NOT NULL,
    [LanguageEnum] [int] NOT NULL,
CONSTRAINT [PK_UAllergenText] PRIMARY KEY CLUSTERED
(
    [TableKey] ASC) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF,
    IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO
ALTER TABLE [dbo].[UAllergenText]  WITH CHECK ADD  CONSTRAINT 
FK_UAllergenText_UBaseFoodGroupInfo] FOREIGN KEY([GroupKey])
REFERENCES [dbo].[UBaseFoodGroupInfo] ([GroupKey])
GO
ALTER TABLE [dbo].[UAllergenText] CHECK CONSTRAINT 
FK_UAllergenText_UBaseFoodGroupInfo]

      

        
        
        
      

    

Basically four columns where TableKey is the identity column and everything else is populated with the following script:

INSERT INTO UAllergenText (GroupKey, Description, LanguageEnum)
VALUES (401, 'Egg', 1)

      

        
        
        
      

    

with a long list of other INSERT INTOs that follow above. Some of the inserted lines contain special characters in their descriptions (for example, accents above letters). I originally thought that including special characters was part of the problem, but if I flush the table completely and then re-close it with just one INSERT INTO on top that has no special characters, it still doesn't work.

So, I moved ...

The data in this table is then available through the following code:

std::wstring wSPName = SP_GET_ALLERGEN_DESC;
_variant_t  vtEmpty1 (DISP_E_PARAMNOTFOUND, VT_ERROR);
_variant_t  vtEmpty2(DISP_E_PARAMNOTFOUND, VT_ERROR);

_CommandPtr pCmd = daxLayer::CDataAccess::GetSPCommand(pConn, wSPName); 
pCmd->Parameters->Append(pCmd->CreateParameter("@intGroupKey", adInteger, adParamInput, 0, _variant_t((long)nGroupKey)));
pCmd->Parameters->Append(pCmd->CreateParameter("@intLangaugeEnum", adInteger, adParamInput, 0, _variant_t((int)language)));

_RecordsetPtr pRS = pCmd->Execute(&vtEmpty1, &vtEmpty2, adCmdStoredProc);            

//std::wstring wSQL = L"select Description from UAllergenText WHERE GroupKey = 401 AND LanguageEnum = 1";
//_RecordsetPtr pRS = daxLayer::CRecordsetAccess::GetRecordsetPtr(pConn,wSQL);

if (pRS->GetRecordCount() > 0)
{
    std::wstring wDescField = L"Description";
    daxLayer::CRecordsetAccess::GetField(pRS, wDescField, nameString);
}   
else
{
    nameString = "";
}

      

        
        
        
      

    

DaxLayer is a third party data access library used by the application, although we have a source for it (some of which will be visible below). SP__GET_ALLERGEN_DESC is a stored process used to get data from a table and it was created with this script:

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

CREATE PROCEDURE [dbo].[spRET_AllergenDescription] 
-- Add the parameters for the stored procedure here
    @intGroupKey int, 
    @intLanguageEnum int
AS
BEGIN
    -- SET NOCOUNT ON added to prevent extra result sets from
    -- interfering with SELECT statements.
    SET NOCOUNT ON;

    -- Insert statements for procedure here
    SELECT Description FROM UAllergenText WHERE GroupKey = @intGroupKey AND LanguageEnum = @intLanguageEnum
END

      

        
        
        
      

    

When the SQL provider is installed in SQLNCLI.1, the application will explode:

daxLayer::CRecordsetAccess::GetField(pRS, wDescField, nameString);

      

        
        
        
      

    

from the above code snippet. So I went to GetField which looks like this:

void daxLayer::CRecordsetAccess::GetField(_RecordsetPtr pRS,
const std::wstring wstrFieldName, std::string& sValue, std::string  sNullValue)
{
    if (pRS == NULL)
    {
        assert(false);
        THROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField", 
        wstrFieldName, L"std::string", L"Missing recordset pointer."))
    }
    else
    {
        try
        {
            tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

            if ((tv.vt == VT_EMPTY) || (tv.vt == VT_NULL))
            {
                sValue = sNullValue;
            }
            else if (tv.vt != VT_BSTR)
            {
                // The type in the database is wrong.
                assert(false);
                THROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField", 
                wstrFieldName, L"std::string", L"Field type is not string"))
            }
            else
            {
                 _bstr_t bStr = tv ;//static_cast<_bstr_t>(pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value);                     
                 sValue = bStr;
            }
        }
        catch( _com_error &e )
        {
            RETHROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField", 
            wstrFieldName, L"std::string"), e.Description())
        }
        catch(...)
        {        
            THROW_API_EXCEPTION(GetExceptionMessageFieldAccess(L"GetField",
            wstrFieldName, L"std::string", L"Unknown error"))
        }
    }
}

      

        
        
        
      

    

Applicant here:

tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

      

        
        
        
      

    

Going to Fields-> GetItem brings us to:

GetItem

inline FieldPtr Fields15::GetItem ( const _variant_t & Index ) {
    struct Field * _result = 0;
    HRESULT _hr = get_Item(Index, &_result);
    if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
    return FieldPtr(_result, false);
}

      

        
        
        
      

    

Which then leads us to:

GetValue

inline _variant_t Field20::GetValue ( ) {
    VARIANT _result;
    VariantInit(&_result);
    HRESULT _hr = get_Value(&_result);
    if (FAILED(_hr)) _com_issue_errorex(_hr, this, __uuidof(this));
    return _variant_t(_result, false);
}

      

        
        
        
      

    

If you look at the _result going through this at runtime, the BSTR _result value is correct, its value is "Egg" from the "Description" field of the table. Continuing to go through traces through all the COM release calls, etc. When I finally get back to:

tagVARIANT tv = pRS->Fields->GetItem(_variant_t(wstrFieldName.c_str()))->Value;

      

        
        
        
      

    

And walk past it to the next line, the content of tv, which should be BSTR = "Egg", is now:

tv BSTR = 0x077b0e1c "ᎀݸﻮﻮﻮﻮﻮﻮﻮﻮﻮﻮﻮﻮ㨼㺛帛᠄"

      

        
        
        
      

    

When the GetField function tries to set the return value to tv.BSTR

_bstr_t bStr = tv;
sValue = bStr;

      

        
        
        
      

    

he unsurprisingly suffocates and dies.

So what happened to the BSTR value and why does this happen when the provider is set to SQLNCLI.1?

To do this, I commented out the use of the stored procedure in the topmost code and just coded the same SQL SELECT statement as the stored procedure and found that it works very well and the return value is correct.

In addition, users can add rows to the table through the app. If the application creates a new row in this table and retrieves that row using a stored procedure, it also works correctly unless you include a special character in the description, in which case it saves the row correctly, but blows up again exactly like above after fetching that line.

So, to generalize if possible, rows put into a table via an INSERT script will ALWAYS blow up the application when they are accessed by stored procedures (regardless of whether they contain any special characters). Rows placed into the table from the application by the user at runtime are correctly retrieved using the stored procedure IF they contain a special character in the description, after which they blew up the application. If you are accessing any of the rows in a table using SQL from code at runtime rather than in a stored procedure, then whether the special character in the description works or not.

Any light that can be shed on this would be greatly appreciated and I thank you in advance.