Why doesn't Truncate work in DTS?

[Seabz420]

Why does TRUNCATE TABLE produce an error when I try to run it in a DTS? I'm using SQL 2000. The error is:

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

Monthly I update 1GB of data and the logs get pretty big pretty quick and need a better solution that DELETE. Ideas?

 

[mrdenny]

truncate table should work fine in DTS, providing that the person running the package has access to truncate the table.

Sounds like you've got some sort of code problem.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[gradley]

If you are still having the problem, you should check that you don't have any FOREIGN KEYS or indexed views over this table. Both of these scenarios will prevent you from using the TRUNCATE TABLE command.

 

[Seabz420]

What the problem was was that my Truncate SQL statement was trying to use my MS Access connection. I changed it so that it uses the SQL connection and bingo bango, it works!