Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
StickRod
Starting Member
1 Post |
 Posted - 2002-12-20 : 10:32:21
|
| Howdy,I have a DTS package who's job is to import text files off of a mapped network drive. The package runs fine when you execute it manually via enterprise manager. This package needs to be executed from an asp.net page, which I am doing by means of executing a stored procedure that runs the package. When running it like this however, the package doesn't work and doesn't return any errors. Here's the code from the sproc (which works if I change the dts package name and run a package that doesn't have anything to do with a mapped network drive):CREATE PROCEDURE [dbo].[sp_RunPkg4]ASDECLARE @DPack int, @PI int EXEC @PI=sp_OACreate 'DTS.Package', @DPack OUTEXEC @PI = sp_OAMethod @DPack, 'LoadFromSQLServer("SERVER", "USERNAME", "PASSWORD", 256, , , , "PFL_Import")', NULLEXEC @PI = sp_OAMethod @DPack, 'Execute'EXEC @PI = sp_OADestroy @DPackGOI am connecting to the sql server with an administrative account. Also, I've taken this code and executed it in query analyzer and put additional code in there to catch errors, and it returns no errors. Anyone have a clue? Also, I've run the same package from a sproc and had it import text files that are on the database server, and it works fine. We are strict here about having seperate servers for seperate functions, so if at all possible I'd like to avoid having to put an ftp account on the database server. All that has me thinking the mapped drive thing is the culprit, but I don't know what to do :-( |
|
|
joldham
Wiseass Yak Posting Master
300 Posts |
Posted - 2002-12-20 : 12:21:36
|
| I believe the root of your problem is that when you execute a DTS package from a stored procedure, the package is executed under the account that was used to start SQL Server instead of the account used to connect to the SQL Server. Therefore, the account used to start the SQL Server needs to have read access to the network drive it needs to read the file from and write access to any drive it needs to export data to.You can test this by trying to execute the SP from QA. You should get the same results (and probably an error message).Also, instead of using a mapped drive letter, it would be better to use the UNC path of the drive. (i.e. \\Servername\shared name instead of m:\shared name)JeremyEdited by - joldham on 12/20/2002 12:22:55 |
 |
|
|
|
|
|
|
|
DTS Mapped Drive Beef