| Author |
Topic |
|
rguthrie
Starting Member
6 Posts |
 Posted - 2003-03-13 : 16:43:17
|
| I have a dts package (sql server 2000) that imports a text file. The dts package works fine. When I try to set up the dts package as a job, I get an acess denied error. In my DTS package, I specify the directory path to go to (including the network drive, ie. \\network\shared_drive\folder1\text.txt. The user name that it is trying to access under is my user name that does have access to this network drive. Anyone set up something like this before?? |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-13 : 16:53:08
|
| Well who owns the job? The owner of the job needs to have access to that directory path. Or is it that the account that the SQLSERVERAGENT runs under needs the access? I can't remember.TaraEdited by - tduggan on 03/13/2003 16:54:36 |
 |
|
|
rguthrie
Starting Member
6 Posts |
Posted - 2003-03-13 : 16:56:49
|
| The owner of the job does have access so it must be the SQLSERVERAGENT that doesn't have access. Let me check on that one. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-13 : 16:58:49
|
| Yep it's the account that the SQLSERVERAGENT runs under. Had to check BOL for that one! The account that we use has administrator on all of the machines that it needs to connect to, so that has never been an issue for us. That's probably why I couldn't remember who needed the access.TaraEdited by - tduggan on 03/13/2003 16:59:35 |
|
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2003-03-13 : 17:09:12
|
| If you are importing a a text file why do you use dts?==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
|
|
|
rguthrie
Starting Member
6 Posts |
Posted - 2003-03-13 : 17:22:25
|
| OK, more questions:1) How do I know what account the SQLServer agent runs under?2) What else would I use other than the DTS import for importing a text file?Thanks |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-13 : 17:31:16
|
| To figure out which account the SQLServerAgent runs under, go the services applet which can be found in Administrative Tools which can be found in the Control Panel. Just double-click on the service and go to Log On. If it is configured to use the local system account, you will want to change it to a domain account that has the necessary privileges to get to other servers. You do not want to configure it with a user account such as your own account. Create a generic account for it, such as sqlservice. If there is an account specified for the service, then you will want to grant that account the necessary privileges to the directory path that you specified.To import a text file, you could also use bcp or BULK INSERT. Both are documented in Books Online. DTS is a powerful tool but it also consumes a lot of resources when it is running.TaraEdited by - tduggan on 03/13/2003 17:32:14 |
|
|
|
rguthrie
Starting Member
6 Posts |
Posted - 2003-03-13 : 17:39:25
|
| I am guessing that I have to be on the server where SQL Server is installed to see the agent. They are strict around here and I don't have direct access to the machine.I am just learing and have just discovered bcp. As I get more into things I figure I will get more advanced. Thanks for the help. |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2003-03-13 : 17:42:43
|
| Yes you will need to be on the server or use some kind of remote access software to get to it in order to see what account it is running under. You could probably just call your DBA or server administrator to find this information out. They should know exactly what you are talking about when you ask them about it.Tara |
|
|
|
|
Running Jobs that have DTS that import flat files