Re: Problems launching DTS package from Stored Procedure
From: Darren Green (darren.green_at_reply-to-newsgroup-sqldts.com)
Date: 12/16/04
- Next message: ScubaD: "Referencing objects on linked server"
- Previous message: oriki9999: "Re: DTS from Excel with empty cell"
- 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: Thu, 16 Dec 2004 08:09:30 -0000
Not too suprising as I think teh problem is with file permissions to the
FoxPro data. Go back to trying to run the pakage on the server (DTSRUN etc)
with logging as well.
-- Darren Green http://www.sqldts.com "ATJaguarX" <mrjboner@yahoo.com> wrote in message news:%238un8Wu4EHA.2568@TK2MSFTNGP10.phx.gbl... > I've added all the error events (which includes the exception event) and > does not generate any errors. > > > "ATJaguarX" <mrjboner@yahoo.com> wrote in message > news:%23AXhrNu4EHA.3092@TK2MSFTNGP10.phx.gbl... > > unfortunately, I'm not very skilled with the sql profiler > > > > "Darren Green" <darren.green@reply-to-newsgroup-sqldts.com> wrote in > > message news:UHU2ncE61IwBFwcE@sqldts.com... > >> 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: ScubaD: "Referencing objects on linked server"
- Previous message: oriki9999: "Re: DTS from Excel with empty cell"
- 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
|
