JeremyGiberson
New Member
I am posting to find out if any one has heard of an sql statement crashing the SQL broker.
Today, I was trying to perform a select statement with a nested query, when I executed the the SQL statement odbc errored with a "Server not reachable or possible RPC error". We were unable to get the broker to accept any more queries after that until we restart the broker.
I did a few tests to make sure it was the query that was causing this error and not just some random coincidence that the error occured when I attempted the query. WE have four brokers, two for SQL , two for 4GL. One of each broker for live, and for test database. I did my following tests on the SQL dedicated test db broker.
First I did a normal select
Result: Part record for 1221-0011R
Then, I attempted my nested query
Result: ODBC ERROR: Server not reachable or possible RPC error
Then, I attempted the normal select once again, only to get the same error. I closed my connection to the broker, opened a new one and was once again able to do a normal select but when I tried the nested select I got the error again.
Even though the broker (for the test db) was responsive once I closed the connection and opened it back up, this morning when we experienced the problem on the live broker, we were unable to get the broker to respond even on new connections and had to do a quick restart of the database (wich auto shuts down and auto starts the brokers).
Do any of you have any ideas why this might have occured? As a work around, we are just performing the inner select, and doing the amtdue comparison in program code with a while loop. But this method really erks me as it *could* be done in one query instead of being broken up into a database call and programming code.
Oh, we use:
ODBC DRIVERS: DATADIRECT 4.10 32-bit Progress SQL92 v9.1E
Progress version 9.1E
Today, I was trying to perform a select statement with a nested query, when I executed the the SQL statement odbc errored with a "Server not reachable or possible RPC error". We were unable to get the broker to accept any more queries after that until we restart the broker.
I did a few tests to make sure it was the query that was causing this error and not just some random coincidence that the error occured when I attempted the query. WE have four brokers, two for SQL , two for 4GL. One of each broker for live, and for test database. I did my following tests on the SQL dedicated test db broker.
First I did a normal select
Code:
SELECT * FROM pub.part WHERE "part-num" = '1221-0011R'
Then, I attempted my nested query
Code:
SELECT * FROM
(SELECT MAX("ar-seq"), MIN("ar-amtdue"), "inv-num" FROM pub.acctrec WHERE "cust-num" = '64047' GROUP BY "inv-num") as temprec
WHERE "ar-amtdue" > 0
Then, I attempted the normal select once again, only to get the same error. I closed my connection to the broker, opened a new one and was once again able to do a normal select but when I tried the nested select I got the error again.
Even though the broker (for the test db) was responsive once I closed the connection and opened it back up, this morning when we experienced the problem on the live broker, we were unable to get the broker to respond even on new connections and had to do a quick restart of the database (wich auto shuts down and auto starts the brokers).
Do any of you have any ideas why this might have occured? As a work around, we are just performing the inner select, and doing the amtdue comparison in program code with a while loop. But this method really erks me as it *could* be done in one query instead of being broken up into a database call and programming code.
Oh, we use:
ODBC DRIVERS: DATADIRECT 4.10 32-bit Progress SQL92 v9.1E
Progress version 9.1E