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 |
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
 Posted - 2010-07-21 : 13:27:02
|
| I set up a linked server on my Localhost instance of SQL Server to import an Excel workbook on the network. I used an SSMS T-SQL query. It works fine. I use Windows authentication on my machine which gets me into SQL Server and gives me access to the network folder. However, when I put it into a job and invoke the job, it fails. I get the following error:07/21/2010 12:37:32,Import_MSDN,Error,0,OPTI745-23845D1,Import_MSDN,(Job outcome),,The job failed. The Job was invoked by User NOAM\X649390. The last step to run was step 1 (Step1).,00:00:01,0,0,,,,007/21/2010 12:37:33,Import_MSDN,Error,1,OPTI745-23845D1,Import_MSDN,Step1,,Executed as user: NT AUTHORITY\NETWORK SERVICE. The OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MSDN_IMPORT" reported an error. The provider did not give any information about the error. [SQLSTATE 42000] (Error 7399) Cannot initialize the data source object of OLE DB provider "Microsoft.Jet.OLEDB.4.0" for linked server "MSDN_IMPORT". [SQLSTATE 42000] (Error 7303). The step failed.,00:00:00,16,7303,,,,0Frankly, I am getting tired of linked server errors. I can easily get them to work on my Localhost version of SQL Server when I run it from SSMS, but as soon as I put the query on a server or in a job on my PC, it fails. Microsoft needs to change something here or at least document it better.Duane |
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
Posted - 2010-07-21 : 13:59:17
|
| who is that job running as and does that user have access to whatever path you are dumping the excel file to? you are duane by job is running as NOAM\X649390? that might be the problemIf you don't have the passion to help people, you have no passion |
 |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-21 : 14:22:13
|
| How do you tell who the job is running as? When I script the job out, it says the @owner_login_name as NOAM\X649390 and if it is running under that account (my domain account), I do have access to that path. Thank you answering.Duane |
|
|
|
yosiasz
Master Smack Fu Yak Hacker
1635 Posts |
|
|
duanecwilson
Constraint Violating Yak Guru
273 Posts |
Posted - 2010-07-21 : 17:00:24
|
| Thank you for looking into this. I don't really understand local accounts, etc. I got grounded on that other post, too, but didn't think it was directly related because I was trying to do a shell command. I thought I straight job with sql would work. First of all, I have domain account which 1) logs me into my machine, 2) allows me into SQL Server on my machine (trusted connection - no username or password), 3) I have full access to the above network folder from my PC, 4) I have access through this account to all the other SQL Servers mentioned on the other post (trusted connection again), and 4), I have full Admin rights on my PC. The other post you mentioned said "A local account will not have access to nccicmshr001 server". I am not sure what that means as I have access when I navigate to the folder. I am not sure what I am looking for or what advantage a new account on my local machine would do or how I would assign that to SQL Server anyway. I know I am rambling, but if anyone could shed more light on this, I am sure I am not the only one who would be helped. Thank you.Duane |
|
|
|
|
|
|
|
|
SQL job - set up linked server to Excel