none
Linked Server results in: Msg 208, Level 16, State 1, Line 1 Invalid object name [Table Name]

    Question

  • Hello All,

    Currently we are moving all our servers to a datacenter. We have been provided with 5 new SQL servers and I need to order the new servers. I have Administrator rights to do this

    I need to create a Linked server on a Microsoft SQL Server Standard Edition 64 bit 2008 to another Microsoft SQL Server Standard Edition 64 bit 2008.

    I Created a linked server from server1 to server2 and this worked as expected.

    (

    I Used  SSMS and simply klikt "New Linked Server..." I inserted the correct Linked Server and selected Server type SQL Server. In Security I selected Connections will be made using this security context: And inserted a SA user and Password here. 

    After this I wanted to create a similar linked server from Server2 to Server1, however after following the steps above I received an error. (Linked server already exists) After searching for a couple of solutions I found that in running this query:

    SELECT * FROM sys.servers

    The server indeed was in here. (Server_Id 0) With some different values then the first linked server from server1 to server2. For example the column is_linked was 0 and the is_remote_login_enabled=1.

    I dropped this (linked) server with the stored procedure to drop linked servers. After this I created the linked server as above and the linked server from server2 tot server1 was created. 

    When clikking through the linked server the databases this linked server should have rights to where all visible, however when Selecting data in a Query the following error message is supplied:

    Msg 208, Level 16, State 1, Line 1
    Invalid object name '[Database].DBO.[Table]'

    I Dropped and created the linked server serveral times, and also supplied the user which is used in the linked server with Sysadmin rights, however this all did not work. 

    Hopefully somebody can help me with this problem! I have been working a while on this problem and the linked server needs to work for my reports.

    Kind Regards,

    Tommax


    Tuesday, August 06, 2013 8:03 AM

Answers

All replies

  • Try Below


    Example: create  linked server (in SSMS under Server Objects > Linked Servers) called "Linkedservername"

    and check if below is working or not

    SELECT * FROM [Linkedservername].[Database].DBO.[Table]


    • Edited by Pradeep_DBA Tuesday, August 06, 2013 5:13 PM help
    Tuesday, August 06, 2013 5:13 PM
  • Hi Pradeep_DBA,

    Thanks for helping, I still receive an error: 

    Msg 208, Level 16, State 1, Line 1
    Invalid object name '[Database].dbo[Table]'.

    This is still the same. Hopefully you can help...


    • Edited by Tomax 1984 Wednesday, August 07, 2013 7:34 AM
    Wednesday, August 07, 2013 7:21 AM
  • Hi Tommax,

    This issue can occur if the current user doesn’t have permission to access “[Database].DBO.[Table]” table on the linked server. Please map the current login to another login on the linked server who has permission to access the table. Additionally, please check whether we can expand the linked server database and see “[Database].DBO.[Table]” table with SQL Server Management Studio. For more detail information, you can refer to the following link:

    Security for Linked Servers
    http://technet.microsoft.com/en-us/library/ms175537(v=sql.105).aspx

    sp_addlinkedsrvlogin (Transact-SQL)
    http://technet.microsoft.com/en-us/library/ms189811.aspx


    Allen Li
    TechNet Community Support

    Thursday, August 08, 2013 7:29 AM
    Moderator