[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now


Execute SQL Task: truncate tables w/parameter

Posted on 2007-10-02
Medium Priority
Last Modified: 2013-11-30
I'm trying to truncate all tables that match the results of a query.  I populate a recordset with the list of table names and pass it to a Foreach loop.  This works fine; if I run a messagebox Script Task inside the loop it gives me the names.

What I want to do inside the loop is run an Execute SQL Task to truncate the table.  I've done this successfully by typing the SQL command explicitly (ie "TRUNCATE TABLE Customer"), but something about the way I'm handling the parameter is failing.  I've tried "TRUNCATE TABLE ?", "EXEC('TRUNCATE TABLE ' + ?)", and even scripting my variable to be a full TRUNCATE statement and then setting my SQL task to "EXEC(?)".

Each time, I get "...failed with the following error: 'Synatax error, permission violation, or other nonspecific error'. Possible failure reasons...".  This seems like I'm doing something small and simple wrong, but I can't find it.

Task Properties:
ResultSet = None
ConnectionType = OLE DB
SQLSourceType = Direct input

Parameter Mapping:
VariableName = User::strTableName (which is in scope and works when I use a Script task to test)
Direction = Input
Data Type = VARCHAR
Parameter Name = 0
Question by:Rakafkaven
  • 4
  • 2
LVL 16

Expert Comment

ID: 19999079
@varibale not ?

Im just taking a shot here...
LVL 16

Expert Comment

ID: 19999089
TRUNCATE TABLE @variablename

Author Comment

ID: 19999252
Doesn't parse-- I changed the Parameter Name (on the Parameter Mapping tab) to @temp, and replaced the '?' with @temp, but the parser says I need to declare @temp and the query fails.

If I'm reading the technet pages right, if my Connection Type is OLE DB then my parameter names have to be ordered integers (0,1,2...) and they're represented in the SQL itself by markers '?'.  Granted, that doesn't seem to be working, so I'm open to the possibility that I'm not reading it right.
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.


Author Comment

ID: 19999305
The ? syntax seems to be right; if I change my task to "SELECT Count(*) from sysobjects where name = ?" it runs fine.  It seems like it's something specifically about the Truncate and/or Exec statement.

Author Comment

ID: 19999332
I made a workaround; if I set a script task to make the tablename into a full SQL statement and then set my script task to SQLSourceType = Variable, it runs.

Points will still go to anyone who can tell me what was wrong with the way I was originally trying.
LVL 15

Accepted Solution

dbbishop earned 2000 total points
ID: 19999354
In DTS you use ? as placeholders in an EXECUTE SQL task, but I am not certain in ADO. In ADO.NET, you do add the '@' to the parameter name, but I doubt you do in ActiveX scripts.

You should be able to execute your code against the connection object:
sSQL = "TRUNCATE TABLE " & sTbaleName
cn.Execute sSQL

Author Comment

ID: 19999741
Good enough for me.  Thanks!

Featured Post

Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ready to get certified? Check out some courses that help you prepare for third-party exams.
This shares a stored procedure to retrieve permissions for a given user on the current database or across all databases on a server.
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Suggested Courses

873 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question