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.
From MSDN:
[ @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!
Thursday, August 6, 2009
Wednesday, August 5, 2009
ORCL - Remove ^M in VI
Our management loves to use MS office products, Word, Excel, Sharepoint, to name a few...Our dev is Oracel/Unix, good thing we picked Oracle, since that is the main stream of univeristy? (BTW, I am working in university external relation department).
Quite often, we need get excel file to import into Oracle database and massaging data. Most colleagues use some gui developer tools such as Toad, PL/SQL Developer to do the job, which are pretty good. I like the Oracle external table (Worked a few years on SQL loader :).
To get file from local (of course Windows) to unix server, the PSCP comes handy! It's from Putty, maintained by a small team based in Cambridge, England, and easy command line. There are also some GUI tools like WinSCP, etc.
But sometimes when I convert excel into csv (comma, tab delimited), the external table will not work due to the stupid ( I should not say this :( )^M at the end of lines. UNIX treats the end of line differently than other operating systems. Sometimes when editing files in both Windows and UNIX environments, a CTRL-M character is visibly displayed at the end of each line as ^M in vi.
Good thing that Vi powerful replace command, :%s/^V^M//g, make sure ^V^M are ctrl-v and ctrl M, not copying/pasting :) . It looks not showing on VIM - GUI vi.
The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).
Just another comment: I don't know why some IT people, especially the network/helpdesk IT guys like windows stuff so much (acutally I love that too, but just dont give up on *nix:), really user friendly? They will do anything to eacpae from any possibility of *nix stuff. Sigh... Are we thinking about converting data from Oracle to SQL server to work on Sharepoint stuff? Maybe it's good for performance.
We are IT people, we should handle everything and anything :)))
Quite often, we need get excel file to import into Oracle database and massaging data. Most colleagues use some gui developer tools such as Toad, PL/SQL Developer to do the job, which are pretty good. I like the Oracle external table (Worked a few years on SQL loader :).
To get file from local (of course Windows) to unix server, the PSCP comes handy! It's from Putty, maintained by a small team based in Cambridge, England, and easy command line. There are also some GUI tools like WinSCP, etc.
But sometimes when I convert excel into csv (comma, tab delimited), the external table will not work due to the stupid ( I should not say this :( )^M at the end of lines. UNIX treats the end of line differently than other operating systems. Sometimes when editing files in both Windows and UNIX environments, a CTRL-M character is visibly displayed at the end of each line as ^M in vi.
Good thing that Vi powerful replace command, :%s/^V^M//g, make sure ^V^M are ctrl-v and ctrl M, not copying/pasting :) . It looks not showing on VIM - GUI vi.
The :%s is a basic search and replace command in vi. It tells vi to replace the regular expression between the first and second slashes (^M) with the text between the second and third slashes (nothing in this case). The g at the end directs vi to search and replace globally (all occurrences).
Just another comment: I don't know why some IT people, especially the network/helpdesk IT guys like windows stuff so much (acutally I love that too, but just dont give up on *nix:), really user friendly? They will do anything to eacpae from any possibility of *nix stuff. Sigh... Are we thinking about converting data from Oracle to SQL server to work on Sharepoint stuff? Maybe it's good for performance.
We are IT people, we should handle everything and anything :)))
Subscribe to:
Posts (Atom)