Re: Remaining connections after Execute?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 03/24/04 

Date: Wed, 24 Mar 2004 20:52:50 +0000

In message <fbd6df9f.0403241150.5abf256@posting.google.com>, Geert
Doornbos <geert.doornbos@planet.nl> writes
>I'm currently running a DTS package from an Installer class (.NET) and
>the execution succeeds. There is transformation/copying taking place
>from one database to another database. Directly after executing the
>package (using package2class interop class) I'm trying to drop the
>first database in the same process space (installutil.exe). This does
>not work: SqlException: cannot drop database; currently in use.
>Execution of SQL statement 'DROP DATABASE' seems to stall, then the
>exception appears after about 15 sec. When the InstallUtil process
>ends I can drop the database.
>I've tried package/step options like 'close connection on completion',
>execute on main thread', disabled 'using a transaction', etc. but I
>still can't drop the database. I made sure the DTS package object was
>released after execution (even did a garbage collection).
>
>Anyone any suggestions?
>
>
>Thanks for your help
>
>Geert

Have you tried running a Profiler trace to see what the DTS connections
are doing?
Have you tried querying sysprocesses or running sp_who2 to see what
connections are in the DB?

Some things to try and ensure that DTS closes everything-

Use Close Connection On Completion
Call the Uninitialize method on the package after execution
Use Marshal.ReleaseComObject to release the (package) COM object. GC is
very unpredictable when playing through RCWs. 

-- 
Darren Green (SQL Server MVP)
DTS - http://www.sqldts.com
PASS - the definitive, global community for SQL Server professionals
http://www.sqlpass.org

Relevant Pages

  • RE: Problems scripting a DTS package that processes a cube
    ... Have you made sure that the "Execute on main package thread" checkbox is checked in the Options tab of the Workflow Propeties dialog for the DTS step that processes the cube? ... > The execution of the following DTS Package failed: ...
    (microsoft.public.sqlserver.olap)
  • Re: Scheduled DTS always fails
    ... DTS Packages run in the context of the calling process. ... if you run the package from Enterprise ... > The execution of the following DTS Package succeeded: ... > Total Step Execution Time: ...
    (microsoft.public.sqlserver.tools)
  • Re: Scheduled DTS always fails
    ... DTS Packages run in the context of the calling process. ... if you run the package from Enterprise ... > The execution of the following DTS Package succeeded: ... > Total Step Execution Time: ...
    (microsoft.public.sqlserver.programming)
  • TransferObjectsTask
    ... I'm writing a utility, in VB6, to create a new database for non-dba's ... My problem area is almost certainly concerned with the access rights. ... The execution of the following DTS Package succeeded: ... Package Execution Lineage: ...
    (microsoft.public.sqlserver.dts)
  • Re: Scheduled DTS always fails
    ... DTS Packages run in the context of the calling process. ... if you run the package from Enterprise ... > The execution of the following DTS Package succeeded: ... > Total Step Execution Time: ...
    (microsoft.public.sqlserver.server)