I need help on TPT to SQL Server access using ODBC Connector.
When I am executing below query on SQL Server Studio it is running fine.
SELECT * FROM dbo.tbl_CSM_Schedules_MM
WHERE StartDateTime >= CAST('2017-07-03 00:00:00' AS SMALLDATETIME)
AND EventID = 123 AND CloseTimeStamp IS NOT NULL ;
But when I am using same query on TPT ODBC Connector to fetch data from SQL Sever, it is giving syntax errors.
Incorrect syntax near the keyword '>' (Code = 102)
Incorrect syntax near the keyword '=' (Code = 102)
Incorrect syntax near the keyword 'IS' (Code = 156)
Please advise how to get over these errors. It is very urgent.
Not sure if this will help, but if this SQL is coded inside a TPT script, do you need to double-up the quote marks?
sql_to_submit = 'SELECT * FROM dbo.tbl_CSM_Schedules_MM
WHERE StartDateTime >= CAST(''2017-07-03 00:00:00'' AS SMALLDATETIME)
AND EventID = 123 AND CloseTimeStamp IS NOT NULL ;'
You may need more than 2 quote marks.
Thanks Dave for your response.
Your suggestion to use double quote ( " ) is valuable and I used that.
But it appears, not only double quote, we need something more. Finally I figured out how to resolve this.
1. On odbc.ini file need to set : AnsiNPW=Yes
2. Most importantly, in query need to place columns in [ ] , that is : [ EventID ] = "123" AND [ CloseTimeStamp ] IS NOT NULL
All above combinations worked for me.