Update Table is not working in BODS through script

[Former Member]

Hi Experts,

I have a problem updating SQL server 2008 table through BODS 4.2

My design as below

Job--->Worflow--> Script1--->Dataflow--->Script2

Script1: Here we are updating starttime before dataflow start. The below script is working fine for updating Startload.

SQL('DS_ODS','UPDATE Timestamp_Tables SET StartLoad = CURRENT_TIMESTAMP WHERE TableName = \'30DayOrderDemand\'');

Script2: Here we are updating endtime after dataflow completes. This is not working and its throwing error like incorrect syntax near '30' but its working for above script. Here only column name is different and both the cloumns are same datatypes Datetime.

SQL('DS_ODS','UPDATE Timestamp_Tables SET LastLoad = CURRENT_TIMESTAMP WHERE TableName = \'30DayOrderDemand\'');

Thanks & Regards,

Balamurugan G

[Former Member · Accepted solution]

Hi,

Please validate and run your query in any SQL editor if available to catch the exact error.

If it is working fine in editor, the only change you need to make is adding escape characters while pasting in BODS.

Thanks

Sameera.

[Former Member · Accepted solution]

Hi Sameera,

I have validated in SQL editor and update is working with error. Through BODS also LastLoad getting updated but its throwing an error.

Msg 102, Level 15, State 1, Line 1

Incorrect syntax near '30'.

Msg 319, Level 15, State 1, Line 1

Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

(1 row(s) affected)

(1 row(s) affected)

Thanks,

Bala G

[Former Member · Accepted solution]

Hi,

Can you please try to put the value 30DayOrderDemand enclosed within double quotes into a variable & use that variable in the filter condition..?

Best Regards,

Praveen

[Former Member · Accepted solution]

Bala,

Please find my understanding and correct me.

You tried to run the following statement in a SQL editor

UPDATE Timestamp_Tables SET LastLoad = CURRENT_TIMESTAMP WHERE TableName = '30DayOrderDemand';

and editor returned you an error-- Incorrect syntax near the keyword 'with'....

I'm confused since we don't have a WITH keyword anywhere in the above statement and editor is returning this error.

[mageshwaran_subramanian · Accepted solution]

Can you check if there's any trigger associated with table Timestamp_Tables?.

sp_depends Timestamp_Tables

I think the UPDATE triggers something that causes the issue

[Former Member · Accepted solution]

Hi Magesh,

Thanks. Yes, there is trigger exist for LasLoad column that is reason it was not working..

Thanks & Regards,

Balamurugan G

[Former Member · Accepted solution]

Hi Sameera,

Yes..I tried the above statement only..

Thanks,

Bala G

[Former Member · Accepted solution]

Hi Praveen,

Yes, Already tried with variable and its not working..

Thanks & Regards,

Balamurugan G

[Former Member]

Dear Friends,

My sincere Thanks to everyone for your valuable inputs and finally we found reason, there is trigger exist for LastLoad column in Timestamp_Tables and spoke business they are planning to change the table name to Demand30DayOrder instead of 30DayOrderDemand .

Thanks & Regards,

Balamurugan G

[Former Member]

Hi Balamurugan,

Please try the below query

SQL('DS_ODS','UPDATE Timestamp_Tables SET LastLoad = CURRENT_TIMESTAMP WHERE TableName = ''30DayOrderDemand''');

Also, please confirm the column name LastLoad. If confirmed, try using the column names within double quotes.

Regards,

Varun

[Former Member]

Hi Varun,

I tried and its not working. LastLoad is the column name for LastLoad.

[former_member186897]

Hi Balamurugan Gurusamy,

Your table name starts with a number, this may be causing issue. Use double quotes for your table name in your script.

Hope this helps.

[Former Member]

Hi Ansari,

I hope you are doing good. Thanks for your response. Already I tried with double quotes it didn't works. it works if we change the tablename values with double quoute in both places.. script & Timestamp_Tables in database but i don't want to use double quotes in the Timestamp_Tables.

Thanks & Regards,

Balamurugan G

[former_member186897]

By standard you should not have a table name starting with a number.

See this article, it has some explanation.

sql - Table or column name cannot start with numeric? - Stack Overflow

[Former Member]

Hello,

Maybe you should make a commit operation explicitly after the last update statement.

Hope it helps,

[Former Member]

Hi Nestor,

Thanks.. I have tried with commit operation and didn't works.

Thanks & Regards,

Balamurugan G

[former_member234353]

Hi Balamurugan,

Before executing the job again, can you please try the below options to check if there are no syntax issues:

1. Can you first validate the script(Script2) and the entire workflow  and check if there is any syntax error at any place?

2. Is the code that you pasted the only thing in the Script2 ?

Let me know if the above observations are correct at your end.

Thanks,

AJ

[Former Member]

Hi Anirban,

Thanks for your response.

1.Yes. I have validated the entire job and there is no syntax error.

2.Yes. Script2 thats it, whatever i have pasted. I am wondering same script its working for startload but not working for Lastload.