SQL Server – Execute Stored Procedure via Linked Server

linked-serversql serversql server 2014sql-server-2008-r2

We are in the planning stages for an upgrade from SQL Server 2008 R2 to SQL Server 2014.

Our system executes stored procedures against different databases on different servers.

If we upgrade one server to SQL Server 2014, but leave the remaining servers on SQL Server 2008 R2, would we lose that ability?

Best Answer

As a test, I just ran the following from my SQL Server 2008 R2 instance through a linked server to my SQL Server 2012 instance:

SELECT LocalServerName = @@SERVERNAME
    , LocalServerVersion = SERVERPROPERTY('ProductVersion');

EXEC [REMOTESERVER].master.sys.sp_executesql 
    N'SELECT RemoteServerName = @@SERVERNAME
        , RemoteServerVersion = SERVERPROPERTY(''ProductVersion'');';

This will show two result-sets, one for the local server, with server name, and version, and one for the remote server with the same details.

Executing this will prove that you can run stored procedures from down-level servers to up-level servers.

Related Question