|
View previous topic :: View next topic |
Author |
Message |
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Fri Feb 14, 2003 8:16 am Post subject: |
|
|
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. |
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
Posted: Fri Feb 14, 2003 12:04 pm Post subject: |
|
|
HostMonitor does not have any limitations for SQL Query, it just sends query to ODBC driver.
What exactly error do you get?
Regards
Alex |
|
Back to top |
|
|
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Fri Feb 14, 2003 6:56 pm Post subject: |
|
|
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
|
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
|
Back to top |
|
|
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Sun Feb 16, 2003 9:48 am Post subject: |
|
|
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. |
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
Posted: Mon Feb 17, 2003 4:11 pm Post subject: |
|
|
>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 |
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
Posted: Mon Feb 17, 2003 4:16 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
Guest
|
Posted: Mon Feb 17, 2003 9:15 pm Post subject: |
|
|
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.. |
|
Back to top |
|
|
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Mon Feb 17, 2003 9:15 pm Post subject: |
|
|
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.. |
|
Back to top |
|
|
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Mon Feb 17, 2003 9:27 pm Post subject: |
|
|
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 ?
|
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
Posted: Mon Feb 17, 2003 10:28 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Tue Feb 18, 2003 8:46 am Post subject: |
|
|
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 ?
|
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
Posted: Tue Feb 18, 2003 1:08 pm Post subject: |
|
|
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 |
|
Back to top |
|
|
unclelude
Joined: 22 Jan 2003 Posts: 20
|
Posted: Tue Feb 18, 2003 1:48 pm Post subject: |
|
|
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
|
|
Back to top |
|
|
KS-Soft
Joined: 03 Apr 2002 Posts: 12818 Location: USA
|
Posted: Tue Feb 18, 2003 1:56 pm Post subject: |
|
|
>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.... |
|
Back to top |
|
|
|
|
You cannot post new topics in this forum You cannot reply to topics in this forum You cannot edit your posts in this forum You cannot delete your posts in this forum You cannot vote in polls in this forum
|
Powered by phpBB © 2001, 2005 phpBB Group
|