DTS Execute SQL task on Access linked table

Can the DTS execute SQL task recognize a table in an Access database that is linked from another Access database?

I'm attempting to use a DTS Execute SQL task connected to Access1.mdb to make a new table in Access1.mdb that is using a Subquery from a linked table in Access2.mdb . (I know it sounds convoluded..) Its giving me an error in my FROM clause as if it can't recognize the linked table from Access2.mdb.

The reason why I'm doing this is that I'm not sure how to run an Access make table query from DTS that will overwrite the existing table in Access. I get an error that the table already exists.

Any Help?You can do testing in query analyzer by placing your select * from openquery statement. If it works in query analyzer then u could execute this sql statement in dts package.

By the way, since you are using DTS package, why not use MS Access connection which is more dynamic and stable than OpenQuery.

DTS cube processing from a non sa account

We are running a DTS package from a non-sa owned job and I am getting the following message:

A problem occurred while attempting to logon as the Windows user 'SQLAgentCmdExec': The parameter is incorrect.

When I set the Sql Agent proxy account to an windows administrator on the sql server it works fine.

However when the Sql Agent proxy account is not administrator I get the above error.

Please note that the Analysis Services is located on a different machine.

Thanks

LiorGrant admin privileges to the SQLAgent service account, which is required to carry on such tasks and to overcome this issue.|||Originally posted by Satya
Grant admin privileges to the SQLAgent service account, which is required to carry on such tasks and to overcome this issue.

Do you mean that the sqlagent stratup account should be windows sys admin?

Can I do it with less power priviliges?

Thanks

Lior|||You can do it, but you may have issues again if any of the jobs have to deal with admin tasks.

Its always better and recommended to keep SQL service accounts with Admin privileges on the box.|||Originally posted by Satya
You can do it, but you may have issues again if any of the jobs have to deal with admin tasks.

Its always better and recommended to keep SQL service accounts with Admin privileges on the box.

Hi,

Thanks again for your help.

Do you know what permissions are required for sql server to run cube processing that resides on a different machine.

Lior