Solved

Linked server error

Posted on 2008-06-19
4
818 Views
Last Modified: 2008-06-25
I have set up linked server sucessfully.. i can see all the tables and views.. however, when I try to run a query I keep getting below error:

OLE DB provider "SQLNCLI" for linked server "Application1" returned message "Communication link failure".
Msg 121, Level 16, State 1, Line 0
Named Pipes Provider: The semaphore timeout period has expired.


How can I resolve this issue?
0
Comment
Question by:jung1975
  • 2
4 Comments
 
LVL 13

Expert Comment

by:rickchild
ID: 21823067
Check you are on MDAC 2.7, if not then install as this is the latest version.
Also have you tried linked server "SQL Server" rather than using the OLE DB data source?

If that doesn't help it sounds like hardware networking issues rather than SQL server issues.
Check out this link and see if you can ping the other server etc.

http://support.microsoft.com/default.aspx?scid=kb;en-us;827422
0
 

Author Comment

by:jung1975
ID: 21823254
does it matter what port number sql server is using it? The server that i am trying to linked using a different port number than default sql server port number..
0
 
LVL 13

Accepted Solution

by:
rickchild earned 250 total points
ID: 21823337
I assume not as you have managed to browse the tables, but make sure that the port is allowed in the firewall of the server, to allow in the server you are connecting from.

What query are you trying to run on the linked server?
Also in the linked server try changing RPC and RPC-OUT to be True.
0
 
LVL 51

Assisted Solution

by:Mark Wills
Mark Wills earned 250 total points
ID: 21824288
Yes, the port number does make a difference...

default port is 1433

when creating a linked server, need to specify in terms of : <servername>[\instancename],<port>    or   <ipaddress>[\instancename],<port>

both the linked server and the server need to understand what port number is being used. Now SQl Server can be configured to use dynamic ports, but, generally need to control what type of traffic and which ports you are using...

From Books On-Line :  To assign a TCP/IP port number to the SQL Server Database Engine:

In SQL Server Configuration Manager, in the console pane, expand SQL Server 2005 Network Configuration, expand Protocols for <instance name>, and then double-click TCP/IP.
In the TCP/IP Properties dialog box, on the IP Addresses tab, several IP addresses appear, in the format IP1, IP2, up to IPAll. One of these are for the IP address of the loopback adapter, 127.0.0.1. Additional IP addresses appear for each IP Address on the computer. Right-click each address, and then click Properties to identify the IP address that you wish to configure.
If the TCP Dynamic Ports dialog box contains 0, indicating the Database Engine is listening on dynamic ports, delete the 0.
In the IPn Properties area box, in the TCP Port box, type the port number you wish this IP address to listen on, and then click OK.
In the console pane, click SQL Server 2005 Services.
In the details pane, right-click SQL Server (<instance name>) and then click restart, to stop and restart SQL Server.
After you have configured SQL Server to listen on a specific port there are three ways to connect to a specific port with a client application:
        1)Run the SQL Server Browser service on the server to connect to the Database Engine instance by name.
        2)Create an alias on the client, specifying the port number.
        3)Program the client to connect using a custom connection string.
0

Featured Post

Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS SQL Delete Query 9 30
export sql results to csv 6 34
Simple SQL query from two tables 13 51
SQL SERVER 2008 R2 Could not obtain information about Windows NT group/user 5 34
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Via a live example, show how to backup a database, simulate a failure backup the tail of the database transaction log and perform the restore.

806 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question