Problem with linked servers

  • Hi all,

    I have problem regarding linked server between one SQL Server 2000 Standard Ed. SP4 (default instance) and SQL Server 2005 Developer Ed SP1 (named instance on same machine).

    I created it with:

    - Name: EXAMPLE

    - option Other data source: Microsoft OLE DB Provider for SQL Server

    - Product name: '.'

    - Data source: ServerName\InstanceName

    - security: remote login with password

    - Server Options: dataAccess, RPC, RPC Out, User Remote Collation - checked.

    But, when try to perform 'select * from EXAMPLE.DBName.dbo.TableName I've got messagge:

    "Server: Msg 14, Level 16, State 1, Line 1

    Invalid connection."

    I've got the same linked server in cluster environment where both servers are developer edition (first is SQL 2000 and the second is SQL 2005). And it works.

    What could be a problems ?

  • What server u are accessing the data.

     

  • from the first (SQL 2000) to SQL server 2005

  • linked server is created on sqlserver2000.

    did u mapped the login on both the sqlserver.

  • yes, of course.

  • What I can tell you is link from SQL 2005 to SQL 2000 or vice versa work

    fine for me. I created linked server by EM, I think you should check two

    things:

    1, make sure SQL 2005 allow remote access

    2, make sure you set right userid/password for linked server.

Viewing 6 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply