SELECT @@VERSION on Different Servers

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SELECT @@VERSION on Different Servers

Author

Topic

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-01-31 : 06:24:00

Hi All

I was wondering if it is possible to gather the SQL Version from lost of different servers in one go?

For example:

I have a a dtsx package in SSIS and I want to ad a query which connects to lots of other SQL Serves and returns their SQL version. I know when you are logged onto the server you can run SELECT @@VERSION but can you do SELECT @@VERSION from <SERVER>.<DB>.<TABLE>

Thanks

Gopher

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-31 : 06:35:18

Of course

SELECT DISTINCT	@@VERSION
FROM		master..spt_values

@@VERSION system function is per server, not per table.

E 12°55'05.25"
N 56°04'39.16"

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-01-31 : 09:02:27

Thats great, however - and this is where it gets tricky!! (ok, For me anyway!)

That results is:
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

However I am only interesed in the:

Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)

and

Microsoft Corporation Enterprise Edition on Windows NT 5.2 (Build 3790: Service Pack 1)

Is there a query I can use to just bring back that info, alo I am assuming the query will work on SQL2000?

Thanks

Gopher

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-31 : 09:08:45

Tricky? Some string manioulation only...

SELECT DISTINCT	LEFT(@@VERSION, CHARINDEX(')', @@VERSION))
FROM		master..spt_values

E 12°55'05.25"
N 56°04'39.16"

Gopher
Yak Posting Veteran

83 Posts

Posted - 2008-01-31 : 10:37:02

Cool!

I assume the othe info is someting like:
SELECT DISTINCT RIGHT(@@VERSION, CHARINDEX(')', @@VERSION))
FROM master..spt_values

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2008-01-31 : 11:11:45

No.

SELECT DISTINCT SUBSTRING(@@VERSION, CHARINDEX(')', @@VERSION) + 1, 8000)
FROM master..spt_values

E 12°55'05.25"
N 56°04'39.16"

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources