how to use sp_addlinkedserver | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

how to use sp_addlinkedserver

hi guys, i’ve tried to select data from a table from another database in another machine using ODBC first, i’ve created a ODBC connection (with DSN Name = Server2) in Server1 and successfully connected then, I use sp_addlinkedserver first to create a link between two machine. i use this syntax in current machine (Server1) and want to connect to Server2 to database named: DATA2 and table named: REQ2 exec sp_addlinkedserver ‘Server2_link’,”, ‘SQLOLEDB’, ‘Server2’, ”,”,” then i execute syntax like this: select * from server2_link.DATA2.dbo.REQ2 error is occuring:
Login failed for user ‘sa’. why is this happened? in ODBC setting, i’ve tested the connection and successfully… is there anything i should add in the parameter?
Check BOL – you need to include the authentication details on the last parameter.<br /><br />EXEC sp_addlinkedserver <br /> @server = ‘LONDON Payroll’, <br /> @srvproduct = ”,<br /> @provider = ‘MSDASQL’,<br /> @provstr = ‘DRIVER={SQL Server};SERVER=MyServer;UID=sa<img src=’/community/emoticons/emotion-4.gif’ alt=’;P’ />WD=sapassword;'<br /><br />You’d have to dig a little more if you need Windows authentication.
You can also set up the Linked Server within Enterprise Manager . Under the Security Tab ___________________________________
Need an IT job? –
Read about sp_addlinkedsrvlogin in BOL. You should mention the login to connect to remote server.
you should use sp_addlinkedserver and sp_addlinkedsrvlogin while configuring linkedserver using script refer this too Madhu