I receive a -2147467259 error when attempting to execute a DTS package in a
VB.Net routine. The client is running Windows XP and ther SQL Server 2000
(with SP3) is running on Windows Server 2000. I am able to execute the DTS
package manually and, from the VB app, I can read, write, delete records on
the SQL database. Additionally, I can successfully execute the
LoadFromServer method of the package and 'see' the items and steps
contained in the package.

Windows authentication is througout the process and with the LoadFromServer
working (and other types of database access, as well) I am having trouble
believing there is a security hangup. Is there a known issue related to
this scenario?

Hi Jerry,

I had the same problems. After researching and pulling out the little
hair I have left I came up with this:

Stored Procedure:
CREATE procedure stp_ExecuteDTS
--@GlobalVar1 varchar(50),
--@GlobalVar2 varchar(50)

as

declare @jid uniqueidentifier
declare @id int

-- Specify DTS to be executed
declare @dts varchar(128)
set @dts = 'SYNCHALL'

-- Initialize command
declare @cmd varchar(4000)
set @cmd = '"C:\PROGRAM FILES\MICROSOFT SQL
SERVER\80\TOOLS\BINN\Dtsrun.exe" /S "(local)" /N "' + @dts + '" /W "0"
/E '

-- Specify global variables values to be passed to DTS trough DTSRUN
--set @cmd = @cmd + '/A GV1:8="' + @GlobalVar1 + '" '
--set @cmd = @cmd + '/A GV2:8="' + @GlobalVar2 + '" '

-- Create a unique job name
--
declare @jname varchar(128)
set @jname = cast(newid() as char(36)) -- Uncomment this to ALLOW TO
RUN MULTIPLE DTS INSTANCES AT THE SAME TIME
--set @jname = 'AspxDtsJob' -- Uncomment this to ALLOW TO RUN ONLY ONE
DTS INSTANCE AT TIME (default)

-- Create job
exec msdb.dbo.sp_add_job
@job_name = @jname,
@enabled = 1,
@category_name = 'ASPX',
@delete_level = 1,
@job_id = @jid OUTPUT

exec msdb.dbo.sp_add_jobserver
@job_id = @jid,
@server_name = '(local)'

exec msdb.dbo.sp_add_jobstep
@job_id = @jid,
--@job_name = 'Test',
@step_name = 'Execute DTS',
@subsystem = 'CMDEXEC',
@command = @cmd

-- Start job
exec msdb.dbo.sp_start_job
@job_id = @jid
GO


and on the .net page normal stuff but the detail:
cmd.CommandText = "stp_ExecuteDTS"
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandTimeout = 900000

cmd.ExecuteNonQuery()
cmd.Dispose()
conn.Close()

I hope this helps.

Catch ya on the flipside,
Tony

Thanks...clearly it's not what I or probably you thought we'd end up with.
Any idea why (even though we have this workaround) we can't exectute the
package2.execute() method?

--
Message posted via http://www.sqlmonster.com

In all honesty....not a clue. I must have spent about 3 days trying and
looking at previously posted code and none of it worked. I finally got
this working...and if it ain't broke...

I think I tried 90% (if not more) of the suggestions out there.

At any rate let me know how you make out.

Catch ya on the flipside,
T

Well, after much gnashing of teeth, I am able to execute the package right
after the 'load'....alcohol was involved so it's a bit murky on exactly what
it took to fix the problem but I do know that it was 100% centered on the
client network utility settings (named pipes in particular). I honestly
believe that I have the same configuration as before but by simply deleting
all alias info and re-establishing (getting out of the car and getting back
in) it works....

--
Message posted via http://www.sqlmonster.com