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.
A:
SELECT @@VERSION
Returns a string detailing the version of the server.
HollyStyles
2009-06-04 09:43:22
@@Version does only work on the local server, not on the linked server
Coentje
2009-06-04 09:43:51
Same with SERVERPROPERTY('productversion')
Coentje
2009-06-04 09:44:08
+2
A:
select * from openquery(MyLinkedServer,'SELECT SERVERPROPERTY(''productversion'')')
Works
SWeko
2009-06-04 09:58:07
+3
A:
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.
Paul Harrington
2009-06-05 00:36:17
+1 because this answer is more versatile and can be used in a more flexible way when needed
Coentje
2009-06-05 10:56:15
I got so used to openquery, I didn't ever bother to try EXEC ... ATThis is way more flexible for calling server procs and parametrized queries. However, for simple things, openquery is more straight-forward.
SWeko
2009-06-09 08:50:23