Linked Server Issue

Question

Linked Server Issue

narayanamoorthy.a

Ten Centuries

Points: 1256
More actions
November 18, 2014 at 6:28 am

#397652

Hi All,
I have remote connections, TCP/IP, SQL 1433 enabled. But still when i run query I get the below message. I am using SQL2012 SP2. A little help over here is appreciated.
OLE DB provider "SQLNCLI11" for linked server "TEST" returned message "Login timeout expired".
OLE DB provider "SQLNCLI11" for linked server "TEST" 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.".
Msg 53, Level 16, State 1, Line 0
Named Pipes Provider: Could not open a connection to SQL Server [53].
Regards,
NM

Viewing 8 posts - 1 through 7 (of 7 total)

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

spaghettidba SSC Guru Points: 105732 More actions · Answer 1

Looks like a connectivity issue.

Log in to the server that contains the linked server and see if you can connect to the linked server from there (use sqlcmd if SSMS is not installed).

-- Gianluca Sartori

christian_t Hall of Fame Points: 3588 More actions · Answer 2

can you post some details of your link server config?

narayanamoorthy.a Ten Centuries Points: 1256 More actions · Answer 3

The below is linked server i created. Its OLEDB DB@ provider.

USE [master]

GO

/****** Object: LinkedServer [ECCVPDB] Script Date: 11/18/2014 5:43:18 AM ******/

EXEC master.dbo.sp_addlinkedserver @server = N'ECCVPDB', @srvproduct=N'Microsoft OLE DB Provider for DB2', @provider=N'DB2OLEDB', @datasrc=N'P08', @provstr=N'Initial Catalog=ECCVPDB;Data Source=P08;Package Collection=admin;Default Schema=admin', @catalog=N'P08'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'ECCVPDB',@useself=N'False',@locallogin=NULL,@rmtuser=N'IKEAPP',@rmtpassword='########'

I added a linked server to it as well.

USE [master]

GO

/****** Object: LinkedServer [IBASE] Script Date: 11/18/2014 5:43:46 AM ******/

EXEC master.dbo.sp_addlinkedserver @server = N'IBASE', @srvproduct=N'SQL Server'

/* For security reasons the linked server remote logins password is changed with ######## */

EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'IBASE',@useself=N'False',@locallogin=NULL,@rmtuser=N'IKEAPP',@rmtpassword='########'

GO

christian_t Hall of Fame Points: 3588 More actions · Answer 4

narayanamoorthy.a (11/18/2014)
The below is linked server i created. Its OLEDB DB@ provider.
@provider=N'DB2OLEDB'

Do you connect to a DB2 Database?

Port 1433 is usually MSSQL

In the Linked-Server Wizard you should select MS OLE DB PRovider for SQL Server or SQL Server Native Client 11.0

narayanamoorthy.a Ten Centuries Points: 1256 More actions · Answer 5

Yes I am connecting DB2 server. I have SQL running on 1433. I have installed the DB2OLEDB provider in the server and gave the provider as Microsoft OLEDB provider for DB2.

christian_t Hall of Fame Points: 3588 More actions · Answer 6

Did you look into this Topic?

http://www.sqlservercentral.com/Forums/FindPost1150136.aspx

another link in this topic is to a how to

http://www.sqlcoffee.com/Tips0013.htm

They are using ODBC-Connection to the DB2-DB.

Be advised that on a 64-bit System you should create ODBC entries for 32 + 64 bit ODBC-Connection as some connectors use 32 bit for meta data.

Christian

narayanamoorthy.a Ten Centuries Points: 1256 More actions · Answer 7

I use 32 bit os. Yes referred the links. Start-RUN-ODBCAD32-System DSN- Click Add- the driver DB2OLEDB is not appearing. Will this be an issue. However the same appears on SSMS-Linked Servers-Providers.