Determine the SQL Server version of remote servers

Technorati Tags: SQL Server 2000 , SQL Server 2005

I ran into this situation where the SQL returned expected data against SQL 2000 but not against SQL 2005. This was one of those breaking changes in the SQL 2005 database engine.  The following piece of code determines the SQL version of the remote server from within a stored procedure. As always I believe there are multiple ways of getting to the solution but here is one. Please note the remote SQL server needs to be a linked server on the SQL server executing the SQL script (Can I add any more SQL/servers in this sentence!).

 

DECLARE @RemoteSQLserver VARCHAR(20), @sql VARCHAR(8000), @SQLVersion VARCHAR(10)

SELECT @RemoteSQLserver = 'sql_1'

/* -------------------------- Determine the version of the target server ---------------------*/
-- Check and drop if temporary table exists
IF (OBJECT_ID(N'tempdb..#Results') IS NOT NULL)
BEGIN
    DROP TABLE #Results
END
-- Create the temp table to hold results
CREATE TABLE [#Results] ([SQLVersion] [varchar] (50))

-- Execute the SERVERPROPERTY on the target server
SELECT @sql = 'SELECT CAST(SQLVersion AS Varchar(20)) FROM OPENQUERY(' + @RemoteSQLserver + ', ''SELECT SERVERPROPERTY(''''ProductVersion'''') AS SQLVersion'')'
INSERT INTO #Results EXEC (@sql)
SELECT @SQLVersion = SQLVersion FROM #Results 

-- Extract the major build of the version number
SELECT @SQLVersion = SUBSTRING(@SQLVersion, 1, CHARINDEX('.', @SQLVersion) - 1)
-- Display the major build
SELECT @SQLVersion