SQL Server Performance

how to use sp_addlinkedserver

Discussion in 'T-SQL Performance Tuning for Developers' started by adyseven, Apr 19, 2007.

  1. adyseven New Member

    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?
  2. Adriaan New Member

    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.
  3. Jack Vamvas Member

    You can also set up the Linked Server within Enterprise Manager . Under the Security Tab

    ___________________________________
    Need an IT job? -http://www.ITjobfeed.com

  4. madhuottapalam New Member

Share This Page