Database Monitor: Perform many SQL-queries. (For getting SQL Server errors)

[Dmitry_V]

Hi!

Can I put many SQL commands?

In this example, I encountered a problem.

Task: Get a last database error from internal MS SQL procedure.

1. I have a SQL-commands from Studio:





2. Then I join all string to one with ";" delimiter:

Declare @End Varchar(19) = (Select CONVERT(Varchar(19), GETDATE(), 120)); Declare @Start Varchar(19) = (Select CONVERT(Varchar(19), DATEADD(mi,-1,GETDATE()), 120)); Declare @Statement Nvarchar(100) = 'EXEC xp_readerrorlog 0, 1, N''Error'', null, '+'N'''+@Start+''''+','+'N'''+@End+''''; Declare @T Table (LogDate DateTime, ProcessInfo Char(64), Text Char(255)); Insert @T exec sp_executesql @Statement; SELECT ISNULL((Select TOP(1) Text from @T Where Text Not Like 'Error: 18456%'), 'No Error')

and place it in file test.sql

3. Test requests with isql:

$ isql DSN DB_USER DB_PASS < test.sql
+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
SQL> Declare @End Varchar(19) = (Select CONVERT(Varchar(19), GETDATE(), 120)); Declare @Start Varchar(19) = (Select CONVERT(Varchar(19), DATEADD(mi,-1,GETDATE()), 120)); Declare @Statement Nvarchar(100) = 'EXEC xp_readerrorlog 0, 1, N''Error'', null, '+'N'''+@Start+''''+','+'N'''+@End+''''; Declare @T Table (LogDate DateTime, ProcessInfo Char(64), Text Char(255)); Insert @T exec sp_executesql @Statement; SELECT ISNULL((Select TOP(1) Text from @T Where Text Not Like 'Error: 18456%'), 'No Error') As Row1
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Row1                                                                                                                                                                                                                                                           |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| No Error                                                                                                                                                                                                                                                       |
+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
SQLRowCount returns 0
1 rows fetched

It works with isql!

4. Create an item:



5. And i have:
- Status of item: Not supported
- Info: SQL query returned empty result

Zabbix server logs:
36615:20200207:104235.653 error reason for "HOST:db.odbc.select[error_message,{HOST.HOST}]" changed: Cannot execute ODBC query: [SQL_NO_DATA]
36601:20200207:104911.157 error reason for "HOST:db.odbc.select[error_message,{HOST.HOST}]" changed: SQL query returned empty result.
36608:20200207:110411.050 error reason for "HOST:db.odbc.select[error_message,{HOST.HOST}]" changed: Cannot execute ODBC query: [SQL_NO_DATA]
and so on

Help please!

[evgenys]

Try to set: 'set nocount on' before your query..

[lptarik]

I don't know why but i think only select queries work in zabbix

[angelsc]

Try to set: 'set nocount on' before your query..

Thanks a lot, this resolve a problem that i had with a query in mssql.