Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'
share|improve this question
add comment

1 Answer

I've seen it happen twice after SQL Server service packs were applied - service packs that took a really long time (hours) to finish the upgrade, or broke as they were trying to apply changes to the system databases.

The fact that you mentioned there was a scheduled restart clues me in, too - that means someone had probably staged updates to happen on restart, and they did - but then they caused an outage.

As a result of this, I'm never a big fan of automatically applying updates (Windows or SQL) on SQL Servers.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.