SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Linked Server behaving strange


Linked Server behaving strange

Author
Message
Bhuvnesh
Bhuvnesh
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21502 Visits: 4079
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;-)
Lowell
Lowell
SSC Guru
SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)SSC Guru (123K reputation)

Group: General Forum Members
Points: 123334 Visits: 41451
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
--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!
Bhuvnesh
Bhuvnesh
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21502 Visits: 4079
no buddy ......Nothing like that !!!

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ace.spades
ace.spades
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 23
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.
Bhuvnesh
Bhuvnesh
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21502 Visits: 4079
you saw 2 earlier years back.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
ace.spades
ace.spades
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 23
Hehehe but have you found the solution for this? :-D
Bhuvnesh
Bhuvnesh
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21502 Visits: 4079
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;-)
ace.spades
ace.spades
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 23
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.
Bhuvnesh
Bhuvnesh
SSC-Insane
SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)SSC-Insane (21K reputation)

Group: General Forum Members
Points: 21502 Visits: 4079
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;-)
ace.spades
ace.spades
SSC Veteran
SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)SSC Veteran (222 reputation)

Group: General Forum Members
Points: 222 Visits: 23
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!
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search