Ms Sql Server 2000 DTS

[weebeng]

hi, i'd like to ask about the DTS in Sql Server.
I'd create the DTS in sql server(located at server PC) to copy the records to access(located at client PC).
Then i executed the package, it is successful.
But when i scheduled the package as job, once hit the schedule time, the job failed.
Can anybody tell me the problem?
Thank you

[Claire]

Most likely it would either be permission issue or invalid file path. The person who created the DTs package has no problem in accessing (including reading and writing) to the file directory. But when you schedule the DTS package as a job. It use SQLAgent Service Account to run the dts package. So you should ensure that the SQL Agent Account has authority to the directory and also the directory is valid from SQL server point of view.

[weebeng]

thanks claire..
may i know how to ensure that the SQL Agent Account has authority to the mdb file directory?where to set this?

the error message is:

DTSRun:  Loading...   DTSRun:  Executing...   DTSRun OnStart:  DTSStep_DTSExecuteSQLTask_1   DTSRun OnError:  DTSStep_DTSExecuteSQLTask_1, Error = -2147467259 (80004005)      Error string:  The Microsoft Jet database engine cannot open the file '\\Mi-industrial\d\carpark.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.      Error source:  Microsoft JET Database Engine      Help file:        Help context:  5003051      Error Detail Records:      Error:  -2147467259 (80004005); Provider Error:  -534709256 (E020FBF8)      Error string:  The Microsoft Jet database engine cannot open the file '\\Mi-industrial\d\carpark.mdb'.  It is already opened exclusively by another user, or you need permission to view its data.      Error source:  Microsoft JET Database Engine      Help file:        Help context:  5003051      DTSRun OnFinish:  DTSStep_DTSExecuteSQLTask_1   DTSRun:  Package execution complete.  Process Exit Code 1.  The step failed. 

[Claire]

It's pretty obvious that the error msg tells you that either somebody else(or you) already open the access file. Or you do not have either write or read permission on the access file.

[skhanal]

Do you have multiple connection to the .MDB file in the package?. If yes, create just one connection and serialize all the data pump task.

[weebeng]

thanks skhanal and claire...
i think i hav write or read permission on the access file. else i cannot successful execute the package.
I failed only when i run it as job.
I think must set something in sql server agent...

[MAK]

1. See what account you use in SQL Server Agent
2. Login to any box using that account
3. try to access the ms-access file
4. If the MS-Access file is used by multiple user, make sure, nobody opens the file in EXclusive mode.

SQL JOB uses SQLAgent's account to run any package.

[weebeng]

Thanks Mak...
I'm using the System Account in Sql Server Agent.Actually what is different btw System Account and This Account?

[MAK]

http://support.microsoft.com/default...b;en-us;120929

To make the long story short "System account is for the local system".

Try to use Domain account, so that it can interact with other servers and shared folders.

[weebeng]

So in my case, to scheduled the Jobs, i must use the sql server agent's Domain Account right?
And I don't have domain here. I just have workgroup.
So I can't set the Domain Account and can't scheduled the jobs.Am I right?
Thank you

[MAK]

I dont use work group here. Every company has the whole Domain and active directory nowadays.

If you are just testing this DTS at home, copy that access file to you local folder in the server and then run the DTS.