SQL Server Performance Forum – Threads Archive
truncate table in DTS
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 JankaThere may be some syntax error?
Refer www.sqldts.com for more DTS queries Madhivanan Failing to plan is Planning to fail
this is a syntax error. can you post the statnebt please —————————————-
Cast your vote
http://www.geocities.com/dineshasanka/sqlserver05.html http://spaces.msn.com/members/dineshasanka
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.
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
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 ?
]]>