December 2, 2008 at 5:53 am
hi
i have two servers D2\dev and IN\dev ..both servers were behaving as Linked server for each other for a long time (say from last 30 days) but yesterday we receive error
when we try do execute a remote query at IN\dev server
------Queries ------------------------------
select top 1 * from [d2\dev].CVENT_DBA.dbo.SYNC_OBJECT_RECEIVE
go
select top 1 * from [d2\DEV].cvent_prod.dbo.contact
------------------------------------------------------------------------
-----ERROR_-------------------------------
OLE DB provider "SQLNCLI" for linked server "d2\dev" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "d2\dev" returned message "Communication link failure".
OLE DB provider "SQLNCLI" for linked server "d2\dev" returned message "Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
Query was cancelled by user.
-----ERROR END-------------------------------
but the strange thing is that first query is working fine but Second query giving above ERROR
we are using two diff databases in two queries
if i m not wrong linked server setting cannot be database specific ?
now below are the linked server settings ;
---At D2\dev server
/****** Object: LinkedServer [in\DEV] Script Date: 12/02/2008 18:22:17 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'in\DEV', @provider=N'SQLNCLI', @datasrc=N'in\DEV'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'collation compatible', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'in\DEV', @optname=N'use remote collation', @optvalue=N'true'
------At IN\dev server
/****** Object: LinkedServer [D2\DEV] Script Date: 12/02/2008 18:23:38 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'D2\DEV', @srvproduct=N'SQL Server'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'D2\DEV', @optname=N'use remote collation', @optvalue=N'true'
----------------------------------------
can any one help me in this area ???????????????
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
December 2, 2008 at 7:37 am
if it has been working without fail for a long time, and suddenly doesn't work, I'd check the basics:
is there basic network connectivity to the DF2\dev server? can you ping it? is the service running?
did someone on the D2\dev server change the password you were using?
did the database you were connecting to get renamed?
did the database you were connecting to get restored? maybe it's an orphaned user login problem
Lowell
December 2, 2008 at 9:05 pm
no buddy ......Nothing like that !!!
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 19, 2010 at 3:32 am
Did you find out the solution for your problem? I have the same problem as yours. same scenario.
I hope you could post the solution here.
Thanks.
April 19, 2010 at 5:30 am
you saw 2 earlier years back.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 19, 2010 at 8:02 pm
Hehehe but have you found the solution for this? 😀
April 19, 2010 at 11:40 pm
i dont really remember the exact cause but i think it happened because of network connection failure.
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 20, 2010 at 1:31 am
I cant find a solution for this.
My case is, I have many linkservers from Server A. Almost all are functioning. Only some are encountering the errors.
OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "Unspecified error".
OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "Communication link failure".
OLE DB provider "SQLNCLI" for linked server "RemoteServer" returned message "Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
The wierd thing is, I can directly connect to the RemoteServer from the Server A. The error only appears when I use the linkserver connection.
One of the Sysad of our company told me that it might be network related, but how come I am able to directly connect to the remote server?? Using the PCName, not even the IP address., By the way the datasource of my linkserver is the ipaddress of the remoteserver, so it should function properly and besides, this has been functional eversince and is used everyday. The error just appears this past few days.
Whew, hope someone could enlighted me.
April 20, 2010 at 2:06 am
What login credentials you are using to connect with linked server ?
and During linked server setup in securty tab which options you are using ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 20, 2010 at 2:26 am
HAHA! Finally Got the solution!
I've added the Ip address PC Name on the host file of the SQL Server where I created the link server as advised by our SYS AD!
GREAT! haha
THanks by the way!
April 20, 2010 at 2:28 am
You and our SysAd were right all along, it was network problem. nothing to do with the linkserver setup.
Thanks for your response! appreciate it! 😀
April 20, 2010 at 3:39 am
acebalajadia (4/20/2010)
I've added the Ip address PC Name on the host file of the SQL Server
Are you sure :w00t: ? Sql server doesnt have any HOST file , where you have made changes , can you share with us ?
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
April 25, 2018 at 9:31 am
We were receiving a similar error message.
OLE DB provider "SQLNCLI11" for linked server "<server>" returned message
"Communication link failure".
Msg 64, Level 16, State 1, Line 0
TCP Provider: The specified network name is no longer available.
We also applied the recommended changes to turn off Offloading. This did not resolve our problem.
We were running an Insert Select statement from server A to Server B. The statement include two varchar(max) fields. When both varchar(max) fields were removed from the insert statement, the query succeeded. When the fields were added back the query, the query failed with the above noted error. We were able to trace the issue to the inclusion of {Tab} Ascii Char(9) values embedded in varchar(max) field values. When the Tab chars were replaced with a {Space} char the query ran successfully. Characters such as Line Feed and Carriage Return did not cause the same problem.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply