Hi,

I want to execute a DTS in VB and found that there are few ways to do that:
1. Create the DTS and schedule the package and use msdb..sp_start_job to
execute the DTS
2. Use DTS.Package2 LoadFromSQLServer
3. (I havn't try this)
Use DTS.Package2, DTS.Task to create the task from a dts file

for method 1, since my VB connection use application role and can't access
the msdb..sp_start_job unless another connection is used. Or is there a way
to temporary grant access right to access msdb..sp_start_job?

for method 2, this seems work however the connection cannot use the current
VB connection. The VB connection and the DTS LoadFromSQLServer are two
different connection and result in transaction issue. Is there a way to use
the same connection for VB and DTS?

for method 3, I havn't try it, will this method have the same transaction
issue in method 2?

Thanks in advance!
Martin

Hello Atenza,

#1 If you are using application roles then #1 is out. #1 is not really
what I would do anyway as I like to have Agent fire jobs and only Agent.
Not my apps.

#2 In what way do you want to use your current VB Connection? A DTS package
is a unit of work that you Execute(). It goes off and does its thing. Sure
you can change some stuff in there with VB. I am not sure what you want
to do here.

#3. I am not sure what you want to do in this example and because of this
#2 is still my favourite.


Thanks

Allan

Allan, thanks for your advice, I've idea how to solve the issue. Thanks


"Allan Mitchell" <allan (AT) no-spam (DOT) sqldts.com> wrote