CODEWITHSUNDEEP
sqlsql-serversql-server-2005t-sql
Coentje
Coentje

Reputation: 520

Determine SQL Server version of linked server

Does anyone here know how i can determine the version of SQL running on my linked server through use of TSQL statements?

I am running SQL2005 my linked servers are running a mix of sql2000, 2005 and 2008.

Upvotes: 4

Views: 5155

Answers (5)

Heather Bomkamp
Heather Bomkamp

Reputation: 11

You can access @@version through a linked server using OPENQUERY

SET @sql = 'SELECT * FROM OPENQUERY(['+@servername+'],''select @@VERSION'')'

Upvotes: 1

Madhivanan
Madhivanan

Reputation: 1

Also you can try:

exec master..xp_msver

Upvotes: 0

Paul Harrington
Paul Harrington

Reputation: 782

One minor nitpick about OPENQUERY is that one cannot use anything other than string literals for both the server and the query.

With EXEC AT you can at least use varchar variables for the query (although it can be a pain to quote the stuff correctly) although not for the server-name:

declare @sql AS varchar(max) = 'SELECT SERVERPROPERTY(''productversion'')' EXEC(@sql) AT MyLinkedServer

I assume this is just a parser limitation rather than some deliberate limitation in the design.

Upvotes: 5

SWeko
SWeko

Reputation: 30912

select * from openquery(MyLinkedServer,'SELECT SERVERPROPERTY(''productversion'')')

Works

Upvotes: 14

hollystyles
hollystyles

Reputation: 5039

SELECT @@VERSION

Returns a string detailing the version of the server.

Upvotes: 0

Related Questions