DTS scheduled job fails on VFP 7 table. Runs fine in SEM
-
My problem seems to be a combo of two items already here.
My DTS package runs FINE when I manually run it from SQL Enterprise Manager, and fails every time it is scheduled to run. I'm importing Visual Fox Pro tables (ver 7) from a third party program (predictive dialer). The error message says "... table not available or is non-existent. Check the table name..." etc.
I've tried it as ME and as ADMINISTRATOR. I'm the dbo of the database in BOTH VFP and SQL. I've made sure there are NO OTHER processes running at that time of day. Using a ODBC FoxPro driver selected from the DTS Wizard. Both databases live on the same machine, so there is no network issue. AND IT RUNS every time I manually run it!
I'm going on vacation. I don't want to login from Maui and have to run this stupid thing while I'm gone
-
You say that you are dbo of both databases, but does the login that SQL Server Agent runs as have permissions in the VFP database? Scheduled jobs run in the security context of the agent account.
Greg
-
Greg; thanks for the reply. Here is the error message:
Executed as user: PRG\Administrator. ...OnStart: -- user is Administrator.
Create Table [TIGERSBE].[dbo].[tblTSRBookings] Step DTSRun OnFinish: -- deletes and
Create Table [TIGERSBE].[dbo].[tblTSRBookings] Step DTSRun OnStart: -- recreates table
Copy Data from results to [TIGERSBE].[dbo].[tblTSRBookings] Step DTSRun OnError:
Copy Data from results to [TIGERSBE].[dbo].[tblTSRBookings] Step, Error = -2147217865(80040E37)
Error string: [Microsoft][ODBC Visual FoxPro Driver]File 'results.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 'results.dbf' does not exist.
Error source: Microsoft OLE DB Provider for ODBC Drivers
Help file: Help context: 0
DTSRun OnFinish: Copy Data from results to [TIGERSBE].[dbo].[tblTSRBookings]
Step DTSRun: Package execution complete.
Process Exit Code 1.
The step failed.
Now, the ODBC driver is the one selected in the DTS Wizard. I've made sure the File DNS is available to the login of Administrator, and I've logged in as Administrator and created the job. It still does not run. On schedule, that is. It runs manually just fine.
Any other ideas?
-
I'm having to rack my brains on this one as we had the same problem, but in reverse, exporting to a VFP 6 table.
Two things to try.
1) If you are running the EM from a local machine, make sure that the ODBC is set up on the SQL box as well, as this is where it will run from.
2) We discovered that we were getting file not found errors as well, and discovered that although we had set up an ODBC pointing to the VFP table/database, it was still looking for the database in the wrong location. The way we had to do it was to set up the connections via ODBC as normal, but where we ran insert or selects referring to the table, we had to use the exact path to find it. i.e. Select From G:\VFPData\Application\Results.dbf
I'm not sure that this will help.
When do you go on holiday? If I've got time, I can try and set up a simulation here and help you track the fault down.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion. -
It may help to create the query using a unc path
select * from \\server\vfpdata\application\results.dbf
then you don't need to be concerned about drive mappings.
-
Rayven;
It does seem like it's a permissions thing. I'm experimenting with the PRG/Administrator login. The ODBC path though is set to the "C:\Program Files\Common Files\ODBC\Data Sources\Confirmations.dsn" path for the File DNS.
The two databases live on the same machine. I had no idea there would be so much trouble making them talk to one another.
DGHagan;
Where does one set the unc path in the DTS package? I've opened the connection up, and I can choose User/System DSN or File DSN. I've always chosen File DSN. And the path is set to the "C:\Program Files\Common Files\ODBC\Data Sources\Confirmations.dsn". Are you saying I can change this path?
-
Hmm, I tried simulating this over the weekend, and with one exception, got a working model. the difference is that I use the System/User DNS connections rather than File DNS connections.
Try this.
Create a SYSTEM ODBC connection using the visual foxpro driver to the point to the VFP database container both on your workstation (if you run EM remotely) AND on the SQL Server box.
Change the connections on your DTS package for the VFP connection to use a System/User DNS and select the ODBC name.
Try and run the job from the EM, both on the workstation and server, both should work.
Now execure the SQL Agent Job which is scheduled to run the task and it should work.
Let me know if this works or fails.
---------------------------------------
It is by caffeine alone I set my mind in motion.
It is by the Beans of Java that thoughts acquire speed,
the hands acquire shaking, the shaking becomes a warning.
It is by caffeine alone I set my mind in motion.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply