June 2, 2011 at 2:16 am
Hiya,
I have 2 instances of SQL running on the same server. One uses the default instance (MSSQLSERVER) and the other we've specified a named instance.
I am trying to create a linked server from the default instance to the named instance but I keep getting the error message;
TITLE: Microsoft SQL Server Management Studio
------------------------------
The linked server has been created but failed a connection test. Do you want to keep the linked server?
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
SQL Server Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].
OLE DB provider "SQLNCLI10" for linked server "[SEV-DB01\ISYS]" returned message "Login timeout expired".
OLE DB provider "SQLNCLI10" for linked server "[SEV-DB01\ISYS]" 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: 65535)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.00.4000&EvtSrc=MSSQLServer&EvtID=65535&LinkId=20476
------------------------------
BUTTONS:
&Yes
&No
------------------------------
If I create the linked server the other way, from named to default, then it works OK.
I have mirrored the config in terms of Named Pipes etc. One thing to note is the default instance has a static port of 1433 and the named instance looks like it has dynamic ports. All services are set to use a domain user account, except the browser service which is set to 'Local Service'.
June 2, 2011 at 2:43 am
does the server where you are trying to create linked server resolves the name "SEV-DB01" . if not either use IP or create an alias for that server then retry.
Regards,
MShenel
June 2, 2011 at 2:50 am
The problem is with this name 'SEV-DB01\ISYS'
I had faced similar issue before when one of the instances had - in it.
I had created an alias and it worked. I agree with Shenal.
M&M
June 2, 2011 at 7:15 am
They are the same physical server, so yes the server can ping itself by name and resolve to IP address. As above, the 2 instances are on the same physical machine.
However, creating an Alias done the trick. IT must be that SQL doesn't like full named instances to be used in Linked Servers, probably the \ character.
June 2, 2011 at 9:09 am
I think the problem is with the hyphen. I could configure linked servers which had / as part of its name.
M&M
January 29, 2012 at 9:50 pm
The only way I could get a remote named instance of sql server to work as a linked server was by creating an alias for it. I think it has something to do with the "\" separator creating problems.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply