PDOException: SQLSTATE[07002]: [Microsoft][SQL Server Native Client 10.0]COUNT field incorrect or syntax error

I don't see a sql problem there, let's move to the sqlserver driver.

Maybe simply post a view, so people can reproduce the problem really.

Hi,
I don't know if in your case the GREATEST function is the one to blame, but I've also managed to get the same PDOException and the concerned query definitely only contains standard SQL.

The problem occurred when using Search API with Database search backend. Entering a single search term worked, while entering two or more terms lead me to this exception.

When testing the query directly against the database, it returned a correct result. So the problem must be situated anywhere in the DB driver area - either on Drupal or on PHP side.

I can show you the difference between the two queries - with already replaced placeholders and I've also dropped out the {} and [] parenthesis because I was also testing the queries against a MySQL db using phpmyadmin console and saved the cleaned up queries:

Query with one search term (works):

SELECT COUNT(*) AS expression
FROM 
	(SELECT 1 AS expression, t.item_id AS _field_0
		FROM 
			(SELECT t.*
				FROM search_api_db_search_index_title t
				WHERE (  (word = 'notebook')  ) UNION ALL SELECT t.*
					FROM search_api_db_search_index_body_value t
					WHERE (  (word = 'notebook')  )
			) t
			INNER JOIN search_api_db_search_index_status t_2 ON t.item_id = t_2.item_id
			INNER JOIN search_api_db_search_index_search_api_language t_3 ON t.item_id = t_3.item_id
		WHERE ( (( (t_2.value = '1') AND( (t_3.value = 'de') OR (t_3.value = 'und') ))) )
		GROUP BY t.item_id
	) subquery

And here the error-producing query with two search terms:

SELECT COUNT(*) AS expression
FROM 
	(SELECT t.item_id AS item_id, SUM(t.score) AS score, 1 AS expression, t.item_id AS _field_0
		FROM 
			(SELECT t.*
				FROM search_api_db_search_index_title t
				WHERE ( ( (word = 'notebook') OR (word = 'mdata') ) ) UNION ALL SELECT t.*
					FROM search_api_db_search_index_body_value t
					WHERE ( ( (word = 'notebook') OR (word = 'mdata') ) )
			) t
			INNER JOIN search_api_db_search_index_status t_2 ON t.item_id = t_2.item_id
			INNER JOIN search_api_db_search_index_search_api_language t_3 ON t.item_id = t_3.item_id
		WHERE ( (( (t_2.value = '1') AND( (t_3.value = 'de') OR (t_3.value = 'und') ))) )
		GROUP BY t.item_id
		HAVING  (COUNT(DISTINCT t.word) >= 2) 
	) subquery

You can see all the differences marked in bold, here the details:
edit: sorry, no bold marking within the code tags...

1. the HAVING clause
2. the additional columns in the SELECT: t.item_id AS item_id, SUM(t.score) AS score,
3. last but not least the 2 different WHERE conditions: naturally because we have two terms and they are concatenated with an OR + there is an additional parenthesis around the OR: and as crazy as this might sound, I've found a similar bug report for a Sqlite instance (I'm using MS SQL in contrast), where the additional parenthesis were guilty: http://drupal.org/node/1516830

The HAVING clause was the most obvious difference, but removing this had no consequences, the same with the additional columns in SELECT. --> so the troublemaking part was the WHERE condition with the OR in it.

I was rewriting the part inside the DB Search module that was generating the OR part into making an IN condition instead. And this really worked for me!!!

But the reason why I succeeded was that this is just shipping around this very strange bug, that may be have to do something with the parenthesis!??!???!???

I'm writing this comment to inform that there's some very strange bug out there (in the sqlsrv driver???) that fires under certain circumstances and I hope that this can help other people having the same problem.

Finally some words about my configuration:

• IIS on Windows NT SERVER34 6.0 build 6002
• PHP 5.2.11
• unsure about which sqlsrv version

hi agoradesign,

i'm getting the same error today (25.03.2013). Latest dev sqlsrv driver and adv. forum module.

So, is there an update now or could you please explain your solution?

My configuration:
Microsoft-IIS/7.5
PHP 5.4.12
Latest dev sqlsrv version

Hi thorsten,
luckily for myself, I wasn't responsible for anything outside of Drupal on the webserver. This was part of the client's IT department. After lots of hours trying, testing and debugging, they finally set up a test environment on a completely different server, with different setup - a newer SQL Server as well as switching to PHP 5.4, and the problems were gone.

So unfortunately I can't provide you with any hints...

Also see here #1598924: Query with duplicate placeholders throws PDO Exception

About parenthesis causing errors:

I found today (after running the apparently fine queries and seeing what was sent to the db via SQL Server Profiler) that yes, there IS a problem with sub-select statements involving parenthesis.

Mine showed up because there was a "... (SELECT ... FOR XML PATH(''))..." portion. It worked when I changed it to "... ( SELECT ..." Notice that by adding the space between the SELECT and the '(' the problem was fixed HACKED to work correctly.

It appears that some portion of the code is stripping off leading parenthesis from "(SELECT" and converting it to "SELECT". I haven't had the time to go chase that down, yet, but whoever/whatever it is needs to be fixed. This is in either Drupal core or in sqlsvr driver code.

The problem is that replacement parameters are getting out of order when this is done.

This is a duplicate of #1598924: Query with duplicate placeholders throws PDO Exception.