Re: remotely call a DTS

Hello symoans@xxxxxxxxx,

And what are the credentials used for the Connection Manager associated with that ExecuteSQL task?

Allan

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)