DTS package executes but JOb doesn't run

[colinbell]

Hi
I have set-up a DTS local package which, if executed, runs ok
there is a job that runs based on the DTS
Operating System Command (cmdExec)
DTSRun /~Z0x2D35A89DED3951A08C911FB002FDF25EDBB091C51DC3365B34576901DC31D6E7E6F622FDA3A21AA76C76863501518421D37D6F66D9249AAC4678639082BDB30645AE282D24BA71DC4FC5BE7D4E2F3D0FAE523C6B644912081F9BD56FC9DE677A50550C8F8C637DCDC3F97921ED79523C7D8339761B5E0B8EFD09C8D93B1D40F26F567457E45BE13145B28E70B78D67F6D4AB52EA8C8522171472E0

this fails ?

I have run the job as owner sa, which exists on both SQL servers and has sysadmin rights ?

any ideas ?
regards
cath

 

[mrdenny]

Is there any message in the job history?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[colinbell]

Hi Denny

Job History as follows

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSExecuteSQLTask_1 DTSRun OnError: DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 Error Detail Records: Error: -2147467259 (80004005); Provider Error: 17 (11) Error string: [DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied. Error source: Microsoft OLE DB Provider for SQL Server Help file: Help context: 0 DTSRun OnFinish: DTSStep_DTSExecuteSQLTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

Thanks
Cath

 

[mrdenny]

Ok, the DTS package wasn't able to locate the SQL Server that was selected in a connection object.

From the Server console make sure that you can connect to all the servers referenced by the DTS package. There may be a network problem between the two, or the login that you are using on the server running the package doesn't have access to the other SQL Server for some reason.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[colinbell]

Hi Denny

Yeah I can connect to it through server console - one strange thing is when i tried to add as an active directory (just as a test) it said error 14353 - cannot determine the service account for this SQL server instance ?

 

[colinbell]

denny

i also notice that, using SQL server client network, the server, i'm trying to connect to, is using TCP/IP which has a network library of c:\windows\system32\dbnetlib.dll which is mentioned in the error message ?

regards

 

[colinbell]

both servers, on the SQL server agent, are using Windows authentication - do i need to grant the SQLAgentCmdExec account proper login and database permission rights to the SQL Server for the job to run ?
sql enterprise manager is version 8.0
if so how do i do that securely ?

thanks for any help

 

[mrdenny]

It's sounding more and more like your having network issues between the servers.

Are there any firewalls, packet filters, etc between these servers? Are they in the same room, on the same network, different states, etc?

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[colinbell]

thanks denny

I've got it working but ! I had to amend the SQL server agent for non-sysadmin job running - it asked me for my windows nt details which I entered

two things

a) where has it stored my details
b) my windows ny password will have to change ! how can i amend the details I've provided to SQL

regards

 

[mrdenny]

I'm not sure where it stores it. In the registery I think. You should be able to go in there and change the settings. You might want to give it an account that doesn't have to change the password, or create another account specifically for this reason.

Denny

--Anything is possible. All it takes is a little research. (Me)

 

[colinbell]

thanks for all your help denny

 

[mrdenny]

no problem

Denny

--Anything is possible. All it takes is a little research. (Me)