xp_msver 1

[Mdavis123]

I'm trying to get the SQL Server version incorporated into some SP and I need to know what the exact use of
xp_msver. I'm looking to evaluate 8.xx or 7.xx
TIA
Mike Davis

 

[tlbroadbent]

Please explain how you want to incorporate the version in a Stored Procedure? Check the following resources.

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q191867

http://msdn.microsoft.com/library/en-us/tsqlref/ts_xp_aa-sz_0o4y.asp

http://msdn.microsoft.com/library/en-us/tsqlref/ts_globals_5zji.asp

Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Mdavis123]

I want to do do something like this:
if @VerN0>=7.0
Alter Table blabla...
else
RaisError('Please call Tech Support',16,1)

I cant get @verNo populated with the Version Information.
Trying not to do a left(substring...

Thanks Terry

 

[tlbroadbent]

Try this.

Declare @verno varchar(12)

--Create temporary table to hold
--output from xp_msver
Create table #ver
(ID Int, OptName varchar(20),
IntValue int Null,
CharValue varchar(12))

--Insert output of xp_msver into temp table
Insert #ver
Exec master..xp_msver ProductVersion

--Obtain version number
Select @verno=CharVal
From #ver
Where OptName='ProductVersion'

--Drop temp table
Drop table #ver

--Proceed with existing code
If @ver>='7.00.000'
... Terry L. Broadbent - DBA
Computing Links:

http://tlbroadbent.home.attbi.com/prog.htm

faq183-874 contains "Suggestions for Getting Quick and Appropriate Answers" to your questions.

 

[Mdavis123]

Terry, Thanks for the info. I opted for the Substring instead because it was shorter.
Mike

Declare @Emsg varchar(200)
Set @Emsg='Please call Support for help adding a column.'
-- if version is less than 7.5 exit with error message
if convert(float,substring(@@version,22,4))<7.5
Begin
select @Emsg
Return
End
--Check to see if table Exist
Declare @id int
select @id=id from sysobjects where name='T4W_Users'
if @id is null
Begin
Select 'T4W_Users table does not exist'
return
End
--Check to see if column exist, if not add it

select @id=count(id) from syscolumns where id=
(select id from sysobjects where name='T4W_Users')
AND Name='OP__LASTLOGINDATETIME'
if @id=0
Begin
Alter Table T4W_Users add OP__LASTLOGINDATETIME Datetime null
Select 'Column Added'
End
Else
Select 'Column already exist'

Return
--End of Script