Running a Stored Procedure in a DTS Package

[alexkeel79]

I am trying to execute a stored procedure in a DTS package but I keep getting an error. I am using the Execute SQL task object with an SP that has a few Update queries and 1 Delete query. When I try to test this object the error it gives me says that I can't execute a select statement. Any thoughts?

 

[SQLDenis]

Can you show me the exact call to the sp in the Execute SQL task object window

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[alexkeel79]

EXEC SP_NAME;

I also tried adding the database name and owner as such:

EXEC DBNAME.dbo.SP_NAME;

With the first call I received an error saying I could not call a select query but there is no select query in the SP. With the second the error said could not find the object 'DBNAME'. Note that I tried both of these lines in Query Analyzer and they both worked fine. Thanks.

 

[SQLDenis]

Is your connection pointing to the right server and or DB?

“I sense many useless updates in you... Useless updates lead to fragmentation... Fragmentation leads to downtime...Downtime leads to suffering..Fragmentation is the path to the darkside.. DBCC INDEXDEFRAG and DBCC DBREINDEX are the force...May the force be with you" --

http://sqlservercode.blogspot.com/

 

[dwight1]

Hi guys,

I am looking for the same solution. I want to run a delete table and append table sp using DTS.

Right now, i am using a macro on the open event of the form but i do not want it to run every time the form is opened as it will be hog on the network.

I never did this before, can you guys let me know how i can create a DTS for Executing an existing SP. So that i can run it once in a day offpeak hours.

Thanks in advance

Dwight.