How to set SQL_BIG_SELECTS = 1 from VB(legacy ASP) with ADODB environment?
Posted
by
conecon
on Stack Overflow
See other posts from Stack Overflow
or by conecon
Published on 2012-09-23T14:43:57Z
Indexed on
2012/09/23
15:37 UTC
Read the original article
Hit count: 386
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
© Stack Overflow or respective owner