sql server dts: Hi all,

I am getting this error message when executing a DTS from a Stored Procedure.

DTSRun: Loading...
DTSRun: Executing...
DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1
DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1
DTSRun OnStart: DTSStep_DTSDataPumpTask_1
DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147217865 (80040E37)
Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'cash.dbf' does not exist.
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0

Error Detail Records:

Error: -2147217865 (80040E37); Provider Error: 173 (AD)
Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'cash.dbf' does not exist.
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file:
Help context: 0

DTSRun OnFinish: DTSStep_DTSDataPumpTask_1
DTSRun: Package execution complete.
NULL

This cash.dbf is located on our network at some location and not on my PC or the SQL Server 2000 machine.

Is it at all possible to run a DTS from to import a table from a another database on the network?

PLEASE HELP!!

Yama Kamyar
Sr. Microsoft .NET Consultant

CONTINUATION to my previous post:

Here is the stored procedure:

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS OFF
GO

IF EXISTS (SELECT * FROM sysobjects WHERE type = 'P' AND name = 'ExecueDTS')
BEGIN
PRINT 'Dropping Procedure DtsRun'
DROP Procedure dbo.ExecueDTS
END

GO

/* ==================================
AUTHOR: Yama Kamyar
================================== */
ALTER Procedure dbo.ExecueDTS
@ServerName VARCHAR(30),
@UserName VARCHAR(30),
@Password VARCHAR(30),
@DTSName VARCHAR(30),
@DTSPassword VARCHAR(30),
--WITH ENCRYPTION --Don't use this command but only for production
/* ===================================================================
Encrypting Procedure Definitions
--------------------------------
If you are creating a stored procedure and you want
to make sure that the procedure definition cannot be
viewed by other users, you can use the WITH ENCRYPTION
clause. The procedure definition is then stored in an
unreadable form.

After a stored procedure is encrypted, its definition
cannot be decrypted and cannot be viewed by anyone,
including the owner of the stored procedure or the system
administrator.
=================================================================== */
AS

DECLARE @ERROR INT -- For Hold Error Number
DECLARE @CMD VARCHAR(1000)-- DTS Run Command

BEGIN
BEGIN TRANSACTION
-- Set as No Error
SET @ERROR = 0
SET @CMD = 'dtsrun /S '+@ServerName+' /U '+@UserName+' /P '+@Password+' /N '+@DTSName+' /M '+ @DTSPassword

EXECUTE @ERROR = master..xp_cmdshell @CMD
-- + Error Checking
SELECT @ERROR = COALESCE( NULLIF ( @ERROR, 0 ), @@ERROR )
IF @ERROR <> 0 BEGIN ROLLBACK TRANSACTION RETURN @ERROR END

END
-- Return Error
RETURN @ERROR
GO

GRANT EXEC ON dbo.ExecueDTS TO PUBLIC
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


When I run this procedure from SQA:
EXECUTE dbo.ExecuteDTS
@ServerName = 'mySERVER'
@UserName = 'sa'
@Password = 'www.yamabiz.com'
@DTSName = 'myDTS'
@DTSPassword = 'I_dont_like_FoxPro'

And I get that error message I wrote in my previous message. You see when I open SQL Enterprise Manager and run the DTS puppy it doesn't fuss at all but oh boy does it get on my nerves when I try to run it from SQL Query Analyser using the stored procedure from above.

So after all the talk is done can anyone show me the walk?

Thank you for reading and for your consideration,

Yama Kamyar

Have you specified a UNC path to the .dbf file?

Look at permissions

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
Microsoft® Windows NT® 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.



Note In earlier versions, a user who was granted execute permissions for
xp_cmdshell ran the command in the context of the MSSQLServer service's user
account. SQL Server could be configured (through a configuration option) so
that users who did not have sa access to SQL Server could run xp_cmdshell in
the context of the SQLExecutiveCmdExec Windows NT account. In SQL Server
7.0, the account is called SQLAgentCmdExec. Users who are not members of the
sysadmin fixed server role now run commands in the context of this account
without specifying a configuration change.



--
--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.allisonmitchell.com - Expert SQL Server Consultancy.
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org


[quoted text, click to view]
I open SQL Enterprise Manager and run the DTS puppy it doesn't fuss at all
but oh boy does it get on my nerves when I try to run it from SQL Query
Analyser using the stored procedure from above.
[quoted text, click to view]


begin 666 note.gif
M1TE&.#EA# `+`(#_`(2&`,# P"'Y! $```$`+ `````,``L`0 (:C(\(H'S[
68%R0&ED;M7,'[%S2YW#1)VJ;4P``.P``
`
end