Re: Problems launching DTS package from Stored Procedure
From: ATJaguarX (mrjboner_at_yahoo.com)
Date: 12/15/04
- Next message: Bish: "DTS Variables"
- Previous message: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- In reply to: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- Next in thread: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 15 Dec 2004 12:25:05 -0600
Could it be that we are running the MSDE version?
"Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in message
news:OcMfN9s4EHA.3644@tk2msftngp13.phx.gbl...
>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
>> >
>>
>>
>
>
- Next message: Bish: "DTS Variables"
- Previous message: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- In reply to: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- Next in thread: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
