accursed ODBC error

[niallser]

hi Alex,

I've seen this error in the forum before but cannot find a solution to it.

When running a SQL statement with an ODBC Query Test the following error is reported:

Error 4294967295: [Microsoft][ODBC SQL Server Driver]Prepared statement is not a cursor-specification

In your response to uncleude in "Getting error when executing stored procedure with ODBC Quer" you asked if the error occurred when the "Alert when field in row n column n" option is ticked.

In this case the error occurs only when this option is ticked.
When unticked the response when Test is clicked is "Query Executed - Ok" so we know that the query code is working.

However the code is returning a value when tested in SQL Analyzer and the alert is useless if we cannot have row1 col1 tested.

The query is using declared variables to construct a string from which date records can be converted into a measurable integer value.

We're using HostMonitor V 5.7 on W2K3 server

I hope this is clear, if not let me know and I'll try to make it clearer.

Many thanks,
Niall

[niallser]

Hi All,

It looks like it's my understanding of how Host Monitor works.

To get this query to work I had to remove all the carriage returns. HM does this as well but it isn't clever enough to put a space in place of the CR it has removed and so some lines get concatenated and cease to have a meaning as far as SQL is concerned - hence the error.
(remove the spaces in the above statement to get a feeling for the issue)

Separating all the statements that have had their CR removed or alternatively, putting a space at the start of each line before copying the code into the edit box, clears this problem.

If anyone has put anytime into this it is appreciated.

Churs,
Niall

[KS-Soft]

To get this query to work I had to remove all the carriage returns. HM does this as well but it isn't clever enough to put a space in place of the CR it has removed and so some lines get concatenated and cease to have a meaning as far as SQL is concerned - hence the error.

This problem was fixed in version 5.34. I just checked code - HostMonitor 5.70 replaces CRLF with single space.
H'm... probably your query contains single CR or LF symbols instead of CRLF? But that is impossible unless.. you have copied (&paste) query from some source? Have you types SQL query or you have used some strange application?

Regards
Alex

[niallser]

Nothing too strange Alex - the code was generated by a colleague and passed to me as a notepad attachment within an email.

initally I just copied and pasted the text straight from notepad and frustration ensued...

The code was then copied into SQL Analyzer for testing as we started chucking away the bits we thought were unneccessary - comments etc and then copied across to HostMonitor to see if we had a cure.

I guess the notepad text had the single CRs

Cheers,
niall

[KS-Soft]

No, notepad uses CRLF...

[Yoorix]

On Winodws systems is used CRLF, but on Linux and other *nix systems it should be only LF.

What mail client do you use? Is it Mozilla Thunderbird or other alternative mail client, isn't it?

Could you share with us this SQL statement to investigate the problem?

[KS-Soft]

Its not a big problem, we can translate single CR into space as well...

Regards
Alex

[niallser]

I guess there's no harm in it.
The code is designed to find the oldest item in a queue so the test can measure its age in minutes and alert us if something has been hanging aroung for too long.
Most of the code is translating the data from the time_stamp field from char to datetime.

I've removed the comment lines for brevity but left in the CRs to make it easier to read

DECLARE @@QUEUE_NAME varchar(80)
DECLARE @@TIMEOLD varchar(50)
DECLARE @@TIMENEW varchar(50)
DECLARE @@STR1 varchar(20)
DECLARE @@STR2 varchar(20)
DECLARE @@STR3 varchar(20)
DECLARE @@TOTALMIN Varchar(20)

SELECT @@QUEUE_NAME = 'PROCESSING QUEUE'

SELECT @@TIMEOLD = (SELECT TOP 1 TIME_STAMP FROM QUEUES WHERE QUEUE = @@QUEUE_NAME)

SELECT @@STR1 = SUBSTRING(@@TIMEOLD,1,10)

SELECT @@STR2 = (SELECT SUBSTRING (@@TIMEOLD,12,8))

SELECT @@STR2 = REPLACE (@@STR2, '.', ':')

SELECT @@STR3 = (SELECT SUBSTRING(@@TIMEOLD,20,3))

SELECT @@TIMENEW = @@STR1 + ' ' + @@STR2 + @@STR3

SELECT @@TIMENEW = (SELECT CAST (@@TIMENEW as DATETIME(20)))

SELECT @@TOTALMIN = DATEDIFF(mi, @@TIMENEW, CURRENT_TIMESTAMP)

SELECT @@TOTALMIN

This is essentially what was oiginally copied in to HostMonitor.

Below is what Host Monitor did to it and this resulted in the original error:

DECLARE @@QUEUE_NAME varchar(80) DECLARE @@TIMEOLD varchar(50) DECLARE @@TIMENEW varchar(50) DECLARE @@STR1 varchar(20) DECLARE @@STR2 varchar(20) DECLARE @@STR3 varchar(20) DECLARE @@TOTALMIN Varchar(20) SELECT @@QUEUE_NAME = 'FAX PROCESSING' SELECT @@TIMEOLD = (SELECT TOP 1 TIME_STAMP FROM QUEUES WHERE QUEUE = @@QUEUE_NAME) SELECT @@STR1 = SUBSTRING(@@TIMEOLD,1,10) SELECT @@STR2 = (SELECT SUBSTRING (@@TIMEOLD,12,8)) SELECT @@STR2 = REPLACE (@@STR2, '.', ':') SELECT @@STR3 = (SELECT SUBSTRING(@@TIMEOLD,20,3)) SELECT @@TIMENEW = @@STR1 + ' ' + @@STR2 + @@STR3 SELECT @@TIMENEW = (SELECT CAST (@@TIMENEW as DATETIME(20))) SELECT @@TOTALMIN = DATEDIFF(mi, @@TIMENEW, CURRENT_TIMESTAMP) SELECT @@TOTALMIN

Don't look too closely - the above won't generate the error now...

Now, having said all that I do have to put my hands up and provide some extra clarification.

The Version which is licensed and on the live server is 4.60 - the 5.7 version is my evaluation copy which is performing as it should.

I believe the errors I saw yesterday were due to my trying to fix the code manually and removed one too many spaces.

Addionally the code was transfered to the live server via encrypted ftp.

As far as trying to reproduce the error again... feckin' Heisenberg and his
bleedin' observation rules have intervened.

As for error code numbers that are the products of Fermi primes... it's all too much really

it'll be half dead fluxuated cats next

Churs,
niall