[Webinar] Streamline your web hosting managementRegister Today

x
?
Solved

Executing a DTS package from a stored Procedure

Posted on 2001-06-04
6
Medium Priority
?
431 Views
Last Modified: 2013-11-30
Hi All

How can I execute a DTS package stored on my SQL Server from a stored procedure.

Many Thanks

0
Comment
Question by:demmick
6 Comments
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6153391
look at dts_run and xp_cmdshell.
Or you could set the job to run under the scheduler then use sp_startjob.
0
 
LVL 18

Expert Comment

by:nigelrivett
ID: 6153396
oops dtsrun
0
 

Author Comment

by:demmick
ID: 6155430
I know of the dtsrun comand but I can't use it in the Query analyser.  I am assuming that it will work in the stored procedure if it works in the query analyser.
0
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

 
LVL 2

Expert Comment

by:niklausj
ID: 6157470
you can't use dtsrun directly from qa or sp, since it's a DOS command, you need to run it via xp_cmdshell (blocking) or add a job and use sp_startjob in your sp.
0
 
LVL 8

Expert Comment

by:chigrik
ID: 6158367
Read about "How can I run a DTS package from within SQL Server - e.g. a stored-procedure?"
http://www.ntfaq.com/Articles/Index.cfm?ArticleID=14230

Hope this helps
0
 
LVL 2

Accepted Solution

by:
iamari earned 45 total points
ID: 6754390
in the stored proc is like:

exec master..xp_cmdshell 'DTSRun /S (local) /U sa /P  /N myPackageName', NO_OUTPUT

where User id (sa) and Password (if existing) should be provided
no_output prevents the server from sending back the "Loading... " message to user
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Question has a verified solution.

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

A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
This month, Experts Exchange sat down with resident SQL expert, Jim Horn, for an in-depth look into the makings of a successful career in SQL.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Via a live example, show how to shrink a transaction log file down to a reasonable size.
Suggested Courses

591 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