Python Pyodbc - Invalid Syntax with SQL

Question

I am using Pyodbc to pull some data off our internal database, and am using the following SQL which works perfectly in SQL Server Management Studio.

'SELECT GrantInformation.GrantRefNumber, Count(GrantResearchTopic.PrimaryInd) AS CountOfPrimaryInd 
FROM GrantInformation 
LEFT JOIN GrantResearchTopic ON GrantInformation.GrantRefNumber = GrantResearchTopic.GrantRefNumber 
WHERE ((GrantResearchTopic.PrimaryInd)='True') 
GROUP BY GrantInformation.GrantRefNumber;'

For some reason if I remove the WHERE statement the SQL pull works, but if I leave it in, I get 'INVALID SYNTAX' despite as I said the SQL working fine in SQLSMS.

I have tried using double quotes around true, but get the following error:

ProgrammingError: ('42S22', "[42S22] [Microsoft][ODBC SQL Server Driver][SQL Server]Invalid column name 'True'. (207) (SQLExecDirectW)")

Any ideas?

Edit: Problem solved. I didn't use double quotes on my SQL Statement so it wouldn't recognise the single quotes around the True statement. Using 1 worked as wasnt using quote marks, and using double quotes around the statement also worked as it then recognised the single quotes around 'True'. Thank you all.

Answer 1

Your whole SQL statement should be in double quotes not single quotes and then the true can remain in the single quotes.

• Nicholas: Yep, that also worked. That was a simple mistake! Thank you Matt!

Answer 2

I solved a similar problem replacing 'True' with 1 (without quotes).

• Nicholas: Perfect, yep, changing it to 1 fixed the problem. It is strange as I have done many SQL statements before with Pyodbc and never had this issue before, must be something about the database!

Answer 3

'True' (True enclosed by single or double quote) is a string and cannot be compared with a boolean. In sql using 1 or TRUE (both without quotes and TRUE is not case sensitive) for boolean true will work.

• Nicholas: The 1 worked for me, the TRUE didn't, but either way, it was the fact I didnt double quote the SQL string which was causing the issue. thank you for your help! :)
• naimul64: Most probably your 'PrimaryInd' field is a bit, not a boolean.

Answer 4

Try removing semicolon at the end of your statement

• cs95: SQL statements can be semicolon terminated.
• Nicholas: I tried removing it and still get the same syntax error pointing at the true statement :/