Opening access using DTS

[Jusenkyo]

Hello All

I have a DTS package which runs an excecute process task to open an access macro, which sends an email, and then closes down.
When excecuting the DTS package, the macro runs perfectly. However, when scheduled as a job, the process hangs and times out.
The SQL server agent is started with my login details, but there still seems to be some permissions issue when running the commandline excecute process task.

Anyone got any ideas?
J

 

[jhall01]

Is the access database on your local machine?

When running a DTS through the Designer you are executing locally (unless the Designer is opened on server itself)

When you run the DTS through a Job it is being executed on the server. If you defined the Access connection to your local machine the Server doesn't know where it is.

If this is not the problem check to see who the owner is and make sure they have permissions to the access database.

Jon

 

[Jusenkyo]

The database is on the server, and I have a personal edition of SQL server on my PC which I am using to test this package.
As the SQL server is started using my domain login and password, and the DTS package runs fine, I can see no reason why it doesnt run from the agent.

??

 

[jhall01]

Just to better understand your setup:

You have an ACCESS DB on a server, SQL Server on the server & SQL Server on the client machine(test server)

The client is connecting to the Access DB on the server when running from DTS designer. right?

When you schedule a job to execute the DTS on the client (test server) to connect to the Access DB on the server it doesn't work.

The Job owner is set to your login correct?

Sorry, but i was a little confused on your setup. Please detail your setup if this is incorrect.

 

[Jusenkyo]

Yep, that is all correct, very well understood!

 

[jhall01]

Is it timing out at the Access DB step? or somewhere else?

Have you tried changing the owner to sa?

How are you accessing the Access DB?

Sorry for all the questions!

 

[Jusenkyo]

There is only one step in the Job, which is to open access through an execute process task. Which has the following paramaters.

WIN32 process : <b>C:\Program Files\Microsoft Office\Office10\MSACCESS.EXE</B>

Parameters : <b>"E:\Share\Economy Select\BTS\Chris Chare\testmacro.mdb" /x Macro1 </b>

Ive tried setting the job owver to sa, to no avail.

I have read on other sites that SQL server canot start an unnatended instance of MS Access, does anyone knows if this is true?

Anymore ideas?

Cheers

 

[Jusenkyo]

sorry - ignore the <b> and </B>!
Trying to use BOLD

 

[jhall01]

Actually there is another post that is having the exact same problem. They are trying to open an access database with SQL Server with no luck either:

http://www.tek-tips.com/viewthread.cfm?qid=989545&page=1

sounds like it may not be very easy to do.

Why do you have to connect to the access DB to email. If it is to receive the address why not import the address table into SQL and use xp_sendmail or get xp_smtp_sendmail (my preferred method)

xp_smtp_sendmail:

http://sqldev.net/xp/xpsmtp.htm

 

[Jusenkyo]

Well, The actual task that access runs is not important, the email thing is just a test.
I actually want to run macros that do various calculations that we havent put onto sql server yet.

I have the same problem as the guy on the end of that link... so it looks like its a known pain in the arse to get working.

Anyone else got any bright ideas?