Results 1 to 10 of 10

Thread: Linked Servers

  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Unanswered: Linked Servers

    I added a remote server using sp_addlinkedserver commandn succesfully.

    But when i run Select count(*) from Servername.dbname.tablename
    it give me this error

    Server: Msg 7202, Level 11, State 2, Line 1
    Could not find server 'Servername' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    Any reasons why?

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    149

    Re: Linked Servers

    Did you add a login to the linked server too? If you did, go to EM and see if you can see the table list.
    Originally posted by vmlal
    I added a remote server using sp_addlinkedserver commandn succesfully.

    But when i run Select count(*) from Servername.dbname.tablename
    it give me this error

    Server: Msg 7202, Level 11, State 2, Line 1
    Could not find server 'Servername' in sysservers. Execute sp_addlinkedserver to add the server to sysservers.

    Any reasons why?

  3. #3
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    You need a 4 part name...you're missing the owner, and youalso need to do sp_addlinkedsrvlogin

    Try this

    Select count(*) from Servername.dbname..tablename
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  4. #4
    Join Date
    Sep 2003
    Posts
    212
    nope tried the sp_addlinkedsrvlogin still i get the same error and a new one:

    Server: Msg 15015, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 40
    The server 'ServerName' does not exist. Use sp_helpserver to show available servers.

    and exec sp_helpserver shows the ServerName but the networkname field is NULL

  5. #5
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Can you post the code that added the linked server?

    And what name did you give the link? You need to use that, not the actual server name, unless it's the same...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  6. #6
    Join Date
    Sep 2003
    Posts
    212
    /* Remove any previous references to the linked server */
    EXEC sp_dropserver 'Server_I_want_to_add'

    EXEC sp_addlinkedserver
    @server='Server_I_want_to_add', /* local SQL name given to the linked server */
    @srvproduct='Existing_Server_Name', /* not used */
    @provider='SQLOLEDB', /* OLE DB provider */
    @datasrc='Existing_Server_Name', /* analysis server name (machine name) */
    @catalog='Barney' /* default catalog/database */

    EXEC sp_addlinkedsrvlogin
    @rmtsrvname='Server_I_want_to_add',
    @useself='true',
    @rmtuser ='**User_Name**',
    @rmtpassword = '***Password***'

    /* Two additional procedures obtain information about the
    tables and columns available in the cube. It is not
    necessary to use them to complete the link.*/

    /* This provides schema rowset information about
    the dimensions available from the linked server */
    EXEC sp_tables_ex
    @table_server='Server_I_want_to_add'

    /* This provides schema rowset information about the
    measures and levels of the dimensions
    available from the linked server */
    EXEC sp_columns_ex
    @table_server='Server_I_want_to_add',
    @table_name='Table_Name'

  7. #7
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Does your select look like
    SELECT COUNT(*)
    FROM Server_I_want_to_add.dbname..table

    Notice the 2 dots, and the reference is to the linked server name, not the actual server name...

    I always use the method in bol

    like

    Code:
    EXEC sp_addlinkedserver 
       'LONDON Payroll', 
       '', 
       'MSDASQL',
       NULL,
       NULL,
       'DRIVER={SQL Server};SERVER=MyServer;UID=sa;PWD=;'
    GO
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  8. #8
    Join Date
    Sep 2003
    Posts
    212
    Yup i notice the 2 dots.... last time it was a typo

    thx for pointing that out

    newayz i'll try ur code. thx

  9. #9
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Just curious...can you go in to Enterprise Manager and see the linked server?

    It's in the Security folder for the server...
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  10. #10
    Join Date
    Sep 2003
    Posts
    212
    yeah it works now, I see it in EM>security.

    thx

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •