Re: remotely call a DTS

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: 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: DTS schedule problem
    ... I found out that the package, sql server agent were running under the SQL ... Who owns the scheduled SQL Agent job? ...
    (microsoft.public.sqlserver.dts)
  • Re: SQL DTS Job Status
    ... your DB and have the DTS task run an Execute SQL task to write a record to ... Another option is to set up an Operator in SQL Server and have the SQL Agent ... > some of the data that was loaded from DTS. ...
    (microsoft.public.vb.general.discussion)
  • Re: SQL DTS Job Status
    ... your DB and have the DTS task run an Execute SQL task to write a record to ... Another option is to set up an Operator in SQL Server and have the SQL Agent ... > some of the data that was loaded from DTS. ...
    (microsoft.public.sqlserver.dts)
  • Re: SSIS as part of scheduled job fails
    ... An SSIS package does not run when you call the SSIS package from a SQL ... Microsoft SQL Server Management Studio ...
    (microsoft.public.sqlserver.dts)