Re: DTS schedule problem

Russell,
Thanks for your help again. When I search for "xp_sqlagent_proxy_account"
on the Help menu, it did not show up. Forgive me for my ignorance, I image
it is a system table, but I did not find out how to access it , even though I
turned the "system files" for the database.
I found out that the package, sql server agent were running under the SQL
sys admin account, which did not have access to the source file folder that
this package was pulling file from. So I asked my network administrator to
created a domain account that had access to the whole server. I then put the
sql server agent, the job, and the package under this new domain account.
And the scheduled job ran successfully! It read the text file on the server,
and wrote the data into a sql table. Well, it did not just work by one test
job, but many many test job, each time I checked something more, and
eventually that was how it worked.
Again your suggestion is much appreciated.

Pelican

"Russell Fields" wrote:

pelican,

I suspect that you still need to better understand the SQL Agent proxy,
since I infer from your original post that you are on a SQL Server 2000
installation. Who owns the scheduled SQL Agent job? Is it your login? (I
assume that it is.) Is your login a SQL sysadmin? (I assume that it is not.)

If your login is a sysadmin, then your SQL Agent job will run under the
server account's context and will have access to everything that it has
rights to.

If your login is not a sysadmin, then your SQL Agent job will run under the
rights of the SQL Agent proxy as defined in xp_sqlagent_proxy_account, not
under your personal rights. In that case, you must also grant the SQL Agent
proxy account rights.

Actually, for a scheduled job running on the server your personal file and
domain rights are ***meaningless*** since they cannot be used. But when you
run the package yourself, it is running under your personal file and domain
rights and so then it works.

RLF
"pelican" <pelican@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:D2A73983-C074-4481-B2AD-913C9B53BDD5@xxxxxxxxxxxxxxxx
Thanks. I have tried the following:
1) the package is under the system account, which was only a SQL account.
I
asked the network admininstrator to give the system account access to the
whole server. The text file the package needs to copy from is on the same
server. I could still manually run the package successfully (which
includes
create a table, then copy the text file into the table just created). But
when it came to schedule the package as a SQL Server Agent job, it created
the SQL table, failed to copy the text file over. I got the same error
message saying "Access denied."

2). I asked the network administrator created a new domain aco*** that
had
access to the whole server. I got the same result as in 1). This new
domain
aco*** is a window's aco*** (server is NT).

What should I do next?

Any suggestion will be greatly appreciated.

Pelican

"pelican" wrote:

Hi everyone,
I tried to schedule a DTS package. I right click on the package and
select "Schedule Package." I filled in the job frequence as occuring
daily
every 20 minutes. Then I clicked "OK." Well, when I opened the
"Schedule
Package" window again, none of the values I entered was saved. I tried
several times, it just would not save my setting.
I went to look at Enterprise Help and it said that I should save the
package first in the Meta Data Service first. Well, the meta data
service
would not let me save either.
I have a feeling that something needs to be switched on before I can
schedule package or save a meta data service record. If yes, what will
that
be?

Thanks.
Pelican



.