Sometime we need to provide one script that works on multiple server, here is how to do in for SQL Servers 2000 and 2005.

We will use simple technique to detect the server type using the serverproperty('ProductVersion') for a list of options visit

SERVERPROPERTY (Transact-SQL). So lets see what are the pros and cons

of this technique.

Pros

This technique allows us to use either SQL 2000 or 2005 servers.

Cons

Cant’ just use new sql 2005 syntax since 2000 does not recognize it, so we are executing it with exec after the new code string have bean concatenated.

This could easily leave us open to sql injection if we are not sanitizing the input correctly in web environment.

Another approach would be to execute a script file with the osql or sqlcmd

 
              EXEC master..xp_cmdshell 'OSQL -S devserver -U sa -P pass -ic:\script2000.sql -n'
 
              EXEC master..xp_cmdshell 'SQLCMD -S devserver -U sa -P pass -ic:\script2005.sql'

Complete Example

 
    DECLARE @sql2005Code VARCHAR(255)             
 
    SET @sql2005Code = 'print ''SQL 2005 Code'' '
 
    DECLARE @ver nvarchar(128)
 
    SET @ver = CAST(serverproperty('ProductVersion') AS nvarchar)
 
    SET @ver = SUBSTRING(@ver, 1, CHARINDEX('.', @ver) - 1)
 
            -- SQL 2000
 
    IF ( @ver = '8' ) BEGIN
 
            print 'SQL 2000'
 
    END
 
            -- SQL 2005
 
    ELSE IF ( @ver = '9' )BEGIN
 
      EXEC (@sql2005Code)
 
    END