Is using the DATABASEPROPERTYEX appropriate for getting version?

[sonname]

I am converting over some code from SQL Server 2000 to SQL Server 2005 and I am having some problems with the sp_addlogin calls that I make in SQL Server 2000. The CREATE LOGIN command is the preferred call for SQL Server 2005. I was thinking of checking the sql version in my code to determine what sql version is running and call sp_addlogin for SQL Server 2000 and CREATE LOGIN for SQL Server 2005. I was thinking of doing this so that I can maintain one source code. My question is, should I use Select DATABASEPROPERTYEX('master', 'version') to determine the version? I know that for SQL 2005 this returns 611 and 539 for SQL Server 2000. Would these numbers change if for example some service packs were installed? Is there a better way of checking the version? Thanks in advance.

 

[ptheriault]

yes, use select @@vesion

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

Paul,

I understand that folks from your area of the country often drop their [!]R[/!]'s during verbal conversations. I guess this applies to written documents too?

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[ptheriault]

Thanks for pointing that out George...



- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[sonname]

ptheriault,
The problem with select @@version is that it returns this long string and I want to be able to do an if statement in my code where I check for the version and do something differently based on 2000 or 2005. I would need to do a substring to get what I want from the string and then I run the risk of that string changing over time.

 

[ptheriault]

use select substring(@@version,29,1) as version

So you would have something like this.

if select substring(@@version,29,1) = 9
BEGIN
CREATE ...
END
ELSE
BEGIN
sp_addlogin ...
END

keep in mind you can't compile this as a procedure because of the mixed 2000 and 2005 syntax.

- Paul

- If at first you don't succeed, find out if the loser gets anything.

 

[gmmastros]

if @@microsoftversion >= 150996343
    Select 'SQL 2005'
Else
    Select 'SQL 2000'

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SQLDenis]

select serverproperty('ProductLevel'),serverproperty('ProductVersion')

will return SP3 and 8.00.760 for 2000
on my machine

and SP2 and 9.00.3042.00 for 2005


so check the first digit of serverproperty('ProductVersion')

if its 8 then 2000 if its 9 then 2005


Denis The SQL Menace
--------------------
SQL Server Code,Tips and Tricks, Performance Tuning
Google Interview Questions

 

[sonname]

Thanks ptheriault,
I wasn't aware that it wouldn't compile in 2005. The symptoms that I was getting when using sp_addlogin in 2005 was that it would complain if it went against windows password policies. This is why I wanted to put that version logic in my code.

 

[ptheriault]

The problem with propertyex is that it could change with SP.

- Paul

- If at first you don't succeed, find out if the loser gets anything.