Question

Syntactical error in update query in cloud A360

  • 24 February 2023
  • 9 replies
  • 500 views

Badge +5

Hi Everyone,

I am facing an issue where I am using excel as database and while running it in the Cloud A360, its giving me a syntactical error but the same query is running fine in the On-Prem A360.

 

Can anyone please help me out with this issue ?


9 replies

Userlevel 7
Badge +13

Hi @Abhi25 ,

 

Kindly share the Update query and the output you are getting from it.

 

It is always advised to use message box while testing with the SQL query to check the output with respect to the query in use.

Badge +5

Hi @Abhi25 ,

 

Kindly share the Update query and the output you are getting from it.

 

It is always advised to use message box while testing with the SQL query to check the output with respect to the query in use.

Hi Padmakumar,

The query looks something like below :

 

Update [Sheet1$$] set Status="Success", Remarks="" where SSN4 like "%$Last4SSN$%" and FirstName="$FirstName$" and LastName="$LastName$" and DateOfBirth='$DtOfBirth$'

 

As I said, the query is running fine in the on prem VM but its giving me the error - There is a syntactical error in the SQL statement. - SQL statement got timed out. when I am executing it in the cloud VM

 

Is it due to package version issue or some other backend issue ?

Userlevel 7
Badge +13

Hi @Abhi25 ,

 

Kindly share the Update query and the output you are getting from it.

 

It is always advised to use message box while testing with the SQL query to check the output with respect to the query in use.

Hi Padmakumar,

The query looks something like below :

 

Update [Sheet1$$] set Status="Success", Remarks="" where SSN4 like "%$Last4SSN$%" and FirstName="$FirstName$" and LastName="$LastName$" and DateOfBirth='$DtOfBirth$'

 

As I said, the query is running fine in the on prem VM but its giving me the error - There is a syntactical error in the SQL statement. - SQL statement got timed out. when I am executing it in the cloud VM

 

Is it due to package version issue or some other backend issue ?

 

Are you testing this code first time on this environment?

If so, you can try changing the package version. Else, put a Message box just before this Update query and see the output and compare it with the Update query.

 

 

 

 

Userlevel 5
Badge +9

@Abhi25 

Are you able to perform the query?

 

Select Status, Remarks from [Sheet1$$] where SSN4 like "%$Last4SSN$%" and FirstName="$FirstName$" and LastName="$LastName$" and DateOfBirth='$DtOfBirth$' 

 

Regards

Badge +5

@Abhi25

Are you able to perform the query?

 

Select Status, Remarks from [Sheet1$$] where SSN4 like "%$Last4SSN$%" and FirstName="$FirstName$" and LastName="$LastName$" and DateOfBirth='$DtOfBirth$' 

 

Regards

yes Rahul, the select query is working fine

Badge +5

Hi @Abhi25 ,

 

Kindly share the Update query and the output you are getting from it.

 

It is always advised to use message box while testing with the SQL query to check the output with respect to the query in use.

Hi Padmakumar,

The query looks something like below :

 

Update [Sheet1$$] set Status="Success", Remarks="" where SSN4 like "%$Last4SSN$%" and FirstName="$FirstName$" and LastName="$LastName$" and DateOfBirth='$DtOfBirth$'

 

As I said, the query is running fine in the on prem VM but its giving me the error - There is a syntactical error in the SQL statement. - SQL statement got timed out. when I am executing it in the cloud VM

 

Is it due to package version issue or some other backend issue ?

 

Are you testing this code first time on this environment?

If so, you can try changing the package version. Else, put a Message box just before this Update query and see the output and compare it with the Update query.

 

 

 

 

Yes I am testing the code for the first time in this environment. I tried updating the database package version but still the same issue.

Userlevel 7
Badge +13

Hi @Abhi25 ,

 

Kindly share the Update query and the output you are getting from it.

 

It is always advised to use message box while testing with the SQL query to check the output with respect to the query in use.

Hi Padmakumar,

The query looks something like below :

 

Update [Sheet1$$] set Status="Success", Remarks="" where SSN4 like "%$Last4SSN$%" and FirstName="$FirstName$" and LastName="$LastName$" and DateOfBirth='$DtOfBirth$'

 

As I said, the query is running fine in the on prem VM but its giving me the error - There is a syntactical error in the SQL statement. - SQL statement got timed out. when I am executing it in the cloud VM

 

Is it due to package version issue or some other backend issue ?

 

Are you testing this code first time on this environment?

If so, you can try changing the package version. Else, put a Message box just before this Update query and see the output and compare it with the Update query.

 

 

 

 

Yes I am testing the code for the first time in this environment. I tried updating the database package version but still the same issue.

 

Ok. Please refer the below articles and see whether either of them is helping or not.

 

  1. A360 || Excel as Database commands fail to execute with Syntactical Error
  2. A360.x: Database package throws error as "There is a syntactical error in the SQL statement. -SQL statement got timed out."
Badge

Tried both, same issue

Badge +2

Hello @shahaksp - 

I had similar issue like yours but after checking many things I figured out the root cause in my case was the formatting of column in excel vs the formatting in the sql statement for that column.

Check if all the columns used in your sql is formatted as text values in the excel. To be sure, you can explicitly format the cell values as text in your excel and try running your update sql again from controlroom. Hope this helps!

Cheers!

 

Reply