How to set SQL_BIG_SELECTS = 1 from VB(legacy ASP) with ADODB environment?
- by conecon
I encountered
The SELECT would examine more than MAX_JOIN_SIZE rows; check your
WHERE and use SET SQL_BIG_SELECTS=1 or SET SQL_MAX_JOIN_SIZE=# if the
SELECT is okay
error with my ASP code.
ASP code has server side ADODB connection with MySQL and connection seems not be able to execute multiple query. How to implement SQL_BIG_SELECTS = 1 in my code?
Set obj_db = Server.CreateObject("ADODB.Connection")
Session("ConnectionString") = "dsn=dsn1016189_mysql;uid=apns;pwd=mypassword;DATABASE=mydb;APP=ASP Script;STMT=SET CHARACTER SET SJIS"
obj_db.Open Session("ConnectionString")
Set obj_ret = Server.CreateObject("ADODB.Recordset")
obj_ret.CursorLocation = 3
and executing SQL...
SQL_BIG_SELECTS = 1; SELECT pu.login_id, pu.p_login_id, pu.first_name, pu.last_name, pu.sex, pu.is_admin, pu.attendance, pu.invited, pu.reason, qaa1.answer AS qaa1_answer, COUNT(pu2.p_login_id) AS companion
FROM party_user pu
LEFT OUTER JOIN party_user pu2
ON pu2.p_login_id = pu.login_id
LEFT OUTER JOIN qa_answer qaa1
ON qaa1.login_id = pu.login_id AND qaa1.party_id = pu.party_id AND qaa1.sort_num = '1' WHERE pu.party_id = '92' AND pu.p_login_id = '' GROUP BY pu.login_id, pu.p_login_id, pu.first_name, pu.last_name, pu.sex, pu.is_admin, pu.attendance, pu.reason, qaa1.answer, pu.invited ORDER BY pu.login_id ASC;
I can't execute multiple query and above query become error.
You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT pu.login_id, pu.p_login_id, pu.first_name, pu.last_name, pu.sex, pu.is_ad' at line 1
