January 27, 2011 at 3:30 pm
So what's my problem if it reports "Success"?
Because it is supposed to take at least 12 minutes to run/finish. On a manual run it takes approx 12minutes. When scheduled to run as a SQL Job it takes 12 seconds and no data is copied over. These are all T-SQL steps. Nothing in an SSIS package. The job pulls data from a linked Oracle server to a SQL 2005 Server, that's it.
Every step reports "Succeeded" and the Job reports the same thing but no data.
Any ideas? I think an SSIS pkg will get around this but now it's just me and I don't want it to beat me. 😀
Thanks
January 27, 2011 at 3:51 pm
does the job owner have the correct perms to run?
I am refering to the Owner: of the actual SQL Server Agent Job. Under general settings.
January 27, 2011 at 4:16 pm
The Agent job Owner has sa server role membership.
You wanna hear something crazy? We changed just the time this SQL Agent job executes to 10 minutes from now (well 15 mins ago really) and the job ran perfectly fine. Every step took the length it takes when ran individually.......and reported "Success".
Makes no sense. :hehe:
I have no clue what's going on. Anyone else ever see this? If so, what did you do to fix it?
/cue Twilight Zone theme song.....:hehe::w00t::hehe::w00t::hehe:
January 28, 2011 at 11:12 am
Update...
After resetting the time to run in the wee early morning, it did the same thing. Ran for 12 seconds and reported "Success" for all steps but no data.
So I right clicked the job and selected "Start job at step..." and selected the first step and it ran fine.
Strange!!! :hehe::w00t::hehe::w00t:
January 28, 2011 at 11:15 am
is the linked Oracle server local or remote?
maybe some network rules are getting in the way.
January 28, 2011 at 11:20 am
It's in another IP segment but in the same building. If there were any network/packet rules even the manual run would've failed. The manual runs worked.
January 28, 2011 at 11:23 am
i was thinking more along the lines of timed denial in an ACL.
for example, we have 24 x 7 access to server X, but a vendor only has access from 8 am to 4 pm M-F.
that rule is based on a router ACL....
January 28, 2011 at 11:26 am
I thought about that but as I am told by the "Network Guru's", nothing like that is happening.
I think I will move the time closer to business times just in case.......
I'll post results.
Thanks
January 28, 2011 at 11:27 am
Warren Peace (1/27/2011)
The Agent job Owner has sa server role membership.:
It has SA rights in SQL server, but are sure it has necessary rights/configuration to hit the Oracle data?
The Redneck DBA
January 28, 2011 at 11:34 am
Yes.
Here's why I say it does.
The linked server uses one set of credentials provided by the Oracle DBA.
When creating the linked server in SQL 2005 those are the credentials we used. This is in the setting in the Security area when creating a linked server. The option "Be made using this security contesxt:" and I used the creds provided by the Oracle DBA. All works fine. You really can't lock down a linked server because EVERYBODY will be connecting to that Oracle with the creds the Oracle DBA provided us.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply