SQL query does not run via host monitor

unclelude · Post by **unclelude** » Fri Feb 14, 2003 8:16 am

Hello,

I am trying to run the following SQL, but it gives me an error. The code runs fine via SQL Query Analyzer. This is pure SQL Server code. Do I have to follow a certain syntax for ODBC ? If yes, then this puts a huge, limit on what we can do with host monitor.

SELECT 'mobitex' as protcol, datediff(mi, MAX(dateadd(hh, -5, tblWooStatusLog.wslInsertedWhen )), getdate() )as MinutesPast , MAX(dateadd(hh, -5, tblWooStatusLog.wslInsertedWhen ) ) LastComm, getdate() as CurrentDate -- Status = case datediff(mi, MAX(dateadd(hh, -5, vewWooStatusLog.wslInsertedWhen )), getdate() )FROM FX_PRD05..tblWooStatusLog (NOLOCK) INNER JOIN FX_PRD05..vewEmpModem (NOLOCK) ON FX_PRD05..tblWooStatusLog.wsl_EmpIDFk = vewEmpModem.emmOwnerEmployee_EmpIDFKwhere emmWirelessType_EwnIDFK = (-100) and tblWooStatusLog.wslWooStatus_WosIDFk NOT IN (-100, -200, -400, -1300)

Thanks.

KS-Soft · Post by **KS-Soft** » Fri Feb 14, 2003 12:04 pm

HostMonitor does not have any limitations for SQL Query, it just sends query to ODBC driver.
What exactly error do you get?

Regards
Alex

unclelude · Post by **unclelude** » Fri Feb 14, 2003 6:56 pm

Hi Alex,

I get the following error in a dialog box:

Error 4294967295 [Microsoft][ODBC Driver Manager] Function Sequence Error

I don't even know how to test my code for ODBC compliance or how to test my SQL so that it can work in Host Monitor. Any ideas. I really like Host Monitor, but this has put a huge limit on what we can do.

Thanks,
Adnan

KS-Soft · Post by **KS-Soft** » Sat Feb 15, 2003 7:01 pm

Do you use MS SQL server? What version?
I found information about bug in Microsoft SQL Server 6.5, and Microsoft Open Database Connectivity 3.0.
http://support.microsoft.com/default.as ... s%3B175966

Regards
Alex

unclelude · Post by **unclelude** » Sun Feb 16, 2003 9:48 am

Hi Alex,

I am using SQL 7 & SQL 2000. I also have SQL 2000 installed on my desktop.

As per my previous question, how can I test my SQL for ODBC compliance ? any tools or utilities. I have only worked in the SQL Enterprise and SQL Query Analyzer environment.

KS-Soft · Post by **KS-Soft** » Mon Feb 17, 2003 4:11 pm

>As per my previous question, how can I test my SQL for ODBC compliance ? any tools or utilities.

We don't have SQL tools. I think you can find a lot of utilities in the Internet. For example here http://www.tucows.com/business/sql95.html

Regards
Alex

KS-Soft · Post by **KS-Soft** » Mon Feb 17, 2003 4:16 pm

BTW Can you execute some very simple SQL Query using HostMonitor? If it will be executed, error in your SQL Query. If even command like "select * from mytable" will not be executed, error in ODBC driver or in HostMonitor.

Regards
Alex

Guest · Post by **Guest** » Mon Feb 17, 2003 9:15 pm

Hi Alex,

I tried a simple SQL, SELECT count(*) from tblModem, and it worked fine. I think the problem is between NATIVE SQL code and ODBC code. The SQL I am having problems with, works fine in SQL Query Analyzer, but it won't work in HostMonitor because HM uses ODBC. I will have to test my SQL for ODBC so that it can work with HM, else, I am stuck pretty badly here.

Don't know what to do..

unclelude · Post by **unclelude** » Mon Feb 17, 2003 9:15 pm

Hi Alex,

I tried a simple SQL, SELECT count(*) from tblModem, and it worked fine. I think the problem is between NATIVE SQL code and ODBC code. The SQL I am having problems with, works fine in SQL Query Analyzer, but it won't work in HostMonitor because HM uses ODBC. I will have to test my SQL for ODBC so that it can work with HM, else, I am stuck pretty badly here.

Don't know what to do..

unclelude · Post by **unclelude** » Mon Feb 17, 2003 9:27 pm

Alex,

I just tested my SQL with two downloaded ODBC Query Tools, Query Tool (ODBC) 3.5.7.9; and SQL Query Tool (ODBC) 4.1. It worked in both of them, so now I am thinking may be something in HostMonitor is not configured properly for ODBC ?

KS-Soft · Post by **KS-Soft** » Mon Feb 17, 2003 10:28 pm

Could you please tell me what options do you use? Do you use "Alert when field x:y...." option? If you use this option, could you try to disable it and click "Test" button? Will error appear? If error gone, try to enable option back and set "Do not use SQLFetchAbsolute command" option (Miscellaneous page in the Options dialog). Do you see error now?

Regards
Alex

unclelude · Post by **unclelude** » Tue Feb 18, 2003 8:46 am

Hello Alex,

I have tried both tests and they both pretty much give the same error number, though with different text. Here are the details. I tried the following SQL
SELECT 'norcomq' as protcol,
datediff(mi, MAX(dateadd(hh, -5, tblWooStatusLog.wslInsertedWhen ) ), getdate() )as MinutesPast ,
MAX(dateadd(hh, -5, tblWooStatusLog.wslInsertedWhen ) ) LastComm,
getdate() as CurrentDate
FROM FX_PRD05..tblWooStatusLog (NOLOCK) INNER JOIN
FX_PRD05..vewEmpModem (NOLOCK) ON
FX_PRD05..tblWooStatusLog.wsl_EmpIDFk = vewEmpModem.emmOwnerEmployee_EmpIDFK
where emmWirelessType_EwnIDFK = -108
and tblWooStatusLog.wslWooStatus_WosIDFk NOT IN (-100, -200, -400, -1300)

First, I tried without the "Alert when field in x, y" enabled and got the following error:
Error 4294967295:[Microsoft][ODBC SQL Server Driver]Line 1:Incorrect Syntax near 'emmWirelessType_EwnIDFK'

Then I tred with the "Alert when field in x, y". I got the following error:
Error 4294967295:[Microsoft][ODBC Driver Manager] Function sequence error

As I had mentioned before, the above SQL works fine in other ODBC applications.

I also could not find the "Do not use SQLFetchAbsolute command" option (Miscellaneous page in the Options dialog) in HostMonitor ?

KS-Soft · Post by **KS-Soft** » Tue Feb 18, 2003 1:08 pm

Ok, one problem is fixed. HostMonitor could display wrong error message. There is update at http://www.ks-soft.net/download.hm366.zip
If you use some old version, update to HM v 3.64 first.

>I also could not find the "Do not use SQLFetchAbsolute command" option (Miscellaneous page in the Options dialog) in HostMonitor ?

What version of HM do you use?

>Line 1:Incorrect Syntax near 'emmWirelessType_EwnIDFK'

I checked code, HM does not have any limits for lenght of the SQL Query. But may be ODBC driver has. Could you try to execute query like
SELECT 'norcomq' as protcol, FROM FX_PRD05..tblWooStatusLog (NOLOCK) INNER JOIN
FX_PRD05..vewEmpModem (NOLOCK) ON
FX_PRD05..tblWooStatusLog.wsl_EmpIDFk = vewEmpModem.emmOwnerEmployee_EmpIDFK
where emmWirelessType_EwnIDFK = -108
and tblWooStatusLog.wslWooStatus_WosIDFk NOT IN (-100, -200, -400, -1300)
?

Regards
Alex

unclelude · Post by **unclelude** » Tue Feb 18, 2003 1:48 pm

Hi Alex,

1. The link , http://www.ks-soft.net/download.hm366.zip does not work.

2. What version of HM do you use?
3.56

3. The following SQL creates the cartesian product.

SELECT 'norcomq' as protcol, FROM FX_PRD05..tblWooStatusLog (NOLOCK) INNER JOIN
FX_PRD05..vewEmpModem (NOLOCK) ON
FX_PRD05..tblWooStatusLog.wsl_EmpIDFk = vewEmpModem.emmOwnerEmployee_EmpIDFK
where emmWirelessType_EwnIDFK = -108
and tblWooStatusLog.wslWooStatus_WosIDFk NOT IN (-100, -200, -400, -1300)

If the idea is to run a shorter version, then I created the following SQL and it still erred out.

SELECT
datediff(mi, MAX(dateadd(hh, -5, tblWooStatusLog.wslInsertedWhen ) ), getdate() )as MinutesPast
FROM FX_PRD05..tblWooStatusLog (NOLOCK) INNER JOIN
FX_PRD05..vewEmpModem (NOLOCK) ON

FX_PRD05..tblWooStatusLog.wsl_EmpIDFk = vewEmpModem.emmOwnerEmployee_EmpIDFK
where emmWirelessType_EwnIDFK = -108
and tblWooStatusLog.wslWooStatus_WosIDFk NOT IN (-100, -200, -400, -1300)

Thanks,

Adnan

KS-Soft · Post by **KS-Soft** » Tue Feb 18, 2003 1:56 pm

>The link , http://www.ks-soft.net/download.hm366.zip does not work.

Sorry, I missed slash. Correct link is http://www.ks-soft.net/download/hm366.zip

>If the idea is to run a shorter version, then I created the following SQL and it still erred out.

Yes, idea was to run a shorter query. So, it still display "Incorrect Syntax near 'emmWirelessType_EwnIDFK'"? H'm....