Hey, last time I just mentioned we are working on MS SharePoint stuff, so SQL server and T-SQL, all familar stuff...uh...
I learned database stuff through T-SQL at school, since our school has a good deal with MS, which school deesn't have? I have to admire MS's business strategy (sarcastic? I don't think so:)
So Our T-SQL team has a procedure to get data from oracle database and insert data into SQL table using OpenQuery, the procedure is working fine on local, but it failed when they put it as SQL job. The team jsut asked me to help so after quite some tries, we found the error is caused by invalid login. The log error is long and confusing, talking about expected NT user failed to login.
Then the problem is narrow down to the sp_addlinkedsrvlogin (Transact-SQL). There are 2 logins, local and remote login. The procedure uses some real account such as SA and some other accounts created for local login. The server is set up to use Windows and SQL loin option. Since I just jump in to try to help the team, I just trouble shooting from my experience without knowing all the set up in servers.
[ @locallogin = ] 'locallogin'
Is a login on the local server. locallogin is sysname, with a default of NULL. NULL specifies that this entry applies to all local logins that connect to rmtsrvname. If not NULL, locallogin can be a SQL Server login or a Windows login. The Windows login must have been granted access to SQL Server either directly, or through its membership in a Windows group granted access.
after reading this, I tried to use NULL for local login, and it worked! I am not sure how to use actual local login to make it work on SQL job. I may check on this if I have time, or if the team has interest on that... And it's working, most time we are just lazy to explore more... hehe.
So I put this on net, hopefully someone has same situation and might be helpful. Long live sharing knowledge!