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 |
|
minju
Starting Member
1 Post |
 Posted - 2005-10-21 : 21:30:28
|
| I have a dts job to copy data from excel to sql server table. Here the excel file is stored in a shared folder in another system than the server. And i have configured the excel connection to the file in the shared folder. When i execute the DTS manually from Enterprise manager it works. But when i am trying to execute DTS from a stored proc its failing. But when i save the excel in the local system's c drive, then the execution thru stored proc works. Please help me on this as i've been stuck with this for days |
|
|
Mariory
Starting Member
5 Posts |
Posted - 2005-10-24 : 08:16:43
|
| DTS is a client utility program. So when you run it from SEM, it runs under your account from the machine you are siiting at or the remote location you are logged onto.The most likely problem you are experiencing (just like me) is that of permissions. When you run the DTS from a stored procedure, you are running it from the sql server machine in the security context of the SQL Server Service. This in most cases is the SYSTEM account. The SYSTEM account is local and so cannot be authenticated on a network. As a result, it has no access to network shares.The solution I am currently trying with my problem is to start the SQL Server Service using a domain account rather than the system account. If you use this solution, make sure that the domain account has access to the SQL Server Database you are importing the file to and also to the Network share you are exporting the file from. |
 |
|
|
|
|
|
executing DTS from stored proc