I'm getting the below error when attempting to run a stored proc across a linked server that is going from Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) to Microsoft SQL Server 2008 (SP2) - 10.0.4000.0 (X64).
The sql instance that I'm linking to is running the database engine successfully, however the sql server agent on the linked server's instance is unable to start with a 'object reference not set to an instance of an object' .net exception being thrown.
This error only started to occur after a scheduled server restart, so I'm thinking maybe there is a config option that was set somewhere that only took effect after the restart.
I've checked firewall and the resources available to the linked server (CPU, RAM and disk) and couldn't see anything causing an issue.
Is there anything else I should be checking? Any config options that may interfere with this that might have taken effect with the server restart?
EDIT: Ok so a server restart fixed the issue. I have no idea why though - can anyone suggest why this could have occurred and how it could've been fixed without a server restart?
OLE DB provider "SQLNCLI10" for linked server "Linkedserver" returned message "Client unable to establish connection due to prelogin failure".
Msg 10054, Level 16, State 1, Line 0
TCP Provider: An existing connection was forcibly closed by the remote host.
OLE DB provider "SQLNCLI10" for linked server "Linkedserver" returned message "Client unable to establish connection".
Msg 26, Level 16, State 1, Line 0
Client unable to establish connection because an error was encountered during handshakes before login. Common causes include client attempting to connect to an unsupported version of SQL Server, server too busy to accept new connections or a resource limitation (memory or maximum allowed connections) on the server.
The t-sql to create the linked server is below:
/****** Object: LinkedServer [LinkedServer] Script Date: 09/30/2009 12:34:15 ******/
EXEC master.dbo.sp_addlinkedserver @server = N'LinkedServer', @srvproduct=N'LinkedServer\InstanceName', @provider=N'SQLNCLI', @datasrc=N'LinkedServer\InstanceName', @catalog=N'Database'
/* For security reasons the linked server remote logins password is changed with ######## */
EXEC master.dbo.sp_addlinkedsrvlogin @rmtsrvname=N'LinkedServer',@useself=N'False',@locallogin=NULL,@rmtuser=N'userid',@rmtpassword='########'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'collation compatible', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'data access', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'dist', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'pub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'rpc', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'rpc out', @optvalue=N'true'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'sub', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'connect timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'collation name', @optvalue=null
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'lazy schema validation', @optvalue=N'false'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'query timeout', @optvalue=N'0'
GO
EXEC master.dbo.sp_serveroption @server=N'LinkedServer', @optname=N'use remote collation', @optvalue=N'true'
