I created a SQL 2000 database using an Access Project (ADP) in Access 2002.
In this ADP I linked (created views) to several Access 97 tables in an MDB
file located on a separate server. I set up SQL tables containing the same
columns as the Access 97 views, to store the data imported from Access 97.
Each night I would like to run two jobs in SQL Server which would first
empty the SQL tables, then re-populate them by appending records from the
Access 97 views in SQL.
The stored procedure (delete query) that I created in SQL works well, both
manually and from a scheduled SQL job. However, the append query only works
manually from the SQL query analyzer or from within Access 2002. The SQL
job that I set up for the append fails every time. The stored procedures
that I am using in the job steps in the two Transact-SQL jobs are:
For the delete:
Execute StoredProcedure_Delete_StatusTypes
Go
For the append:
Execute StoredProcedure_Append_StatusTypes
Go
I thought the problem might lie with permissions, but I have given full
rights to the user, including database owner rights.
I would appreciate any thoughts on why the append query is failing while in
job mode.
Bill