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!

Hello symoans (AT) yahoo (DOT) com,


You could simply use the DTS Object model.

have a look here for some ideas

Execution
(http://www.sqldts.com/default.aspx?104)


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 (AT) yahoo (DOT) com,

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

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:

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 (AT) yahoo (DOT) com wrote:

Hello symoans (AT) yahoo (DOT) com,

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

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 (AT) yahoo (DOT) com,

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

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

Allan Mitchell wrote: