Re: remotely call a DTS

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

Hello symoans@xxxxxxxxx,

The U and P have nothing to do with the internal package. They are the logon to the server to retrieve the package. Once the package is retrieved then you will either be using trusted or SQL credential you typed in at design time.

When you ran it on the server, you actually went to the server itself and did this? You logged in as you?

How do you call the package in the proc?

Have you run profiler to see who is doing the executing?

Allan


What I meant was .. the stored procedure always gets called fine, but
the DTS blows up. I get the access denied error from within the
stored procedure.

symoans@xxxxxxxxx wrote:

I have tried with both/either the U and P options or the E option.
The only time this stored procedure hsa worked was when I ran it with
the E option and ran it locally on the server. No matter how I
configure the call to the DTS I can not get it to work remotely. Are
there any resources for help on how all this windows security stuff
works ... I think thats whats throwing me off.

Ideally I would like to run the DTS using the U and P switches with
the SQL user associated with that DB. However, I think there is some
authentication problem regarding the windows user from the remote
machine?

really, I am completely stuck so any advice on how to proceed would
be great!

Allan Mitchell wrote:

Hello symoans@xxxxxxxxx,

The U, P and E switches are for you to be able to access the SQL
Server to retrieve the package. Is this where you fall over?

Allan

Allan and all,

So far I tried calling the SQL Server remotely from VB using the
LoadfromSQLServer which did not work. I am getting the "access
denied" message that I see many users get. I also tried calling
the DTS from within a stored procedure which again gives me an
access denied message (when I use the /U /S options). If I setup
the stored procedure as /E to run as windows authentication and
execute it locally on the SQL Server, everything works fine.
However, the users who will be remotely using this procedure can
not have Admin. access on the domain, is there a way to allow these
users to run the stored procedure AND DTS?

Thanks,
Jason
P.S. My basic problem is calling a DTS from Excel on a remote
machine.
If there is an easier way then I'm all ears ...
Allan Mitchell wrote:
Hello symoans@xxxxxxxxx,

You could simply use the DTS Object model.

have a look here for some ideas

Execution
(http://www.sqldts.com/default.aspx?104)
Allan
Is it possible to remotely call a DTS with VB6.0 embedded in an
Excel application? The Excel application is on Machine A and the
SQL DB is on Machine B. They are on the same network in the same
domain.

I assumed it was and would be fairly easy to implement, but I can
not get it working. Do I need to use DCOM with a service running
on machine B?

Basically, whats the easiest way to run this DTS?

Thanks!



.

Relevant Pages

  • Re: remotely call a DTS
    ... Allan Mitchell wrote: ... Denied on something inside the package. ... This worked when I was logged into the server and ran it from my ... but the DTS blows up. ...
    (microsoft.public.sqlserver.dts)
  • RE: JOB fails immediately
    ... We have a new server outside of our LAN and I have the exact same problem. ... > I created a dts package that when manually run, ... > Help file: sqldts80.hlp ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS in VB: system hanging after SP4 installed
    ... SP4 of what? ... I am not sure about the setup of the package though? ... You have DTS installed where? ... You pick the package up from that server? ...
    (microsoft.public.sqlserver.dts)
  • RE: DTS fails to import data when called from VB6
    ... DTS package within SQL Server it is saved as the current user. ...
    (microsoft.public.sqlserver.dts)
  • Re: DTS Tasks running in job random failure
    ... then uninstalled (created a unforseen problem on the server). ... lookup, which I'd like to follow Microsoft's advice for a workaround to "not ... Each step executes a separate DTS ... Job now running is one Job executing one DTS Package that executes 6 DTS ...
    (microsoft.public.sqlserver.dts)