error when running the DTSRUN command

Hi
We're using SqlServer 2000.

I want to run a DTS using the DTSRUN command.
The commmand is inside a ".bat" file which my application is running
from an SQL client.
No matter how I run it from the client it fails giving me the message:

[ConnectionOpen (Connect()).]SQL Server does not exist or access denied

I've tried to run it from the client from the application, from the
command prompt and it fails.
I went into enterprise on the client and tried to run the DTS from
there. The run failed already on the 1st step which is a call to a
stored procedure, also giving me the same message. This really stuck me
as wierd because in enterprise I'm accessing the server successfully so
why the above messgae. It identifies the server and I am accessing it.

Because it failed on the 1st step which is a call to a stored procedure
I tried running the sp from within query analyzer and it succeeded.

Has anyone got any idea what the problem is and ghow to solve it.

Thanks !

David GreenbergDavid Greenberg (davidgr@.iba.org.il) writes:

Quote:

Originally Posted by

I want to run a DTS using the DTSRUN command.
The commmand is inside a ".bat" file which my application is running
from an SQL client.
No matter how I run it from the client it fails giving me the message:
>
[ConnectionOpen (Connect()).]SQL Server does not exist or access denied
>
I've tried to run it from the client from the application, from the
command prompt and it fails.
I went into enterprise on the client and tried to run the DTS from
there. The run failed already on the 1st step which is a call to a
stored procedure, also giving me the same message. This really stuck me
as wierd because in enterprise I'm accessing the server successfully so
why the above messgae. It identifies the server and I am accessing it.
>
Because it failed on the 1st step which is a call to a stored procedure
I tried running the sp from within query analyzer and it succeeded.
>
Has anyone got any idea what the problem is and ghow to solve it.

You can't accuse me to know too much about DTS, but I see that a
DTS package has connection properties. I would assume that you can
have a package on server A, and run it against server B.

So I would check the connection properties of the package (or the step,
as different task surely must be able to connect to different servers).

The error message simply means that you are trying to connect to a
server that is not available. (Not running, misspelled, a firewall
in the way etc.)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Error traping in DTS

I am using a DTS for my purpose and I am calling that DTS execution in a stored procedure(using master..xp_cmdshell dtsrun/F).I like to store the error meassage or error description in the Stored Procedure if it fails due to any reason.How can I do that?call the dtsrun also with /L<log file path >|||Will you kindly give me a brief of that please?|||dtsrun gets some arguments the /L <file path > switch "tells" dtsrun where to log it executed steps results
for more info search books online for "dtsrun Utility" there you can find all info about dtsrun.

error to executing job on Sql Server Enterprise Manager

... DTSRun: Executing... DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
DTSStep_DTSDataPumpTask_2 DTSRun OnError:
DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
Drivers Help file: Help context: 0 Error Detail
Records: Error: -2147467259 (80004005); Provider Error: 171
(AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
open file e:\private.dbc. Error source: Microsoft OLE DB
Provider for ODBC Drivers Help file: Help context: 0
DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
4. The step failed.

anyone know becaouse if i execute this job whit the Schedule package
dont work but i execute manually it's work ?

pls help me ...Initial guess would be that the account that is used for running SQL
Agent can not see e:\private.dbc... Double check what user account is
being used to run SQL Server service versus SQL Agent service and double
check permissions on the directory. Also, test what user accounts can
run the DTS package from Ent Man. It may be that the system or service
account can not run the scheduled job because service account does not
have access where as your account does via Ent Man.

Maurizio Amoroso wrote:
> ... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnError:
> DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
> string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
> e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 171
> (AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
> open file e:\private.dbc. Error source: Microsoft OLE DB
> Provider for ODBC Drivers Help file: Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
> 4. The step failed.
> anyone know becaouse if i execute this job whit the Schedule package
> dont work but i execute manually it's work ?
> pls help me ...|||"Maurizio Amoroso" <maurizio.amoroso@.mlink.it> wrote in message
news:933ecd1c.0401280623.498fed23@.posting.google.c om...
> ... DTSRun: Executing... DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_4 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_2 DTSRun OnError:
> DTSStep_DTSDataPumpTask_2, Error = -2147467259 (80004005) Error
> string: [Microsoft][ODBC Visual FoxPro Driver]Cannot open file
> e:\private.dbc. Error source: Microsoft OLE DB Provider for ODBC
> Drivers Help file: Help context: 0 Error Detail
> Records: Error: -2147467259 (80004005); Provider Error: 171
> (AB) Error string: [Microsoft][ODBC Visual FoxPro Driver]Cannot
> open file e:\private.dbc. Error source: Microsoft OLE DB
> Provider for ODBC Drivers Help file: Help context: 0
> DTSRun OnFinish: DTSStep_DTSDataPumpTask_2 DTSRun OnStart:
> DTSStep_DTSExecuteSQLTask_6 DTSRun OnFinish:
> DTSStep_DTSExecuteSQLTask_1 DTSRun OnStart:
> DTSStep_DTSDataPumpTask_3 DTSRun OnError: DTS... Process Exit Code
> 4. The step failed.
> anyone know becaouse if i execute this job whit the Schedule package
> dont work but i execute manually it's work ?
> pls help me ...

Check out this KB article:

http://support.microsoft.com/defaul...4&Product=sql2k

The account running the job probably doesn't have access to E:\ (NTFS
permissions, unknown drive mapping), and the article explains quite well how
to work out what context the job is running in.

Simon