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 |
|
vwemil
Starting Member
7 Posts |
 Posted - 2003-07-02 : 15:46:28
|
| I have a stored procedure that runs simple DTS package. The DTS package that exports SQl table to .csv file on another PC. Runs fine as long as the destination is located on my local drive. However, when I modify the connection to point to a folder located on a network drive, I get the following error when running the stored procedure: DTSRun OnError: DTSStep_DTSDataPumpTask_1, Error = -2147467259 (80004005) Error string: Error opening datafile: Access is denied. Running the DTS package directly from within Enterprise Manager works fine -- the error occurs only when running the package from a stored procedure. Thanks for any help in resolving this problem. Emil |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-02 : 15:58:10
|
| Instead of using the stored procedure, go into Query Analyzer and see if you can get access to the file. Run xp_cmdshell to do this (will require sa or system administrator privileges). xp_cmdshell allows you to do commands like you would in a cmd window. For example:xp_cmdshell 'dir c:\temp'Tara |
 |
|
|
vwemil
Starting Member
7 Posts |
Posted - 2003-07-02 : 16:26:13
|
| Thank for you response.I get "Access is denied" error.exec xp_cmdshell 'c:\temp' works correctly, however when I attempt to do a directory listing of a mapped drive the "Access is denied" error occurs...exec xp_cmdshell 'g:\sharedfolder' or '\\compname\folder' both failI am logged into the primary machine as a domain admin...Emil |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-07-02 : 16:47:32
|
| Based upon the paths that you have given, you have them backwards. When you type in G:\sharedfolder, sharedfolder must be the actual folder name and not a share name. When you type in \\compname\folder, folder must be a share name and not the actual folder name. You have them backwards.BTW, just because you are a domain admin doesn't mean that you have access to everything. Someone could have removed domain admins from shares. In order to correct it, you'd have to either take care of the share or ask the owner to add you back.Tara |
|
|
|
vwemil
Starting Member
7 Posts |
Posted - 2003-07-03 : 09:28:07
|
| You are right, I just mistyped path in the example that I sent. If the path is incorrect I get “Path not found “ error and I am getting "Access is denied". I have all rights to the shared folder, it is on my second PC and I double-checked.I can go to the command prompt, change directory to the same share and look at the directory. No Problems. That is why I think that there is something in the SQL settings that needs to be changed.Whatever is responsible for running xp_cmdshell “does not have enough privilege” to connect to the shared drive. SQL server Agent…?Thank you for all your help. |
|
|
|
mr_mist
Grunnio
1870 Posts |
Posted - 2003-07-03 : 09:58:51
|
| That would be the sql server agent service login that you need to investigate.-------Moo. |
|
|
|
vwemil
Starting Member
7 Posts |
Posted - 2003-07-03 : 11:14:03
|
| I did add my Domain username as a login for server agent service .Still cannot access shared directory..Thanks |
|
|
|
vwemil
Starting Member
7 Posts |
Posted - 2003-07-03 : 13:59:28
|
| I found the solution. I changed the Logon property of the MSSQLSERVERfrom Local to Domain\Username. Restarted the sevrice. All setThanks everybody. |
|
|
|
|
|
|
|
|
DTS Problem