February 7, 2009 at 12:27 am
I have a cross-server query that works marvelously when executed in a Query Analyzer session, and it works fine from inside a stored procedure; however, if I try to execute the script or the sproc from within a job, it fails with the following error:
02/06/2009 23:19:18,PushWinPMStatsToHelm,Error,1,MSIONLDEMO1,PushWinPMStatsToHelm,Exec Push,,Executed as user: NT AUTHORITY\SYSTEM. OLE DB provider 'SQLOLEDB' reported an error. [SQLSTATE 42000] (Error 7399) [SQLSTATE 01000] (Error 7312) OLE DB error trace [OLE/DB Provider 'SQLOLEDB' IDBInitialize::Initialize returned 0x80004005: ]. [SQLSTATE 01000] (Error 7300). The step failed.,00:00:00,16,7300,,,,0
Any help out there? Thanks!
.
February 7, 2009 at 3:49 am
0x80004005 usually means "not authorized"
- What's the service account of sqlagent?
- which account is owner of the job ?
- which is the proxy account used in the job ?
- How did you set up the linked server (security wise)
- are you using "openquery" ?
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
February 7, 2009 at 10:56 am
The SQLAgent is setup to use the local system account. It's a simple query that uses multi-part syntax to insert from one server into another:
insert into [10.2.1.50].TargetDb.dbo.TargetTable(f1,f2,f3)
select 'f1', 'f2', 'f3' from dbo.SourceTable
so the job is owned by and executed from the context of the source table.
I used Enterprise Mgr. to setup a server link to the target from the source.
.
February 9, 2009 at 12:08 am
Is there any credential mapping ?
Local system does not have auth on other servers !
Johan
Learn to play, play to learn !
Dont drive faster than your guardian angel can fly ...
but keeping both feet on the ground wont get you anywhere :w00t:
- How to post Performance Problems
- How to post data/code to get the best help[/url]
- How to prevent a sore throat after hours of presenting ppt
press F1 for solution, press shift+F1 for urgent solution 😀
Need a bit of Powershell? How about this
Who am I ? Sometimes this is me but most of the time this is me
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply