SQL Agent puzzle
-
The particulars:
SQL Server 2000 Standard Edition, SP3 (8.00.760 SP3 Standard Edition)
Windows Server 2003 Enterprise Edition, SP1
SQL Server Agent running under a Service Account, [MachineName]\SQLServ
member of [MachineName]\Administrators group
[MachineName]\SQLServ is a member of the SA database role on \\[MachineName]
SA is the owner of the failing job
MyDomain\MyWindowsAccount is a member of the SA database role on \\[MachineName]
This machine was built as [FormerMachineName] and then renamed [MachineName] when we de-commissioned an old machine, the original [MachineName].
The puzzle:
I have a SQL Server Agent job, ThisJob. ThisJob is owned by SA. It has one T-SQL step, as follows:
if (select count(*) from vwOrdersShippedNotInvoiced) > 0
begin
exec master.dbo.xp_sendmail
@recipients = 'seth.buxton@ihs.com',
@message = 'Stuff',
@subject = 'More Stuff',
@query = 'select * from vwOrdersShippedNotInvoiced',
@dbuse = 'Ais'
end
If I connect to [MachineName] as SA with Query Analyzer, I can run the code above with no problem whatsoever. If I connect with MyDomain\MyWindowsAccount AND comment out the @query and @dbuse lines in the above code, it runs with no problem whatsoever. If I comment out the @query and @dbuse lines of code in the ThisJob SQL Agent job, ThisJob runs with no problems whatsoever.
If I connect to [MachineName] as MyDomain\MyWindowsAccount with Query Analyzer, I receive the error below when I attempt to run the code above:
ODBC error 8198 (42000) Could not obtain information about Windows NT group/user 'MyDomain\MyWindowsAccount'.
If I run ThisJob, it completes successfully, but does not send the email. The error listed in the details of the job history:
Message
Executed as user: [MachineName]\SQLServ. ODBC error 8198 (42000) Could not obtain information about Windows NT group/user '[FormerMachineName]\SqlServ'. [SQLSTATE 01000] (Message 18014). The step succeeded.
Has anyone seen anything like this before? I am truly at a loss. I was under the impression that any T-SQL step of any SQL Agent job would run under the context of the job owner, SA in this case. I'm having a hard time searching the forums for this problem as it is pretty funky. Please let me know if you have any advice at all.
-
1.Try running "Select @@ServerName" and see what it returns if it returns the FormerMachineName then SQL Server has not been remaned so need to rename the SQL Server.
sp_dropserver 'old server name'
sp_addserver 'new server name', 'local'
2.Does MyDomain\MyWindowsAccount has access to "AIS" database?
Thanks
Sreejith
-
Select @@ServerName returns [MachineName], not [FormerMachineName]
MyDomain\MyWindowsAccount is SA, it has access to the AIS database. I can SELECT from the AIS table, and the view, when logged in as MyDomain\MyWindowsAccount.
Thank you for the response!
-
@query = 'select * from AIS.dbo.vwOrdersShippedNotInvoiced'
this may "sound" redicule, but that's how it always works overhere
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
-
Thanks, alzdba, but I've tried that, too. No dice. I understand it's proper to fully qualify things, but this job runs in the context of the AIS database.
-
You may want to check the Wins setting on your server NIC card(s).
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Double check that the account that SQL Agent uses is correct. Also, it looks like your MAPI program (Outlook?) may be the culprit. Go into the Control Panel and reset your mail profile to use the correct machine name and account, then reset the profile that the Agent uses.
Hope this helps. SQL Mail can be a pain to get working correctly.
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
Thank you for the suggestion, Russell. I can send a test email from the properties of the SQL Server Agent. Nonetheless, I logged in to the machine where my SQL Server installation exists as the same account that I have the SQLServerAgent and MSSQLServer services running under. I configured another Outlook profile. I associated the SQL Server Agent with this second profile, and bounced the SQLServerAgent service.
Still no dice, my situation has not changed.
-
Interestingly enough, through the 'New Login' dialogue, I can search for and find 'MachineName\SQLServ'. However, when I click 'OK' to create the login, I am treated to an error message stating in no uncertain terms that "Windows NT user or group 'MachineName\SQLServ' not found. The login 'MachineName\SQLServ' does not exist."
Good times.
-
Are you using a local account rather than a network account? Try using a domain account, and configuring the mail profile to use that. (Logon to your server with that account to configure the profile).
So long, and thanks for all the fish,
Russell Shilling, MCDBA, MCSA 2K3, MCSE 2K3
-
What collation are you using ? If it is case sensitive it may appear that XXX\LOGIN could actually be xxx\Login or XXX\Login.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
-
Thank you for your responses, gentlemen. I resolved this issue by configuring a new login, making it a member of the sysadmin server role, and then making it the owner of all of the SQL Server scheduled jobs. That's the Reader's Digest condensed version, of course, I'm sparing you the details.
A co-worker passed along this handy link:
http://support.microsoft.com/default.aspx?scid=kb;EN-US;q281642
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply