Database Monitor Item - SQL Server calling stored procedure

[cdecarlo]

I was trying to add a database monitor item into Zabbix which called a stored procedure in MS SQL server that returned a result set.

Something like the following (created by the DBA group):

create procedure foo as 
  set nocount on;
  exec proc2()
 select @@rowcount
return

I then configured the zabbix database monitor item and used the following syntax for calling the procedure

{call dbo.[foo]}

With various amounts of tweaking to the procedure I would only ever receive one of the following error messages back from the ODBC layer.

Cannot execute ODBC query: [SQL_NO_DATA]
or
SQL query returned empty result.

Running these through a couple of other SQL query tools did produce the correct results from the procedure call.

I even tried something like

select 1

as the last statement in the procedure as the most basic form of returning a resultset from a procedure (which with any of the other SQL tools that I use returns 1 row with 1 column which has a value of 1) but I was still getting the same error message from Zabbix.

Turning tracing on I see the following type of statements in the zabbix proxy logs (which looks like it actually returned a value..although it is definitely not the value that it should have been returning and I suppose it could have been from another query also running through that proxy)

zbx_odbc_connect() connected to Microsoft SQL Server(13.00.4466) using libmsodbcsql-17.7.so.2.1(17.07.0002)
End of zbx_odbc_connect()
In zbx_odbc_select() query:'{call dbo.[zabbix-monitor]}'
End of zbx_odbc_select()
In zbx_odbc_query_result_to_string()
In zbx_odbc_fetch()
column #1 value:'44630016'
End of zbx_odbc_fetch()
End of zbx_odbc_query_result_to_string():SUCCEED

Does anybody know the proper way to call a stored procedure in SQL Server (or have any examples they could share) that returns a resultset?

I'm running the ODBC on a Zabbix proxy VM running Ubuntu Linux 18.04 LTS on x64 hardware with unixODBC 2.3.7 and the version 17 ODBC drivers from Microsoft.

Thanks,

Cliff