Re: Problems launching DTS package from Stored Procedure

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

Date: Wed, 15 Dec 2004 17:54:09 -0000

I cannot really believe that EM is not installed on your SQL Server, I'm not
sure if you can even install SQL Server without tools, but maybe you can.
DTS for example is part of the Tools install, so unless you have manually
installed DTS, DLL by DLL, then this will certainly be a problem when comes
to running a package on that machine, since you need DTS components on the
machine to run a DTS package. In fact the sp_OACreate would certainly fail
as DTS.Package(2) would be missing. 

-- 
Darren Green
http://www.sqldts.com
"ATJaguarX" <mrjboner@yahoo.com> wrote in message
news:%233AKy1s4EHA.3124@TK2MSFTNGP11.phx.gbl...
> The SQL Service is running under the Domain Admin account which has access
> to both directories and files.
>
> I cannot run enterprise manager on our server because it is not installed.
> Can enterprise manager be installed on the server?
>
> "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in
message
> news:N4NA4OBi42vBFwks@sqldts.com...
> > In message <uIbX$pi4EHA.3420@TK2MSFTNGP10.phx.gbl>, ATJaguarX
> > <mrjboner@yahoo.com> writes
> >>I have a stored procedure create for the sole purpose of launching a DTS
> >>package.  I can post the SQL later if necessary.
> >>
> >>The problem is that I have multiple DTS packages that I would like to
> >>execute in a SP.  The sole purpose for this is that we have data stored
> >>and
> >>being used in a foxpro database and I am writing Reports and would like
to
> >>have the data in SQL Server.  So I created a SP that will accept a DTS
> >>package name as a parameter, executes the package and continues on.
> >>Well...
> >>my DTS package consists of a table drop, a table creation, a connection
to
> >>Foxpro, a query to retrieve the data and a connection to SQL Server.
> >>
> >>If I run the DTS package directly from Enterprise manager, it executes
> >>correctly.  It drops the table (if it exists), creates it, queries the
> >>data
> >>and drops it into the table.  Pretty simple considering it was all done
> >>from
> >>the "Import Wizard".  The problem arises when I use my stored procedure
to
> >>execute the DTS package.  It gets as far as dropping and recreating the
> >>table, but never retrieves the data or populates the table.  The stored
> >>procedure does not error out or give me any signs of problems.  In
> >>actuality, it works perfectly fine for a VERY similiar DTS package for a
> >>different table (which was also made from the "Import Wizard").  Both
> >>packages do the same thing (drop, create, import) except they hit
> >>different
> >>tables.
> >>
> >>Does anyone know why this would happen?  Any help would be GREATLY
> >>appreciated.
> >>
> >
> > I would not expect the store procedure to fail if there was an error
> > during package execution, instead you need to check the log or the step
> > error information post execution to find this out.
> >
> > I suspect the error is around security and location contexts. When
running
> > via the sp the package is executed under the security context of the SQL
> > Server service account. So what is this account and does it have access
to
> > the Foxpro file? The package is also being executed on the SQL Server
> > machine itself.
> >
> > The typical development scenario means, that when you "test" through
> > Enterprise Manager it executes the package under the context of you, as
> > the logged on user, and on your local workstation, not the server.
> > So taking that into account it is not normally a very valid test. To do
a
> > more realistic test you need to log into the server console as the SQL
> > Server service account and use Enterprise Manager there.
> >
> >
> > -- 
> > 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: DTS Packages fail when executed from Jobs
    ... Action Plan 2: It runs fine on the server when run from the DTS Designer. ... Expand the server that is running SQL Server in SQL Server ... > Action Plan 2 - Run the DTS Package on the server rather than on ...
    (microsoft.public.sqlserver.dts)
  • RE: Windows Install Failure, KB921896, Code 2B26 .... Summary.txt atta
    ... Download the update and install it manually. ... Package Language: 1033 ... the SQL Server Instance is set to Mixed Authentication ... if the password provided is correct or check to see if the SQL Server ...
    (microsoft.public.windowsupdate)
  • Re: DTS Tasks not there???
    ... Services Packages", goes into a package... ... I've tried re-installing SQL Server, ... Do you have the menu bar with the following. ... you performed a reinstallation but you didn't install ...
    (microsoft.public.sqlserver.dts)
  • Re: Install of SQL Server 2005 fails due to no space to extract pa
    ... Actually I am using sqleval.exe (180 day trial of the SQL server 2005). ... It went through the extracting package stage, ... location to extract to. ... tried to install that. ...
    (microsoft.public.sqlserver.setup)
  • Re: DTS Security
    ... the package due to the guest account being enabled in msdb ... where SQL Server is installed. ... >the DTS package. ... >> Protection Program and to order your FREE Security Tool Kit, ...
    (microsoft.public.sqlserver.security)
Loading