Try using the SQL Server 2005 SQL Server Native Client
(@provider=N'SQLNCLI') instead of the SQLOLEDB. That's what Managment Studio
is using for the connection. And when you are using Management Studio to
connect to a named instance on the same computer, Management Studio often
uses the Shared Memory protocol, though you can force it to use TCP.
If you select this route, then you must upgrade your clients on other
computers with the SQL Server Native Client as well. But they may already
have that if they are connecting to your SQL Server 2005.
Also make sure SQL Server 2000 is running at leas SP2.
--
Rick Byham (MSFT)
This posting is provided "AS IS" with no warranties, and confers no rights.
[quoted text, click to view] "Dan" <Dan@discussions.microsoft.com> wrote in message
news:BE518899-E737-460D-B8F4-E81ACC797DE5@microsoft.com...
>I am trying to create a linked server between 2 SQL Servers. The server
>that
> I'm trying to connect to has 2 instances of SQL Server on it. One is SQL
> 2000
> STD and is the default instance. The other is SQL 2005 Wrkgrp and has the
> instance name 'sql05'. I need to be able to connect to the linked server
> from
> outside the LAN by just using the IP and instance name.
>
> SCRIPT
> ================
> EXEC master.dbo.sp_addlinkedserver @server = N'MyLinkedServer',
> @srvproduct=N'', @provider=N'SQLOLEDB',
> @datasrc=N'111.222.333.444\sql05', @catalog=N'myDB'
>
> PROBLEM
> ================
> This script works and the linked server returns results when I set
> @datasrc
> to either:
> 1) The local 10. IP address and instance name (ex 10.0.1.1\sql05)
> 2) The external IP (that gets routed through a firewall and converted to
> the
> internal IP in scenario 1), BUT ONLY if I leave off the instance name
> (this
> makes it connect to the default SQL 2k instance that has a db with the
> same
> name).
>
> The problem occurs when I try to set @datasrc to the external IP and
> instance name. When I do that and then run 'exec sp_tables_ex
> myLinkedServer', I get the error:
> "Msg 65535, Level 16, State 1, Line 0
> SQL Network Interfaces: Error Locating Server/Instance Specified
> [xFFFFFFFF]."
>
> I can connect to the same server\instance_name using SSMS, but not with a
> linked server. What could possibly be the problem here? I am stumped. Any
> help is greatly appreciated.
>
