Re: remotely call a DTS

Package starts up, the first step is access denied. First step is a
delete from a table.

Allan Mitchell wrote:
Hello symoans@xxxxxxxxx,

What DB?

You are retrieving packages from MSDB and the credentials you are passing
have to be able to get into MSDB (this is not an issue by default)

I am not sure right now whether you get Access Denied on the package or Access
Denied on something inside the package. Does the package actually start
up? Do you have logging enabled

Allan

Here is my proc

CREATE PROCEDURE sp_executeDTS AS
exec master..xp_cmdshell 'dtsrun /S SQLDB /N "UpdateDB" /E'
GO
This worked when I was logged into the server and ran it from my
application on the DB server. I was logged in as Administrator. I
changed the stored proc to

CREATE PROCEDURE sp_executeDTS AS
exec master..xp_cmdshell 'dtsrun /S SQLDB
/N "UpdateDB" /U "UpdateUSER" /P "UpdatePass"
GO
This did not work on the server. UpdateUSER/UpdatePass have full
rights to the DB. I get the same access denied message I get when I
try and run this remotely. The package was setup at the time to
accept SQL Authentication.

Ultimately I need any user on the domain to be able to run the Excel
application, start the macro, and run the DTS from any machine on the
domain.

I will try to run the profiler and get back to you on results.... any
other ideas/suggestions?

Thanks a lot!

Allan Mitchell wrote:

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: 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)
  • Re: SQL 2005, DTS, problems, problems, problems, etc..
    ... "Allan Mitchell" wrote: ... all columns mappings, and some of my tables have 50+ columns. ... I will be using the Execute 2000 Package task and leaving it at that. ... Backward compatibility/2000 DTS Components installed? ...
    (microsoft.public.sqlserver.dts)