All,

Background:

I have an MS Access 2000 DB that launches a DTS package in SQL Server
via VBA code. (N.B. The SQL Server is the back end for the Access
DB.)

The two applications both sit on the same server and when the Access
db code is run directly from the server the DTS will run fine.
However, users cannot access this server and only have shortcut links
to the database on another server. Note: The database usually runs
perfectly across this shortcut link.

Problem:

The VBA code that launches the DTS does not work when the Access
application is run via the shortcut (across servers). It fails at the
first step in the DTS with "Error: -2147467259" and then all other
steps fail with "Error: 0".

The source of the initial error is "Microsoft OLE DB Provider for SQL
Server"; with description "[DBNETLIB]{ConnectionOpen(Connect()).]SQL
Server does not exist or access denied."

Can anyone please help me with this? / Any ideas?

Thanks,

Chris

In message <63b98d1c.0408240710.64efc9e1 (AT) posting (DOT) google.com>, Chris
<chrissmith_76 (AT) yahoo (DOT) co.uk> writes
Are you happy with execution location of DTS, which may not be a server
since DTS is a client side tool, so it is running on the same machine as
Access. From your description it sounds like the packages will end up
running on the user's desktop which sounds like a bad idea to me. This
will cause performance and security headaches, as well as what looks
like a name resolution problem from the "SQL Server does not exist or
access denied.".

--
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com

PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Darren,

Thanks for you answer, which I sounds like it may be the problem. Not
only does the error message appear but the performance is much, much
worse (upto the error).

Is there a way of triggering a DTS package in SQL Server (so that the
package runs on the SQL Server machine) from an Access database using
a client side machine?

Alternatively, is there a way of scheduling the DTS to run
continuously (i.e. more than just once a minute) without having to
worry about it stopping unexpectedly?

Thanks for any answers,

Chris


Darren Green <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote

Chris,

The easiest way to get a package to run on the server is to go via T-SQL.
You can use the object model via the OLE stored procedures, or DTSRUN via
xp_cmdshell, both from any T-SQL. I often prefer in some ways using a job.
This of course uses DTSRUN, but is asynchronous which can be beneficial. The
user just calls sp_start_job, or can use SQL-DMO to do the same thing. Often
the problem is also the permissions allowed to a user, which may not be
sufficient to run xp_cmdshell for example or start a CmdExc job. In this
case the user can insert a row into a table that flags the package for
execution. The regularly scheduled job can then detect this and run the
package, without any direct user call, and hence security issues. Just some
ideas for you.

You could write a never ending DTS package, but I'm not sure that would be a
good idea. To do this you would "loop" inside the package. Some articles on
SQLDTS.com that demonstrate the loop concept and practices.

If you are worried about a job stopping, then just schedule it regularly
perhaps?

--
Darren Green
http://www.sqldts.com

"Chris" <chrissmith_76 (AT) yahoo (DOT) co.uk> wrote

Darren,

Thank you again for your input.

I have got around the original problem using the SQL Server Agent Jobs
idea that you mentioned.

The solution I have used (in case anyone would like to know) is:

1. Set up a job for the DTS package and remove any scheduling.
2. Create a new table with one cell which can be Yes or No (0 or -1)
3. Set up a trigger on this table so that any updates to the table
starts the aforementioned job using:
EXEC msdb.dbo.sp_start_job @job_name = '(Job Name Here)'
in the trigger.
4. In Access VBA code, I have a simple procedure that updates the new
table with 0 or -1 (from -1 or 0 respectively) when a button is
clicked in the front end.
5. The update to the table sets off the trigger, which starts the job
that executes the DTS package.

I've tested it and it seems to work fine.

Thanks again for all your help; I've been looking for a solution to
this for months!

Chris.


"Darren Green" <darren.green (AT) reply-to-newsgroup-sqldts (DOT) com> wrote