Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

Linked Server behaving strange Expand / Collapse
Author
Message
Posted Tuesday, December 02, 2008 5:53 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917
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
Post #611989
Posted Tuesday, December 02, 2008 7:37 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 11:15 AM
Points: 12,749, Visits: 31,111
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

--There is no spoon, and there's no default ORDER BY in sql server either.
Actually, Common Sense is so rare, it should be considered a Superpower. --my son
Post #612059
Posted Tuesday, December 02, 2008 9:05 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917
no buddy ......Nothing like that !!!

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #612550
Posted Monday, April 19, 2010 3:32 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 8:33 PM
Points: 6, 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.
Post #905793
Posted Monday, April 19, 2010 5:30 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917
you saw 2 earlier years back.

-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done
Post #905848
Posted Monday, April 19, 2010 8:02 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 8:33 PM
Points: 6, Visits: 23
Hehehe but have you found the solution for this?
Post #906454
Posted Monday, April 19, 2010 11:40 PM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917
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
Post #906534
Posted Tuesday, April 20, 2010 1:31 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 8:33 PM
Points: 6, 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.
Post #906586
Posted Tuesday, April 20, 2010 2:05 AM


SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Today @ 6:52 AM
Points: 2,820, Visits: 3,917
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
Post #906609
Posted Tuesday, April 20, 2010 2:26 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, February 22, 2012 8:33 PM
Points: 6, 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!

Post #906618
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse