TRUNCATE TABLE at BODS SQL script

[christian_swonke]

Hi all,

I would like to do a TRAUNCATE TABLE during BODS data flow.

The flag "delete before insert" seems to execute a DELETE statement which is occuring a lot of database logs.

I found several examples how to do this but is is not working well.

My SQL syntax is following:

sql('DataSource', 'TRUNCATE TABLE TableName')

I am getting following error:

ODBC data source <...> error message for operation <SQLExecute>: <[Microsoft][ODBC Driver 17 for SQL Server][SQL Server]Cannot find the object "TableName" because it does not exist or you do not have permissions.>.

The table exists. I did a short test by SELECT COUNT(*).

What is wrong?

If it is a user permission error, which permissions will the user need?

Regards

Christian

[sapuserapr2021]

Hi Christian,

From your example, do you have a table called 'TableName' or is that meant to be a variable or did you just used that text because you didn't want to show the real table name? What database are you using? If it's Oracle, all table names should be in uppercase. If this is Oracle, as long as the table is in your schema you should be able to truncate it.

Thanks, Dave

[denise_meyer]

Check permissions for the user connecting with the datastore, if a select works fine, the user may not have permissions to do a truncate on the same table.

Thanks,

Denise
SAP Support

[christian_swonke]

Hi Dave,

thanks for your feedback.

in the original database name we ware doing this. At least Schema and Table name - as I wrote for count it is working.

Regards

Christian

[christian_swonke]

Hi,

it is just a placeholder because I don't want to show the real name.

Database is a Microsoft SQL database

Regards

Christian

[sapuserapr2021]

Thanks. Well, your syntax seems fine. The only other thing I can think of would be if you need to specify the database and schema.

If you were to do this the statement would be 'truncate table <database>.<schema>.<table name>'.

See this page for more info: SQL Server: TRUNCATE TABLE Statement (techonthenet.com)

Regards, Dave