Determine the SQL Server version of remote servers

Technorati Tags: ,

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)
    DROP TABLE #Results
-- 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


Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s