ODBC Query- Prepared statement is not a cursor specification

cgrieves · Post by **cgrieves** » Mon Jul 02, 2012 8:26 am

Hi, I'm trying to get HM to run a daily script against a SQL server and email the results. However when I try running it from HM is comes back with the error "Prepared statement is not a cursor specification". Host machine has the latest MDAC, and the query works fine in query analyzer.

HM seems to mangle the script beyond recognition, removes all carriage returns, this is what I enter:

use irs
CREATE TABLE #tmp_references(bookingid int, response_time datetime, booking_conformation_pos int, booking_reference_number char(15), booking_details_pos int, booking_reference_number_details char(255))
CREATE TABLE #tmp_final_references(bookingid int, responetime datetime, booking_reference_number char(255))
INSERT INTO #tmp_references
SELECT BOOKREQUESTID,
(RESPONSEDATETIME - REQUESTDATETIME) as Responsetime,
(PATINDEX('%<BOOKING_REFERENCE>%', Response)+19) as BookingConformation,LTRIM(Substring(Response,(PATINDEX('%<BOOKING_REFERENCE>%', Response)+19),12)) as BookingReference, (PATINDEX('%<BOOKING_REFERENCE_NO%', Response)+22) as BookingDetails, LTRIM(Substring(Response,(PATINDEX('%<BOOKING_REFERENCE_NO%', Response)+22),12)) as BookingReference2
FROM dbo.BOOK_CANCEL_REQUESTS
WITH (NOLOCK)
WHERE REQUESTDATETIME > GETDATE()-1
AND LICENSEKEY != 'XXXXXXXX'
AND RequestType = 1
AND (RESPONSEDATETIME - REQUESTDATETIME) > '1900-01-01 00:00:50.000'
INSERT INTO #tmp_final_references
SELECT bookingid,
response_time,
Substring(booking_reference_number,(PATINDEX('%J%', booking_reference_number)),11)
FROM #tmp_references
WHERE booking_conformation_pos > 150
INSERT INTO #tmp_final_references
SELECT bookingid,
response_time,
Substring(booking_reference_number_details,(PATINDEX('%J%', booking_reference_number_details)),11)
FROM #tmp_references
WHERE booking_details_pos > 150
select rtrim(b.bookingreferencenumber) Ref,
rtrim(c.clientname)Client,
b.bookingstartdate,
tfr.responetime,
rtrim(bs.bookingstatusname) Status,
b.bookingdatetimestamp,
tfr.bookingid
from booking b
with (nolock)
join #tmp_final_references tfr on b.BOOKINGREFERENCENUMBER = tfr.booking_reference_number collate SQL_Latin1_General_CP1_CI_AS
left join client c on b.clientid = c.clientid
left join booking_status bs on b.bookingstatusid = bs.bookingstatusid
order by 2
drop table #tmp_references
drop table #tmp_final_references

This is what Hostmonitor saves it as:

use irs CREATE TABLE #tmp_references(bookingid int, response_time datetime, booking_conformation_pos int, booking_reference_number char(15), booking_details_pos int, booking_reference_number_details char(255)) CREATE TABLE #tmp_final_references(bookingid int, responetime datetime, booking_reference_number char(255)) INSERT INTO #tmp_references SELECT BOOKREQUESTID, (RESPONSEDATETIME - REQUESTDATETIME) as Responsetime, (PATINDEX('%<BOOKING_REFERENCE>%', Response)+19) as BookingConformation,LTRIM(Substring(Response,(PATINDEX('%<BOOKING_REFERENCE>%', Response)+19),12)) as BookingReference, (PATINDEX('%<BOOKING_REFERENCE_NO%', Response)+22) as BookingDetails, LTRIM(Substring(Response,(PATINDEX('%<BOOKING_REFERENCE_NO%', Response)+22),12)) as BookingReference2 FROM dbo.BOOK_CANCEL_REQUESTS WITH (NOLOCK) WHERE REQUESTDATETIME > GETDATE()-1 AND LICENSEKEY != 'XXXXXXXX' AND RequestType = 1 AND (RESPONSEDATETIME - REQUESTDATETIME) > '1900-01-01 00:00:50.000' INSERT INTO #tmp_final_references SELECT bookingid, response_time, Substring(booking_reference_number,(PATINDEX('%J%', booking_reference_number)),11) FROM #tmp_references WHERE booking_conformation_pos > 150 INSERT INTO #tmp_final_references SELECT bookingid, response_time, Substring(booking_reference_number_details,(PATINDEX('%J%', booking_reference_number_details)),11) FROM #tmp_references WHERE booking_details_pos > 150 select rtrim(b.bookingreferencenumber) Ref, rtrim(c.clientname)Client, b.bookingstartdate, tfr.responetime, rtrim(bs.bookingstatusname) Status, b.bookingdatetimestamp, tfr.bookingid from booking b with (nolock) join #tmp_final_references tfr on b.BOOKINGREFERENCENUMBER = tfr.booking_reference_number collate SQL_Latin1_General_CP1_CI_AS left join client c on b.clientid = c.clientid left join booking_status bs on b.bookingstatusid = bs.bookingstatusid order by 2 drop table #tmp_references drop table #tmp_final_references

Any idea how I can get round this? I've removed all comments, tried a space at the beginning of every line, removed tab characters, tried different carriage return positions... nothing seems to work. Is there a way to stop HM changing the script?

KS-Soft · Post by **KS-Soft** » Mon Jul 02, 2012 8:59 am

HostMonitor is not designed to execute scripts.
You should create stored procedure and use HostMonitor to call it.

Also, what means "email the results"? ODBC Query test checks single field and test may pass single value to the action.

Regards
Alex

cgrieves · Post by **cgrieves** » Mon Jul 02, 2012 9:54 am

OK, fair enough (though I couldn't spot that limitation in the documentation). I created an SP with the same script, so now I'm calling:

use irs; exec failedbookings1;

From HM.

Again, it runs fine through query analyzer, but still gives the same error:

"Prepared statement is not a cursor definition"

The only other relevant resource I can find related to this error mentions MDAC versions. The HM host has the latest MDAC installed... Anything else I can try?

KS-Soft · Post by **KS-Soft** » Mon Jul 02, 2012 12:09 pm

though I couldn't spot that limitation in the documentation

That's HostMonitor basis - every test returns some status (Ok, Host is alive, Bad, Unknown, etc) and Reply string. That's it.
Test item does not store entire ODBC table or contents of some log file within HostMonitor data.
If you want to use "Send e-mail" action to send some external data, such data should be stored in the file (so you may use "Attach file" option).

"Prepared statement is not a cursor definition"

This is not HostMonitor error message... probably error caused by several SELECT statement in one ODBC call.
You may add SET NOCOUNT ON statement to suppress unnecessary results (you are using MS SQL server, right?).

What exactly field has to be checked by HostMonitor? Probably you do not want to check anything? Just start script from time to time?

Regards
Alex

cgrieves · Post by **cgrieves** » Mon Jul 09, 2012 5:41 am

When the script runs if it returns anything at all, I want HM to generate an alert. Preferably with the script reply included.

No matter, we're working on getting it running via cognos instead.

KS-Soft Europe · Post by **KS-Soft Europe** » Mon Jul 09, 2012 10:05 am

Have you tried to use SET NOCOUNT ON statement ?

When the script runs if it returns anything at all, I want HM to generate an alert. Preferably with the script reply included.

In such case, you should specify alert condition like:
Alert when field in 1 row 1 col
is <> ""
If field is unavailable, set status OK

You may use %Reply% macro variable with action profile to show first bookingid returned by the script.