 | |
Problems launching DTS package from Stored Procedure
microsoft.public.sqlserver.dts microsoft.public.sqlserver.dts
 |
| | Thread Tools | Display Modes |
#1
 
| |||
| |||
|
Problems launching DTS package from Stored Procedure -
12-14-2004
, 04:24 PM
#2
| |||
| |||
|
|
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. |
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
#3
| |||
| |||
|
|
In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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 |
#4
| |||
| |||
|
|
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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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 |
#5
| |||
| |||
|
|
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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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 |
#6
| |||
| |||
|
|
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 (AT) yahoo (DOT) com> wrote in message news:%233AKy1s4EHA.3124 (AT) TK2MSFTNGP11 (DOT) 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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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 |
#7
| |||
| |||
|
|
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 (AT) yahoo (DOT) com> wrote in message news:%233AKy1s4EHA.3124 (AT) TK2MSFTNGP11 (DOT) 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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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 |
#8
| |||
| |||
|
|
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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:OcMfN9s4EHA.3644 (AT) tk2msftngp13 (DOT) 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 (AT) yahoo (DOT) com> wrote in message news:%233AKy1s4EHA.3124 (AT) TK2MSFTNGP11 (DOT) 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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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
#9
| |||
| |||
|
|
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 (AT) yahoo (DOT) com> wrote in message news:%233AKy1s4EHA.3124 (AT) TK2MSFTNGP11 (DOT) 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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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
#10
| |||
| |||
|
|
Well I'm confused. Not a big MSDE man myself, but a SELECT @@VERSION may reveal the edition as MSDE, I don't know. It will say Standard or Enterprise at least if it is full SQL Server. If you are running MSDE then where did the EM you develop with come from? EM is not licensed as a standalone tool for MSDE development. How about running the package, logged onto the server, through DTSRUN or DTSRUNUI, and maybe also set the package log files, and log to SQL Server options. What I would do is try and replicate the environment of the scheduled job as close as possible, which means on the server under the service account, and then use as many UI or logging options as possible to get a decent error message out to try and deduce the cause of the problem. Cheers Darren In message <OxzMEFt4EHA.4004 (AT) tk2msftngp13 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) com> writes 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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:OcMfN9s4EHA.3644 (AT) tk2msftngp13 (DOT) 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 (AT) yahoo (DOT) com> wrote in message news:%233AKy1s4EHA.3124 (AT) TK2MSFTNGP11 (DOT) 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 (AT) reply-to-newsgroup-sqldts (DOT) com> wrote in message news:N4NA4OBi42vBFwks (AT) sqldts (DOT) com... In message <uIbX$pi4EHA.3420 (AT) TK2MSFTNGP10 (DOT) phx.gbl>, ATJaguarX mrjboner (AT) yahoo (DOT) 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 |
|
« Previous Thread
|
Next Thread »
| Thread Tools | |
| Display Modes | |
Linear Mode
| |
Powered by vBulletin Version 3.5.3
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.
Copyright ©2000 - 2012, Jelsoft Enterprises Ltd.