Unreadable value returned in VBA/ADO from SQL Server 2019

1
votes

To monitor SQL Server jobs in a VBA app, I run the sp_get_composite_job_info stored procedure from the msdb database using ADO. I check the current_execution_step field once I have the results in a ADODB.Recordset. This has worked fine with SQL Server 2012 for years.

We just migrated to SQL Server 2019 and now the same code which still works on SQL Server 2012 is returning an unreadable value for the current_execution_step field.

Instead of "4 (Step Name)", I get a bunch of rectangles and squares.

If I run the same call to sp_get_composite_job_info in SSMS, I can read the current_execution_step field just fine on both servers. It's only when I try to read that field in VBA/ADO that it's gibberish.

What's worse, I can read other fields from that same recordset just fine. It's only this one field (which just happens to be the only field I need to read) that is unreadable.

The driver I'm using in VBA is ODBC Driver 17 for SQL Server. But again, I use the same driver for both servers, and only 2019 has this issue.

What could be causing this?

2
sql-servervbaadosql-server-2019
Unfortunately sp_get_composite_job_info is an undocumented procedure, so it is possible that it has changed the data type of that column. You should also note that although VBA can use Unicode, the VBA IDE cannot display it properly so don’t be misled by the debugger in this regard. - RBarryYoung
Yeah, my first thought was that it was unicode and I read somewhere that the IDE couldn't display unicode, so I tried just displaying it in a control, but it was the same gibberish. I also tried the StrConv function to convert it from unicode, but that didn't work either. - WATYF
Could be UTF-8, SQL Server supports that now but VBA would need a conversion. Or maybe print out the hex codes and see if anybody recognizes it. - RBarryYoung

2 Answers

2
votes

The SQL Server 2012 version of sp_get_composite_job_info (undocumented as @RBarryYoung mentioned) returns current_execution_step as nvarchar(128) whereas SQL Server 2019 returns nvarchar(MAX).

ADO is unaware of SQL Server datatypes introduced after SQL Server 2000. Try installing the latest MSOLEDBSQL OLE DB driver (ADO is natively OLE DB) and specifying the DataTypeCompatibility=80 in the connection string.

1
votes

Fortunately, the answer was as simple as going back to OLE and switching the Provider to SQLNCLI11 instead of SQLOLEDB. Since SQLNCLI11 is omnipresent, I don't have to hunt down all of the machines that might use these apps and install MSOLEDBSQL on them.