DTS / Stored Proceedure

How can you run a DST package from a stored proceedure.
I am using sql server 2000
i cant find the syntax anywhere
it is a DTS that takes a file and imports it into a table in the dbi was looking around and someone said to try to run this at the sql prompt

EXEC xp_cmdshell 'dtsrun /S /U /P /N
exportEmail'

where exportEmail is the dts
when i do this though it says xp_cmdshell is a stored procedure that cant be found
is this something i have to make or is it something that should be there

http://www.dbforums.com/t688385.html|||i tried this method

http://p2p.wrox.com/topic.asp?TOPIC_ID=9741

and then it came back with "you dont have permission for xp_cmdShell" in short
where is this function located and where can i change permissions|||xp_cmdshell is found in the Master database. Seexp_cmdshell for more information.

Terri|||i set the permissions and i used the command

CREATE PROCEDURE MassImport AS
exec master..xp_cmdshell 'dtsrun /S /N /E'
GO

and i got the error

".Net SqlClient Data ProviderA severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on link 472"

Is my stored proceedure correct
did I miss something does it not like the permissions
i am going to try to dig deeper into the link you gave me. But if someone knows a quick fix please tell me|||The problem I see with the stored procedure is that you did not supply the package name (exportEmail) with the /N switch, and you used the /S switch to indicate which server you were running against but did not supply the server name.

Note that you can run DTSRUN /? from the command prompt to see an explanation of each of the switches.

Terri|||Looking at the help in the command prompt this should be correct
my package name is "Mass Import" and this is a local sql server
Does this require no spaces in the package name or do i need quotes somewhere in the statement

here is my new stored procedure
and i get the same error message

exec master..xp_cmdshell 'dtsrun /S(local) /N(Mass Import) /E'

when i looked at the help /s and /n are both optional and /e is needed
/e seems to be the only one required.

i also tried it at a command prompt and this is the error i got.

H:\>dtsrun /S(local) /N(Mass Import) /E
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
 Error string: The specified DTS Package ('Name = '(Mass Import) '; ID.Versio
nID = {[not specified]}.{[not specified]}') does not exist.
 Error source: Microsoft OLE DB Provider for SQL Server
 Help file:
 Help context: 0

|||Did you try enclosing your package name in double quotes?

H:\>dtsrun /S "(local)" /N "Mass Import" /E

Terri|||gives me the same error

H:\>dtsrun /S "(local)" /N "Mass Import" /E
DTSRun: Loading...
Error: -2147217900 (80040E14); Provider Error: 14262 (37B6)
 Error string: The specified DTS Package ('Name = '"Mass Import" '; ID.Versio
nID = {[not specified]}.{[not specified]}') does not exist.
 Error source: Microsoft OLE DB Provider for SQL Server
 Help file:
 Help context: 0

|||I've tried that exact syntax and it works for me. So I would guess that either the Server is wrong or the Package Name is wrong.

You might try the dtsrunui.exe tool. This will enable you to visually set up your dtsrun statement.

From the command prompt, run this tool, select your Server from the drop-down (or type it in if it does not appear), choose the Authentication method, then choose the Package Name from the dropdown. When you have all of this set, click on the "Advanced..." button, and on this popup, click the "Generate..." button towards the bottom. This will drop in the generated dtsrun command in the box to the left. You should be able to use this generated command to run your package (by copying and pasting it into your stored procedure).

Terri|||that tool is cool thank you :)

here is the new command it made me run

DTSRun /S "(local)" /N "Mass Import" /G "{73D07FB3-E2D4-4A92-B69F-20C746F00DC1}" /W "0" /E

|||alright now my stored proceedure runs correctly in the command prompt and in the sql query analyzer
but for some reason it isnt working in my code

here is my code

 Dim cmdMassImport As New SqlCommand("MassImport", C.ndConnection)
 cmdMassImport.CommandType = CommandType.StoredProcedure
 C.ndConnection.Open()
 cmdMassImport.ExecuteNonQuery()
 C.ndConnection.Close()

Here is the error

A severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line 472
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.Data.SqlClient.SqlException: A severe error occurred on the current command. The results, if any, should be discarded. xpsql.cpp: Error 997 from GetProxyAccount on line 472
Source Error:
Line 5760:
Line 5761: C.ndConnection.Open()
Line 5762: cmdMassImport.ExecuteNonQuery() <-- Says error is occuring here
Line 5763: C.ndConnection.Close()
Line 5764: End Sub
Source File: e:\Inetpub\wwwroot\Objectmanipulation\panelMaker.aspx.vb Line: 5762
Stack Trace:
[SqlException: A severe error occurred on the current command. The results, if any, should be discarded.
xpsql.cpp: Error 997 from GetProxyAccount on line 472
]
 System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream)
 System.Data.SqlClient.SqlCommand.ExecuteNonQuery()
 Objectmanipulation.panelMaker.btn_Click(Object sender, EventArgs e) in e:\Inetpub\wwwroot\Objectmanipulation\panelMaker.aspx.vb:5762
 System.Web.UI.WebControls.Button.OnClick(EventArgs e)
 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
 System.Web.UI.Page.ProcessRequestMain()

Am I missing something stupid that I need to initialize to make the stored proceedure work?
or is there something deep that needs to be fixed|||Please??
does this seem to be a code thing
i cant seem to see that i missed anything with the stored proceedure call|||Ack, sorry, I didn't mean to leave you hanging. :-(

I have to try this out myself and get back to you -- I'll try not to make it later than tonight.

And if anyone has a suggestion -- please chime in!

Terri|||After playing around with this for a while and looking at Google, it's a permissions problem with xp_cmdshell. I am not going to be able to talk you through it because I don't thoroughly understand it -- the DBA at my place of employment set this up for me when I needed it.

Terri