Getting SQL Server version of linked server

Question

I have many linked servers in my SQL Server. A need has arisen that getting SQL Server version of these linked servers in a script. I find following command that return SQL Server version of current server

SELECT convert(varchar(max),serverproperty(''productversion''))

How can I use this command on linked server? Of course, I have to specify linked server as variables.

Answer 1

Unless your main server is 2000 or older, you can use something like this:

exec ('select serverproperty(''productversion'');') at [LinkedServerName];

This line will have to be executed as dynamic SQL. The list of linked servers' names can be obtained from sys.servers. Also, this might require some options to be enabled in linked servers' properties (forgot which one, unfortunately, but the error message will tell which one needs to be enabled).

See EXECUTE for details.

I'm not saying it's the best possible option, but it definitely should work, unless your server's menagerie is really old. Barring that, there are other similar approaches, such as OPENQUERY(). This one should work even on 2000, actually.