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 |
|
AskSQLTeam
Ask SQLTeam Question
0 Posts |
 Posted - 2005-03-03 : 07:45:09
|
| Mark writes "What is the best way to execute DTS packages within stored procedures in SQL2000, and what problems might I expect to encounter when doing so?" |
|
|
robvolk
Most Valuable Yak
15732 Posts |
Posted - 2005-03-03 : 07:51:11
|
| I can think of a few ways to do this, which one is best is up to you:1. Create a job that executes the DTS package, then use sp_start_job to run it from your stored procedure (my preference)2. Use xp_cmdshell to run DTSRun to execute the package3. Us sp_OACreate to instantiate a DTS COM package and call the methods needed to execute the package using sp_OAMethod.The first option will cause you the least problems, probably none, and it will run asynchronously. The 2nd option might lock up your sproc if the package takes a while to run, and you may not have permissions to run xp_cmdshell. The 3rd option *may* cause memory issues because it runs inside the SQL Server process space, but it's not likely. It's also the longest method to program, not difficult really, just not as easy as the others.This site may have other or better examples of how to do what you're looking for:http://www.sqldts.com/ |
 |
|
|
|
|
|
Executing a DTS package from a stored procedure