none
With sql server 2008 std, can't create a linked server to sql server 2008 express

    Question

  • I thought creating a linked server would be easy but after an hour trying to implement the most basic linked server imaginable and getting nowhere, I have to ask.

    This msdn article deals with sql server 2008, and seems very direct, but must be incomplete:

    http://msdn.microsoft.com/en-us/library/aa560998%28v=bts.10%29.aspx

    I've read about ten other articles on the subject and all fail.

    Here is what I am trying in the case of the msdn article.

    On a sql server 2008 std box I'm trying to link to an instance of sql server 2008 R2 express which I normally access via xpressservername\sqlexpress. The two servers can connect to each other via odbc so there is no issue with 'allow remote connections' and firewalls etc.

    In the create new linked server wizard I select server type SQL Server, Linked Server name "xpressservername" without the double quotes. I select a local login which has rights to my local sql server instance; leave impersonate blank; and enter remote user "myuser" with password. I've also tried "xpressservername\myuser". On the server options tab I select RPC true.

    The error is:

    The linked server has been updated but failed a connection test. Do you want to edit the linked server properties?
    ------------------------------
    ADDITIONAL INFORMATION:
    An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
    ------------------------------
    Named Pipes Provider: Could not open a connection to SQL Server [2].
    OLE DB provider "SQLNCLI10" for linked server "xpressservername" returned message "Login timeout expired".
    OLE DB provider "SQLNCLI10" for linked server "xpressservername" returned message "A network-related or instance-specific error has occurred while establishing a connection to SQL Server. Server is not found or not accessible. Check if instance name is correct and if SQL Server is configured to allow remote connections. For more information see SQL Server Books Online.". (Microsoft SQL Server, Error: 2)

    One thing I don't get is how the simple server name "xpressservername" is supposed to be enough of a directive; maybe if one only has a default instance but in this case it's a named instaned (sqlexpress) and what if more than one sql install on that server? So I tried using the "other data source" option because you can supposedly pick a server and specific 'catalog' (db) and got nowhere. The wizard dialog seems defective...if I leave product name blank it complains, and if I enter "SQL Server", it says not to enter a product name if it's sql server. Wow...


    Thursday, December 23, 2010 6:49 PM

Answers

  • Are these 2 SQL server instances on the same machine ? When creating the linked server are you using the SQL server or other data source option ?
    The error message has nothing to do with the users/login at all its either you are not entering a valid SQL instance or the service on the sql instance is stopped.

    If you are trying to create the linked server for a named instance then you should include the name in Linked server as servername/instancename


    Thanks, Leks
    • Marked as answer by rusticloud Thursday, December 23, 2010 8:06 PM
    Thursday, December 23, 2010 7:43 PM

All replies

  • Are these 2 SQL server instances on the same machine ? When creating the linked server are you using the SQL server or other data source option ?
    The error message has nothing to do with the users/login at all its either you are not entering a valid SQL instance or the service on the sql instance is stopped.

    If you are trying to create the linked server for a named instance then you should include the name in Linked server as servername/instancename


    Thanks, Leks
    • Marked as answer by rusticloud Thursday, December 23, 2010 8:06 PM
    Thursday, December 23, 2010 7:43 PM
  • Hi Lekss these are two different servers (I thought that should be apparent from what I wrote but maybe not).

    And your tip was perfect - servername/instancename made it light up.

    Thanks

     

     

    Thursday, December 23, 2010 8:06 PM
  • Finally, how do I create a query against the newly linked server? I've seen two syntaxes used and neither seem to work for me:

    SELECT * FROM OPENQUERY
    ('xpressservername\sqlexpress','SELECT * FROM master.sys.servers')
    GO

    and

    SELECT * FROM 'xpressservername\sqlexpress'...master.sys.servers

     

    Thursday, December 23, 2010 8:11 PM
  • -- This should work

    select

     

     

    * from [servername/instancename].master.sys.databases

    SELECT

     

     

    * FROM OPENQUERY

    (

     

     

    [servername/instancename],'SELECT * FROM master.sys.servers')

    GO

     


    Thanks, Leks
    Thursday, December 23, 2010 8:17 PM
  • Perfect, except that those should be backslashes (at least here)

    select * from [xpressservername\sqlexpress].master.sys.databases
    SELECT * FROM OPENQUERY
    ([xpressservername\sqlexpress],'SELECT * FROM master.sys.servers')

    GO

    Thank you!

    Thursday, December 23, 2010 8:31 PM
  • I'm also interested to see if the other syntax will work if adjusted

    SELECT * FROM [xpressservername\sqlexpress]...master.sys.servers

    I get this error:

    The object name 'xpressservername\sqlexpress...master.sys.servers' contains more than the maximum number of prefixes. The maximum is 3.

    Is that syntax invalid or am I just calling it wrong?

    Thursday, December 23, 2010 11:32 PM
  • OK, I guess the syntax I found in one article was wrong. In addition to the OPENQUERY example you provided, this syntax also works:

    SELECT * FROM [xpressservername\sqlexpress].master.sys.servers

    or

    SELECT * FROM [xpressservername\sqlexpress].mydb.dbo.mytable

    Thursday, December 23, 2010 11:39 PM