Hello -
I'm running SQL Server 2000 and I've built 3 OLAP cubes that are
refreshed via DTS package, including an Analysis Services task to
re-process each cube. Now, I'd like to provide the ability for select
users to refresh/re-process the cubes without installing SQL Server
EM, Analysis Services on the client machine. Is this possible?

I thought that I could do this with DTSRun, so I reviewed the
redist.txt and have built a small Zip file with a batch file to
register the DTS dll's and copy the rll's to the appropriate directory
on the client machine.

I performed the zip "install" and register tasks on a client machine
without AS or EM. Now, when I run the batch file that calls DTSRun to
execute the DTS package, I am getting the following errors:

DTSRun: Loading...

Error: -2147221005 (800401F3); Provider Error: 0 (0)
Error String: Invalid class string
Error Source: Microsoft Data Transformation Services (DTS) Package
Help File: sqldts80.hlp
Help Context: 713

I've read some of the other postings, but not sure if they apply
directly to my case. Bottom line question: Is it possible to build a
tool for non SQL Server/AS client machines to run a DTS package that
reprocesses cubes?

Thanks in advance.
Jeff

In message <279e1d30.0404020827.60e8ba2e (AT) posting (DOT) google.com>, Jeff
Lockard <jlockard (AT) rwd (DOT) com> writes
No because DTS is a client side tool, and as you know you need to
install the DTS DLLs on the local machine. So the DTS package is running
on that local machine from where it is called. The error is probably
because you haven't installed the OLAP processing task on that machine,
so the package load fails.

You need to abstract the package execution location from the user, The
package needs to run on a machine with all the required dependencies,
best place is probably the server. Perhaps one method is to allow the
user to start a SQL Agent Job that runs the package. You only need ADO
for example to call the sp_start_job stored procedure. How you provide
this as an interface is up to you, but both Win form and web form
methods could easily be used.

Make sense?

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

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

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

Darren –
What you say about the DTS Package failing on my client makes sense –
I have intentionally not installed EM or AS on that machine.

I think I understand your suggestion about abstracting the package
from the user. I have created a job to run the DTS package on the
server. However, I have not been able to get the job to run when
connected using sa. The job step is "DTSRun" followed by a long
string of alpha characters. Any suggestions on this?

What I was originally thinking was that I could use ISQL to call
sp_start_job, but then I realized that I'd probably need to install
ISQL on the client machine to make that work. Can you educate me a
little on what you meant when you were referring to ADO? I think I it
stands for Active Data Objects, but not sure of what you were
suggesting about implementing a web form or Win form.

Thanks…

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

Darren –
What you say about the DTS Package failing on my client makes sense –
I have intentionally not installed EM or AS on that machine.

I think I understand your suggestion about abstracting the package
from the user. I have created a job to run the DTS package on the
server. However, I have not been able to get the job to run when
connected using sa. The job step is "DTSRun" followed by a long
string of alpha characters. Any suggestions on this?

What I was originally thinking was that I could use ISQL to call
sp_start_job, but then I realized that I'd probably need to install
ISQL on the client machine to make that work. Can you educate me a
little on what you meant when you were referring to ADO? I think I it
stands for Active Data Objects, but not sure of what you were
suggesting about implementing a web form or Win form.

Thanks…

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

Darren -
I did manage to get the SQL Server Agent job running, but I can only
get it to run when the owner is sa. I was trying to change it to a
generic SQL login/user to avoid sa compromise. I am going to look at
the database and table permissions to see if my generic login/user has
the appropriate permissions, but wanted you to know that I got it
working. Still would appreciate your further input on how to allow my
users to execute this job (e.g., ADO, web forms, Win forms).

Thanks...Jeff

On 5 Apr 2004 13:38:24 -0700, jlockard (AT) rwd (DOT) com (Jeff Lockard) wrote:

Jeff, you have a lot of options on how you kick off the package. The
way we do it is to have a web page call a stored procedure. That SP
runs the job. The job has to be scheduled, but we schedule it in the
past to run once, so we don't have conflicts. We are not using .Net,
only vanilla ASP. You can use ASP.Net, you can create a VB6
application that uses ActiveX Data Objects (ADO), or probably a
million other methods. Probably the best thing is to pick one that
you have the capability of using, and then, if you need more help,
asking specifica questions about that method. For example, we do not
put .Net on our servers yet, so we can't use ASP.Net. You may not
have VB. Someone else may have both, but not have the knowledge.
Nobody here can tell you which to do, because we don't know your
situtation. If you are interested in the method we use, I could share
some with you.

Hi Jeff,

I am reviewing your post and since we have not heard from you for some
time, I want to know if you still have question that need our help. For any
questions, please feel free to post your message here and we are glad to
help.

Best regards

Baisong Wei
Microsoft Online Support
----------------------------------------------------
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

In message <279e1d30.0404051238.5e6bbf8c (AT) posting (DOT) google.com>, Jeff
Lockard <jlockard (AT) rwd (DOT) com> writes
Jeff, by default only sysadmin users can execute jobs that have CmdExec
steps in them. To change this create a proxy account and set the options
in the SQL Agent Properties dialog.

To avoid this hassle abstract the users from the job itself. Perhaps use
ADO to run a SQL statement that inserts a row into a table. Create a job
that executes regularly, to check for the row in the table. If found
then, delete the row and start the other job. Both jobs can be sysadmin
owned as the users never start them directly.


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

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