Error [SAP BusinessObjects][ODBC SQL Server Wire Protocol driver][Microsoft SQL Server]Incorrect syntax near 'Colume_Name'.>.

[Former Member]

Hi Experts

I have rebuild a New BODS job (SQL Server 2008) from existing Old BODS job (SQL Server 2000) as the source system has got upgrade from SQL Server 2000 to SQL Server 2008

But When i try to excite the New BODS job (SQL Server 2008) it throws error, please find the below error log

Error Log :

|Data flow DF_LS_BW_BLK|Reader search+Results+Property+Solicitor+tblSearchAddOns1

SQL submitted to ODBC data source <LandSearch_UAT> resulted in error <[SAP BusinessObjects][ODBC SQL Server Wire Protocol

driver][Microsoft SQL Server]Incorrect syntax near 'column name'.>. The SQL submitted is <SELECT  { fn substring(

"table"."column name " , 1, 60)  }  ,  { fn substring( "table"."column name" , 61, 40)  }  ,  { fn substring(

"table"."column name1" , 1, 60)  }  ,  { fn substring( "table"."column name2" , 61, 20)  }  ,

[akhileshkiran · Accepted solution]

Hi Suvendu,

Copy the Optimised SQL of that Dataflow and Execute that SQL code in SQL Server Management Studio Query panel.

If everything works fine. Please check for the case sensitive column names for the template tables make everything CAPITAL in the query transform before the template table.

If it is a functionality change like data type conversation you need to explicit convert them by using cast function.

Reagrds,

Akhilesh Kiran.

[Former Member · Accepted solution]

Hello Akhilesh,

Thanks for your response!

As mentioned above i have tried executing the Optimized SQL in Query panel and it was fine

We have not used any Template tables.

We have use cast function explicitly for datatype conversions and the issue is still the same

I still get the error .

Regards

Suvendu

[akhileshkiran · Accepted solution]

Hi

can you upload a screenshot of the particular explicit conversion. Post the error log if possible. 

Regards,

Akhilesh Kiran.

[Former Member · Accepted solution]

Hi Akhilesh,

Please find the below screenshots

[akhileshkiran · Accepted solution]

Hi

can you please post some sample DateOutputGenerated data once.

Regards,

Akhilesh Kiran.

[Former Member · Accepted solution]

Hi Akhilesh,

Please find the below sample DateOutputGenerated

2000-01-05 14:51:42.670

2000-01-05 14:08:24.623

2000-01-06 15:00:57.107

2000-01-06 12:41:33.233

Regards

Suvendu

[akhileshkiran · Accepted solution]

Hi

The below way you want output right ?

Input: 2000-01-05 14:51:42.670  (YYYY.MM.DD)

Output:  2000-01-05 (YYYY.MM.DD)

The data type is date right ?

Regards,

Akhilesh Kiran.

[Former Member · Accepted solution]

Akhilesh,

Yes, this is the output .

Regards

Suvendu

[akhileshkiran · Accepted solution]

Hi

you can use.

to_char( Query.date_var, 'YYYY.MM.DD')

Output:

Or-else if the source data type is date-time. Directly you can use date as datatype it will truncate the time.

Output:

Regards,

Akhilesh Kiran.

[Former Member · Accepted solution]

Hi Akhilesh,

I have tried all the above examples.

But still i get the same error message incorrect syntax near ( column_name)

Regards

Suvendu

[akhileshkiran · Accepted solution]

if possible can you please share me your .ATL File to my

mail id:  narayandasakhileshkiran@gmail.com.

Regards,

Akhilesh Kiran.

[Former Member · Accepted solution]

Hi Akhilesh,

Thank you very much for suggestions

The issue has resolved, we need to check the check-box of Enable Quote identifier in the data source of the job  and ODBC file

regards

Suvendu