SQL Server Performance

truncate table in DTS

Discussion in 'SQL Server DTS-Related Questions' started by jastone, Nov 10, 2005.

  1. jastone New Member

    I tried to truncate a table using the execute sql task object and it would not allow me, the error message i got said:

    Invalid SQL statement; expected 'DELETE', 'INSERT', 'PROCEDURE', 'SELECT', or 'UPDATE'.

    however, there is already a execute sql task object that allows a truncate, but i cant tell how the person made it accept the truncate logic

    Joe Janka
  2. Madhivanan Moderator

    There may be some syntax error?
    Refer www.sqldts.com for more DTS queries


    Failing to plan is Planning to fail
  3. dineshasanka Moderator

  4. Adriaan New Member

    If your target database is not a SQL Server database, but for instance an MDB file, then TRUNCATE may not be supported, and would fail.

    Also, the login under which the action is executed needs to have pretty elevated permissions in the database to execute TRUNCATE, and not just delete permissions on the table.

    Finally, if the table that you're trying to truncate is referenced in a FK, then TRUNCATE will also fail.
  5. jastone New Member

    no foreign key is referenced, this is just a staging table that i want to truncate and then refill with the latest data from a quarterly load.

    the account running the dts is admin, which has rights to do a truncate, delete, etc.

    the syntax used is
    truncate table tableOwnerName.TableName

    Joe Janka
  6. DBA_Newbie New Member

    Then there should be no issues. As Adriaan said, check the permissions again. Are you running the details using sa user ? Or with a login having admin privileges ?

Share This Page