Überwachungregel - ODBCSQL

Why supplies SELECT productsn from product where productsn=1 always ok although the column PRODUCTSN after the SELECT inquiry in each case 1 to actually be can???



ODBC Connection string:



Driver={Microsoft ODBC for Oracle};Server=k54;Uid=hvb_stage_v50;Pwd=jcatalog



SQL Statement:



select productsn from product where productsn=1



Field Name:



productsn



Generate an alert when the returned value is : less than 1



Last it possible the problem to solve?



Thank you



ODBC trace:



test 33c-42c ENTER SQLAllocEnv

HENV * 01B07E08



test 33c-42c EXIT SQLAllocEnv with return code 0 (SQL_SUCCESS)

HENV * 0x01B07E08 ( 0x01b31540)



test 33c-42c ENTER SQLAllocConnect

HENV 01B31540

HDBC * 00AAEDF8



test 33c-42c EXIT SQLAllocConnect with return code 0 (SQL_SUCCESS)

HENV 01B31540

HDBC * 0x00AAEDF8 ( 0x01b315e8)



test 33c-42c ENTER SQLDriverConnectW

HDBC 01B315E8

HWND 00000000

WCHAR * 0x745D9A38 [ -3] "****** 0"

SWORD -3

WCHAR * 0x745D9A38

SWORD 2

SWORD * 0x00000000

UWORD 0 <SQL_DRIVER_NOPROMPT>



test 33c-42c EXIT SQLDriverConnectW with return code 0 (SQL_SUCCESS)

HDBC 01B315E8

HWND 00000000

WCHAR * 0x745D9A38 [ -3] "****** 0"

SWORD -3

WCHAR * 0x745D9A38

SWORD 2

SWORD * 0x00000000

UWORD 0 <SQL_DRIVER_NOPROMPT>



test 33c-42c ENTER SQLAllocStmt

HDBC 01B315E8

HSTMT * 00AAD968



test 33c-42c EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)

HDBC 01B315E8

HSTMT * 0x00AAD968 ( 0x01b32c28)



test 33c-42c ENTER SQLFreeStmt

HSTMT 01B32C28

UWORD 1 <SQL_DROP>



test 33c-42c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1 <SQL_DROP>



test 33c-42c ENTER SQLAllocStmt

HDBC 01B315E8

HSTMT * 00AAD968



test 33c-42c EXIT SQLAllocStmt with return code 0 (SQL_SUCCESS)

HDBC 01B315E8

HSTMT * 0x00AAD968 ( 0x01b32c28)



test 33c-42c ENTER SQLExecDirect

HSTMT 01B32C28

UCHAR * 0x01F9AB74 [ 47] "select productsn from product where productsn=1"

SDWORD 47



test 33c-42c EXIT SQLExecDirect with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UCHAR * 0x01F9AB74 [ 47] "select productsn from product where productsn=1"

SDWORD 47



test 33c-42c ENTER SQLGetStmtOption

HSTMT 01B32C28

UWORD 9

PTR 0x01F46F00



test 33c-42c EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 9

PTR 0x01F46F00



test 33c-42c ENTER SQLNumResultCols

HSTMT 01B32C28

SWORD * 0x01F46EFC



test 33c-42c EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

SWORD * 0x01F46EFC (1)



test 33c-42c ENTER SQLColAttributes

HSTMT 01B32C28

UWORD 1

UWORD 6 <SQL_COLUMN_DISPLAY_SIZE>

PTR 0x00000000

SWORD 0

SWORD * 0x00000000

SQLLEN * 0x01DBC1A4



test 33c-42c EXIT SQLColAttributes with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1

UWORD 6 <SQL_COLUMN_DISPLAY_SIZE>

PTR 0x00000000

SWORD 0

SWORD * 0x00000000

SQLLEN * 0x01DBC1A4 (12)



test 33c-42c ENTER SQLDescribeCol

HSTMT 01B32C28

UWORD 1

UCHAR * 0x00000000

SWORD 0

SWORD * 0x0012F716

SWORD * 0x0012F722

SQLULEN * 0x0012F6AC

SWORD * 0x0012F716

SWORD * 0x0012F716



test 33c-42c EXIT SQLDescribeCol with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1

UCHAR * 0x00000000

SWORD 0

SWORD * 0x0012F716 (9)

SWORD * 0x0012F722 (3)

SQLULEN * 0x0012F6AC (10)

SWORD * 0x0012F716 (9)

SWORD * 0x0012F716 (9)



test 33c-42c ENTER SQLBindCol

HSTMT 01B32C28

UWORD 1

SWORD 1 <SQL_C_CHAR>

PTR 0x01F15BD8

SQLLEN 13

SQLLEN * 0x016A626C



test 33c-42c EXIT SQLBindCol with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1

SWORD 1 <SQL_C_CHAR>

PTR 0x01F15BD8

SQLLEN 13

SQLLEN * 0x016A626C (0)



test 33c-42c ENTER SQLNumResultCols

HSTMT 01B32C28

SWORD * 0x00AAD980



test 33c-42c EXIT SQLNumResultCols with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

SWORD * 0x00AAD980 (1)



test 33c-42c ENTER SQLColAttributes

HSTMT 01B32C28

UWORD 1

UWORD 1 <SQL_COLUMN_NAME>

PTR 0x0012F730

SWORD 1024

SWORD * 0x0012FB30

SQLLEN * 0x00000000



test 33c-42c EXIT SQLColAttributes with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1

UWORD 1 <SQL_COLUMN_NAME>

PTR 0x0012F730

SWORD 1024

SWORD * 0x0012FB30 (9)

SQLLEN * 0x00000000



test 33c-42c ENTER SQLGetStmtOption

HSTMT 01B32C28

UWORD 9

PTR 0x0012FB34



test 33c-42c EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 9

PTR 0x0012FB34



test 33c-42c ENTER SQLExtendedFetch

HSTMT 01B32C28

UWORD 1 <SQL_FETCH_NEXT>

SQLLEN 1

SQLULEN * 0x0012FB38

UWORD * 0x016F6DB8



test 33c-42c EXIT SQLExtendedFetch with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1 <SQL_FETCH_NEXT>

SQLLEN 1

SQLULEN * 0x0012FB38 (1)

UWORD * 0x016F6DB8 (0)



test 33c-42c ENTER SQLGetStmtOption

HSTMT 01B32C28

UWORD 9

PTR 0x0012FB34



test 33c-42c EXIT SQLGetStmtOption with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 9

PTR 0x0012FB34



test 33c-42c ENTER SQLExtendedFetch

HSTMT 01B32C28

UWORD 1 <SQL_FETCH_NEXT>

SQLLEN 1

SQLULEN * 0x0012FB38

UWORD * 0x016F6DB8



test 33c-42c EXIT SQLExtendedFetch with return code 100 (SQL_NO_DATA_FOUND)

HSTMT 01B32C28

UWORD 1 <SQL_FETCH_NEXT>

SQLLEN 1

SQLULEN * 0x0012FB38

UWORD * 0x016F6DB8



test 33c-42c ENTER SQLErrorW

HENV 01B31540

HDBC 01B315E8

HSTMT 01B32C28

WCHAR * 0x0012FAD0 (NYI)

SDWORD * 0x00AAE1E8

WCHAR * 0x0012F6D0

SWORD 512

SWORD * 0x0012FB1C



test 33c-42c EXIT SQLErrorW with return code 100 (SQL_NO_DATA_FOUND)

HENV 01B31540

HDBC 01B315E8

HSTMT 01B32C28

WCHAR * 0x0012FAD0 (NYI)

SDWORD * 0x00AAE1E8

WCHAR * 0x0012F6D0

SWORD 512

SWORD * 0x0012FB1C



test 33c-42c ENTER SQLFreeStmt

HSTMT 01B32C28

UWORD 2 <SQL_UNBIND>



test 33c-42c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 2 <SQL_UNBIND>



test 33c-42c ENTER SQLFreeStmt

HSTMT 01B32C28

UWORD 1 <SQL_DROP>



test 33c-42c EXIT SQLFreeStmt with return code 0 (SQL_SUCCESS)

HSTMT 01B32C28

UWORD 1 <SQL_DROP>



test 33c-42c ENTER SQLDisconnect

HDBC 01B315E8



test 33c-42c EXIT SQLDisconnect with return code 0 (SQL_SUCCESS)

HDBC 01B315E8



test 33c-42c ENTER SQLFreeConnect

HDBC 00000000



test 33c-42c EXIT SQLFreeConnect with return code -2 (SQL_INVALID_HANDLE)

HDBC 00000000

Could you ask your question in German as I do not understand well what you exactly mean.

the sql query



SELECT productsn from product where productsn=1



generates in serverscheck enterprise downloaded version from yesterday as registered version from feb 2004 always



ok status.





but that ist not proper functionality, because the column PRODUCTSN after the SELECT query must be NULL or 1 but never



less than 1.



the settings in serverscheck are:



ODBC Connection string:



Driver={Microsoft ODBC for Oracle};Server=k54;Uid=hvb_stage_v50;Pwd=jcatalog



SQL Statement:



select productsn from product where productsn=1



Field Name:



productsn



Generate an alert when the returned value is : less than 1

OK but I understand your question.



You say that ServersCheck always returns OK on following SQL statement:

SELECT productsn from product where productsn=1



As far as I am with familiar with SQL, this will only return records where the value of productsn is 1 so the returned value will be never less than 1 because your where clause is =1

thank you for understanding.



to repeat:



(a) i get OK after testing to the db column productsn from serverscheck, if the result record set is empty



(b) i get OK after testing to the db column productsn from serverscheck, if the result record has productsn with value 1



(c) i get OK after testing to the db column productsn from serverscheck, if the result record has productsn with value others than 1 (sql query would be changed for that test to SELECT productsn from product where productsn>1



how can we get this feature running.



i have licenced the serverscheck enterprise last year in mai, in that version this feature didnt work, i tested now with the newest version without success.

Sorry but as stated in my last reply, your sql statement needs to be

SELECT productsn from product



and then apply the value condition and not put the where clause in it



The check is a value comparison condition check so:

a) will return OK because no value less than 1 found (NULL is not a value

b) will return OK because no records with value 1 found

c) will return OK because empty recordset so not records with a value less than one found

independent of the sql query i always get OK.



please give me an example of sql query where i get NOT OK.

I don't know your database (nature of the fields etc...) so it is hard to give an example of your database.



Image that you have a table with in there values of results from for example a temperature sensor (based upon the ServersCheck database) and that you want to alert if the last record contains a value higher than 30



SELECT TOP 1 CheckVal FROM SERVERSCHECK_DATA WHERE TYPE='TEMPERATURE'



It checks it for the last record found from the resultset (if there is more than 1 record in the resultset)

Output from test settings:



Testing Settings

--------------------------------------------------------------------------------

The check has been performed.



Status: DOWN?

Error returned: Error criteria reached: 35 (returned value) greater than 30 (threshold value)

Value returned: 35

as i see you use MS SQL Server. we have oracle 9.2i.



we test with



select productsn from product where productsn=1



we get as a result



1



so we test the column with the



value less than 1



and get OK from serverscheck.



i think, there must be some correlation with oracle server / oracle odbc driver



please help

I just tested it against an Oracle database and it works. (some sql statement as before but without TOP 1)



It then just takes the last record in the resultset to compare.



If you have a support agreement, then you can contact our tech support team through the online helpdesk. They can then connect to your database and see what is going on (you need to make your database then available to us through the Internet)