SQL Server access using TPT ODBC

Database
Enthusiast

SQL Server access using TPT ODBC

Hi All

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.

Such as,
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.

 

Thanking You
Santanu

3 REPLIES
Enthusiast

Re: SQL Server access using TPT ODBC

Hi All

 

Answer from anybody is appreciated.

 

Regards

Santanu

Apprentice

Re: SQL Server access using TPT ODBC

Hi,

 

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?

 

Something like:

 

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.

 

Cheers,

Dave

 

Ward Analytics Ltd - information in motion
www: http://www.ward-analytics.com
Enthusiast

Re: SQL Server access using TPT ODBC

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.

 

Regards 

Santanu