Open commands in SQL Server Enterprise Stage

suja.somu · Post by **suja.somu** » Thu Mar 21, 2013 12:21 pm

For every day run, I have to truncate the target table before loading.
The job runs on a 2- node configuration.

I am using a OPEN Command in the SQL server Enterprise stage.

DELETE FROM tablenameA;

This is not working . Looks like I cannot use a commit in open command. whats the feasible approach to do?

ray.wurlod · Post by **ray.wurlod** » Thu Mar 21, 2013 3:16 pm

TRUNCATE TablenameA perhaps? This should be non-transactional.

suja.somu · Post by **suja.somu** » Thu Mar 28, 2013 9:16 am

Firstly, truncate table commands can be executed only by the owner. So the DBA's are reluctant to grant the owner privilege for the Datastage user.

Secondly, there is a reference for Foreign key to this table. So Truncate table command cannot be used in this case.

What is the best alternative for this process?

chulett · Post by **chulett** » Thu Mar 28, 2013 10:16 am

Typically, that would be a stored procedure written by the DBAs that you would have execute grants on. It would have the grants needed to do the truncate and could handle any RI constraints as well.

suja.somu · Post by **suja.somu** » Mon Apr 01, 2013 3:15 pm

I used an ODBC connector stage instead of SQL server enterprise stage , to acheive this delete table request in run before SQL comand and then

suja.somu · Post by **suja.somu** » Mon Apr 01, 2013 3:16 pm

--- Continuation to the previous post

I did " delete table " in before sql and then insert into table.