Re: Problems launching DTS package from Stored Procedure
From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 12/15/04
- Next message: Darren Green: "Re: DTS Variables"
- Previous message: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- In reply to: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Next in thread: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Reply: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Messages sorted by: [ date ] [ thread ]
Date: Wed, 15 Dec 2004 19:16:10 +0000
Good idea, unfortunately not very conclusive. Did you include the
Exception event as well? Kind of stuck without an error to look into.
The trace shows that it stops, but I certainly have no idea why just
from that.
Cheers
Darren
In message <OkIFhBt4EHA.1264@TK2MSFTNGP12.phx.gbl>, ATJaguarX
<mrjboner@yahoo.com> writes
>Here is an example of a SUCCESSFUL sql profiler log:
>
>----------------------------------------------------------
>
>Audit Login
>
>exec sp_oledb_ro_usrname
>select collationname(0x0904D00034)
>drop table [Users1]
>CREATE TABLE [Users1]
>exec sp_bcp_dbcmptlevel "UsersDatabase" SET FMTONLY ON Select * from Users1
>SET FMTONLY OFF exec "mpfloor_UsersDatabase"..sp_tablecollations
>'"mpfloor_UsersDatabase"..Users1'
>
>Audit Logout
>
>----------------------------------------------------------
>
>Here is an example of an UNSUCCESSFUL sql profiler log:
>
>----------------------------------------------------------
>
>Audit Login
>
>exec sp_oledb_ro_usrname
>select collationname(0x0904D00034)
>drop table [Users2]
>CREATE TABLE [Users2]
>
>Audit Logout
>
>----------------------------------------------------------
>
>You can notice after it creates the table, it hits "Audit Logout" and never
>continues on with the batch input
>
>
>
>"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
>>>
>>
>>
>
>
-- 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: Darren Green: "Re: DTS Variables"
- Previous message: Darren Green: "Re: Problems launching DTS package from Stored Procedure"
- In reply to: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Next in thread: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Reply: ATJaguarX: "Re: Problems launching DTS package from Stored Procedure"
- Messages sorted by: [ date ] [ thread ]
Relevant Pages
|
