Getting information about SQL Server
I've always used @@version to get the version on my SQL Server. Well not always, in SQL-DMO, you can use the Server2.VersionString property, which I believe runs @@version to make this happen. However recently I saw a post that asked what the difference was between @@version and sp_server_info as they were reporting different information.
I checked the SQL Server Central server and it was reporting the same information for both methods. Hmmm. I then decided to check a few servers at work and found a number of them to be different. So I investigated further.
My first stop was Books Online. That is usually where I start and we I recommend everyone take a moment to look. It has errors, bugs, can be hard to use, but it is the reference point for SQL Server and worth a look. I didn't find too much about the internals, so the next step was Technet. About the same results.
My next stop was Google, plugging in sp_server_info, which isn't a great idea. This is an old Sybase construct as well so about 70% of my hits were Sybase related. In weeding through the MS hits, it seems that a great many people copied the BOL table word for word, so no new information appeared.
So what is the difference?
Here is what I found. I used Enterprise Manager to decode the sp_server_info command. Using my many years of experience, I was able to decipher this T-SQL:
create proc sp_server_info (
@attribute_id int = null)
as
if @attribute_id is not null
select *
from master.dbo.spt_server_info
where attribute_id = @attribute_id
else
select *
from master.dbo.spt_server_info
order by attribute_id
My years of experience led me to open Query Analyzer and run a select * from master.dbo.spt_server_info. The amazing results will astound you. However, if you have any issues relating them to the output of the stored procedure, please feel free to email and I will assist. So you can see that there is nothing special here.
Also keep in mind the validity of the data depends on some process correctly updating this table as well as no other process or person making changes.
Which I don't. And so I don't necessarily trust this information. But what about @@Version?
A little checking shows that this command actually calls an extended stored procedure. master.dbo.xp_msver to be exact. This stored procedure outputs a number of items, most of which can be checked in as follows. First, browse to your SQL Server installation and find the "binn" folder. Then right click the "sqlservr.exe" file and choose properties. On the Version tab, there are many of the same items that are in the xp_msver result set, along with the corresponding values. The main differences are the hardware values, which I am sure come from querying the underlying Windows OS to see what it detected. I'm not sure how the hardware is queried, but since there are a number of ways to do this (and I'm not concerned about them), I didn't investigate that part.
Conclusions
I had never used the sp_server_info procedure until I saw the post and after digging into this, I wouldn't recommend it. Any time you upgrade your SQL Server, especially with a Service Pack, you are relying on the install program to correctly update sp_server_info, which based on my nonscientific survey, isn't that reliable. I'd suggest you stick with @@Version for your server information.
Steve Jones
©dkRanch.net April 2003