Re: Problems launching DTS package from Stored Procedure
From: ATJaguarX (mrjboner_at_yahoo.com)
Date: 12/15/04
- Next message: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- 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: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- Reply: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 15 Dec 2004 12:18:16 -0600
The following file does not exist on our SQL Server:
"C:\Program Files\Microsoft SQL Server\80\Tools\BINN\SQL Server Enterprise
Manager.MSC"
even though "DTSRun.exe" can be found in the same directory. This is the
3rd company I've worked for that does not have EM installed on the SQL
server. That is why I ask, because I've never seen EM installed on the SQL
Server.
"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: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- 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: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- Reply: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
