| Author |
Topic |
|
mdhingra01
Posting Yak Master
179 Posts |
 Posted - 2004-06-02 : 10:33:52
|
| I have a DTS Package that drops and creates a Table, imports a Foxpro DBF into that table and indexes the table. The foxpro DBF is sitting on a different server , so I created an ODBC connection to map to the DBF location. When I run the Package, it runs sucessfully. When I schedule the Package and run the job using the SQL SERVER Agent, the job fails. The job history shows: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 'paradelt.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 'paradelt.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. Process Exit Code 1. The step failed.Does anyone knoww why my package runs but the job doesn't. Thanks |
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-02 : 10:45:18
|
| When you run the DTS from your local machine, it uses the ODBC drivers on your local machine, when you run it as a job, it uses the servers ODBC drivers... You don't have the ODBC set up on the server... |
 |
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-02 : 10:47:03
|
| Check the account running the SQLSERVERAGENT service. Does it have privileges to the DBF location you have specified?Raymond |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 11:06:18
|
| RICK:How do I set up a ODBC on the server. I did physically set it up on the server where SQL SERVER is running. I also used a UNC path.Raymond:Do you mean I need to set up a SA account on the the server for where I am creating the ODBC connection for? I mean. I created a the ODBC connection on server a to access server B, so its on Server B I need to create a Server A sa account on Server B?Thanks guys. |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-02 : 11:10:17
|
| I was guessing different from Rick, as I thought the 'File does not exist' indicated that the windows account name running the SQL Agent may not have permissions to the file share on the network that the fox table is in. Nothing to do with ODBC.Raymond |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-02 : 11:11:13
|
If you did set the ODBC up on the server, ignore me... You just need to add whichever user you have set up running your SQLAgent service to be able to have full rights to the directory where the .DBF file exists... This isn't the sa account (or hopefully not).. |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 11:21:36
|
| Is there a way for me to find SQL SERVER Agent's user name and password? I am assuming its all running under sa |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-02 : 11:24:06
|
| Right-click in EM on the SQL Server Agent and choose properties. The General tab has the Service Startup Account details. This is either the local windows system account or a named winds account, it can't be sa or any other SQL Server login.Raymond |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 11:32:22
|
| The Service Startup Account is grayed out. Nothing entered there and no radio buttons engaged. Wierd! |
|
|
|
raymondpeacock
Constraint Violating Yak Guru
367 Posts |
Posted - 2004-06-02 : 11:35:39
|
| How have you connected to the server in EM, is the account a system administrator account?Raymond |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 11:41:12
|
| Yes, logged in as 'sa' in EM. |
|
|
|
RickD
Slow But Sure Yak Herding Master
3608 Posts |
Posted - 2004-06-02 : 11:57:15
|
| You really shouldn't be using sa at all...If you go to the SQLAgent service on the server, is it started? If not, you will need to start it, you may need to set an account up to use which has access to your network... |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 12:20:33
|
| That's funny :o)I think on all are servers we are loggin in as SA.I am checking if it is currently running...........Yes it is.I went to check the SQL Server AGENT on the server and it is using account: .\SQLADMIN |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 12:20:34
|
| That's funny :o)I think on all are servers we are loggin in as SA.I am checking if it is currently running...........Yes it is.I went to check the SQL Server AGENT on the server and it is using account: .\SQLADMIN |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 13:03:06
|
| Log on to the database server with the SQLADMIN domain account. See if you can get to the DBF file manually. If you can't, it's due to permissions of SQLADMIN account. It should have local admin on the database server and at least read on the DBF file on the other server. BTW, start getting in the habit of not using SA. If you are the DBA, you should be using your domain account that has been granted either sysadmin fixed server role inside SQL Server or local admin privileges. This is the recommended approach to SA type people to do their work in SQL Server.Tara |
|
|
|
mdhingra01
Posting Yak Master
179 Posts |
Posted - 2004-06-02 : 13:46:21
|
| Thanks, I will set up my own id with sys admin privlidges.I got around this problem. What I did was I created the packages on the server where the DBF were existing, therefore I don't need to have a user id and password for SQL Server Agent to access the files.I know one day I will have to set up the account for SQL SERVER Agent on this server. Right now there is already an account, but it must be for the SQL SERVER agent on that server.Do you think you can help me setting up a new user for SQL Server?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-02 : 13:51:03
|
| A SQL authentication user or Windows Authentication user on the domain? You've already got the agent setup with a Windows account.Tara |
|
|
|
|
DTS and Jobs