sql server dts:
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
[quoted text, click to view] "helenz" <hadfaf@microsoft.com> wrote in message
news:hadfaf@microsoft.com:
> 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
