Hi,

I am getting frastrated by trying to call DTSrun inside the stored prcedure.
I want to my DTS triggered by user interface clicking. So I wrote a stored
procedure
inside sql server and call DTS inside the stored procedure:

CREATE PROCEDURE [dbo].[CSCDDTSRefresh] AS
EXEC master..xp_cmdShell 'dtsrun /S sqlserver /N "DTS_CSCD" /G
"{4729D6E9-0AED-4BD3-A2A1-B617836EB22D}" /V
"{FE153DB1-583D-4246-B786-2554AF57A0F5}" /U "sa" /P "pass" ' , no_output
GO

Then inside xx.vb file,
sub DTSRefresh() As Int16
Dim dbConn As New SqlConnection(connectionString)
Dim command As New SqlCommand("CSCDDTSRefresh", dbConn)
command.CommandType = CommandType.StoredProcedure
dbConn.Open()
command.ExecuteNonQuery()
dbConn.Close()

End Sub


I can run my DTS manually successfully. But every time when I call from the
stored
prcodure, nothing happened. How can I catch error?

can Somebody help?
Really appreciate.


--
helenz

I would log to text file in the package

Xp_cmdshell will change the execution context

Your cmdline specifies a Version (/V) of the package also which may not
be what you want


Execute a package from T-SQL
(http://www.sqldts.com/default.aspx?210)


From BOL

When xp_cmdshell is invoked by a user who is a member of the sysadmin
fixed server role, xp_cmdshell will be executed under the security
context in which the SQL Server service is running. When the user is not
a member of the sysadmin group, xp_cmdshell will impersonate the SQL
Server Agent proxy account, which is specified using
xp_sqlagent_proxy_account. If the proxy account is not available,
xp_cmdshell will fail. This is true only for MicrosoftR Windows NTR 4.0
and Windows 2000. On Windows 9.x, there is no impersonation and
xp_cmdshell is always executed under the security context of the Windows
9.x user who started SQL Server.




Allan



"helenz" <hadfaf (AT) microsoft (DOT) com> wrote