Need SQL for distinguishing SQL 2005 from 2000 and 7.0

I want to use 'ALTER LOGIN' if the SQL Server is running 2005, but have to use sp_password if it's SQL 2000 or 7.0

Currently I use the following SQL to determine the version:

int nVersion = 7;
// this will throw an exception on 7.0
rs = con.execute("SELECT SERVERPROPERTY('productversion')");
if (rs != null && !rs.getEOF())
{
strVersion = rs.getField(0).getString();
if (strVersion == null)
strVersion = "7.";
strVersion = strVersion.trim();
int nIdx = strVersion.indexOf(".");
strVersion = strVersion.substring(0, nIdx);
nVersion = Integer.parseInt(strVersion);
}

Can anyone suggest a cleaner way?

Hi,

if you have the capabilities to use SQLDMO, this would be a snippet for it:

Set oSQLObj = CreateObject("SQLDMO.SQLServer")
version = oSQLObj.PingSQLServerVersion(sServer)

Otherwise if you have SQL Server 2005 and the SMO dll use can also use SQLSMO from .NET.

HTH; Jens Suessmeyer.

http://www.sqlserver2005.de