dts works but run same dts via sql job failed

hi all,
i posted this somewhere else but i think here is the right group.

i have a dts that shld write to a text file located at a mapped drive. i read somewhere that sql job does not recognise mapped drive, so i use UNC path in form \\128.1.1.1\dtsfile, which dtsfile folder is the shared folder name. i opened the folder permission to everyone.

thats abt the remote server. the sql server i am running is on windows NT, logon using Administrator to local. my sql is SQL 2000. i register the server using sa username. the server agent is start up using System account.

now the problem is when i run the dts interactively/manually, it runs succesfully. but when i run it thru SQL job it says "Access denied". Or full error is like this,

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 5 (5); Provider Error: 5 (5) Error string: Error opening datafile: Access is denied. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i really hope and appreciate if someone out there can help me out. thanks!

ps: pls let me know if more clues needed from my environment settings.The account that executes the scheduled job does not have the same permissions as you. You need the job to be run with an account with the necessary permissions (same as your perhaps) which also has a non-changing password.|||I had a similar problem which was caused by the account used by the sqlagent service. I changed it to a domain account with the appropriate privs, and the problem was resolved. Whey you exec the DTS job via the designer, it is using your credentials vs. the agent's credentials.|||thank u both for yr reply...

what do u mean by change it to domain account? i tried to start up the sql server agent using THIS ACCOUNT, which taking sql id that i created as sysadm and the name and password is the same username/password i log on to the server. the reason to do this to make the job using the same logon id as i run it thru dts designer. but now i am getting this error:

DTSRun: Loading... DTSRun: Executing... DTSRun OnStart: DTSStep_DTSDataPumpTask_1 DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 Error Detail Records: Error: 1326 (52E); Provider Error: 1326 (52E) Error string: Error opening datafile: Logon failure: unknown user name or bad password. Error source: Microsoft Data Transformation Services Flat File Rowset Provider Help file: DTSFFile.hlp Help context: 0 DTSRun OnFinish: DTSStep_DTSDataPumpTask_1 DTSRun: Package execution complete. Process Exit Code 1. The step failed.

i think my problem now, i am not sure when running the dts thru job, from where the job is taking username/password to connect to the remote server location. appreciate further help...thnks much.|||hi again...

i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.

how to check the SQL Server service account?

really need yr expertise here..thanks.|||hi again...

i came across some suggestions on the net like this:
Make sure that the SQL Agent uses the same account as SQL Server service.

how to check the SQL Server service account?

really need yr expertise here..thanks.

start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.|||start -> run -type 'services.msc'
Find the SQL Server and SQL Agent services, right click, properties, and go to the login tab.

hi again...
i already changed the sql services to use logon username/password to be the same as sql agent. means both sql agent and sql services are using domain account now, no longer system account. but unfortunately, i am stil getting the same error...

anymore ideas can help me out here? please please...

tqvm.|||hi all again...
maybe i forgot to tell that my remote server i am connecting to is UNIX server (is that made a difference anyway?). well...just to test out, i just gave it a try to point to another remote server running on Windows. i shared the same folder and give sufficient privs while my sql server is stil using same domain account for sql agent as well as MSSQLSERVER services. then i run the job, it run successfully!

1.now i wld like to ask yr expertise here to guide me - how do we share a folder in UNIX to enable another server to connect to it? right now i just give permission read/write/execute to the folder. and it seems giving the above error - logon error/bad username or bad password.

2.how do we give permission to a server running on local? (not on network). the reason i asked is - normally its easy to give permission to some users on same network. is it by IP will work?

hope u all out there can help! thanks...really appreciate yr help.

DTS to transfer data from different database failed

My SQL server is SQL 2000. The DTS package uses SQL anthentication to
transfer data from database A to database B. It fails. The error message
shows Windows NT user or group 'A\Guest' not found.
In DTS package, on Copy SQL Server Objects Task Properties screen, if I
don't select Create destination objects, copy data works. However the
destination tables do not have keys adn indexes.
How can I fix it? Any thoughts? Thanks.Hi
I am not sure why it will allow you to do this assuming that you use the
same users to authenticate when using copy objects and copy tables!
You may want to try scripting the tables including the indexes/pk/fks and
create them before populating them with data. If you have FKs then you will
need to load the data in the correct order if you don't want to
disable/enable the constraint.
John
"Sean" wrote:

> My SQL server is SQL 2000. The DTS package uses SQL anthentication to
> transfer data from database A to database B. It fails. The error message
> shows Windows NT user or group 'A\Guest' not found.
> In DTS package, on Copy SQL Server Objects Task Properties screen, if I
> don't select Create destination objects, copy data works. However the
> destination tables do not have keys adn indexes.
> How can I fix it? Any thoughts? Thanks.
>
>|||Please correct me if I am wrong. When I use SQL authentication for
destination database in DTS package when using copy objects and copy tables,
I thought it should be allowed. Otherwise, how do I make it work? Is there
anything missing?
Thanks.
"John Bell" wrote:
> Hi
> I am not sure why it will allow you to do this assuming that you use the
> same users to authenticate when using copy objects and copy tables!
> You may want to try scripting the tables including the indexes/pk/fks and
> create them before populating them with data. If you have FKs then you wil
l
> need to load the data in the correct order if you don't want to
> disable/enable the constraint.
> John
> "Sean" wrote:
>|||Hi
If you have permissions to create the table then you should be able to use
either method regardless of whether you use SQL Authentication or Windows
Authentication, therefore I am not sure why the copy tables worked and the
copy objects did not (assuming that you did not create the tables first). Yo
u
may want to use SQL Profile to see what is being executed on each of the
servers.
By pre-creating the table you will only need to insert the new data.
John
"Sean" wrote:
> Please correct me if I am wrong. When I use SQL authentication for
> destination database in DTS package when using copy objects and copy table
s,
> I thought it should be allowed. Otherwise, how do I make it work? Is there
> anything missing?
> Thanks.
> "John Bell" wrote:
>|||I agree with you. I assigned public and db_owner role for the SQL account on
database B. I can use that SQL account to create SQL object on database B
manually. The thing I am not sure is, when I run DTS package, why SQL serve
r
chooses 'A\Guest' account on database B.
By the way, how can I use SQL Profile to see what is being executed on each
of the servers?
Thanks.
"John Bell" wrote:
> Hi
> If you have permissions to create the table then you should be able to use
> either method regardless of whether you use SQL Authentication or Windows
> Authentication, therefore I am not sure why the copy tables worked and the
> copy objects did not (assuming that you did not create the tables first).
You
> may want to use SQL Profile to see what is being executed on each of the
> servers.
> By pre-creating the table you will only need to insert the new data.
> John
> "Sean" wrote:
>|||John,
I figured out why the DTS package did not work by using SQL Profile. I have
a user 'A\Guest' on datatase A. That account could not be created on databas
e
B. After I removed that account, the DTS package works.
Thanks for your help.
"John Bell" wrote:
> Hi
> If you have permissions to create the table then you should be able to use
> either method regardless of whether you use SQL Authentication or Windows
> Authentication, therefore I am not sure why the copy tables worked and the
> copy objects did not (assuming that you did not create the tables first).
You
> may want to use SQL Profile to see what is being executed on each of the
> servers.
> By pre-creating the table you will only need to insert the new data.
> John
> "Sean" wrote:
>