Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
starson45
Starting Member
5 Posts |
 Posted - 2012-06-20 : 09:01:43
|
| "FINDSTRING( @[User::strTableNameSolarUKbe] , 'DontTruncate_', 1) >= 1? : TRUNCATE TABLE + @[User::strTableNameSolarUKbe] "I've got a For Each Loop Container with this Execute SQL Statement inside it. All I wanna do (ha yeah!) is to truncate all tables in the database where the name does not contain 'DontTruncate_'The error I'm getting is vague"Syntax error, permission violation, or other nonspecific..." whatnotIf table name doesn't contain 'DontTruncate' in name, then don't do anything, simply move on to next table.Any ideas.Thank you. |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-20 : 10:34:30
|
| how are you running the SSIS package, under what permissions?Permissions for TRUNCATE TABLE: The minimum permission required is ALTER on table_name. TRUNCATE TABLE permissions default to the table owner, members of the sysadmin fixed server role, and the db_owner and db_ddladmin fixed database roles, and are not transferable. However, you can incorporate the TRUNCATE TABLE statement within a module, such as a stored procedure, and grant appropriate permissions to the module using the EXECUTE AS clause<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
 |
|
|
starson45
Starting Member
5 Posts |
Posted - 2012-06-20 : 11:07:31
|
| I'm running the package from within Visual Studio. How do I work out what permissions it's using?I got the truncate statement to work on a different database that is a copy of the one I'm trying to use now. In that case though, the truncate statement is simply "TRUNCATE TABLE " + @[variableName] |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-20 : 11:14:30
|
| what type of permissions do you have on the copy database vs the one you are trying to use?<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
starson45
Starting Member
5 Posts |
Posted - 2012-06-20 : 11:30:56
|
| AS far as I can see I've got dbo access. So that should be full permissions surely. |
|
|
|
starson45
Starting Member
5 Posts |
Posted - 2012-06-20 : 11:33:37
|
| db_owner |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2012-06-20 : 11:42:54
|
| spin up sql profiler in the back ground and let it trace, run the ssis package and see what the values are for all your variables<><><><><><><><><><><><><><><><><>If you don't have the passion to help people, you have no passion |
|
|
|
|
|
|
|
|
ExecuteSQLTask ExpressionBuilder syntax check