how to make a condiction with mssql version?

Hello guys

I have a script that I need to run in my clients but some of them have the mssql 2005 and others mssql 2014

I need to make something this:

if mssql_version = 2005 begin
   
end else if mssql_version = 2008 begin

end else if mssql_version = 2014 begin

end;


Thanks
alexandre
LVL 1
hidrauAsked:
Who is Participating?
 
Pawan KumarConnect With a Mentor Database ExpertCommented:
got it. Please use like below -

IF CHARINDEX('2005',@@version,0) >0  or CHARINDEX('2008',@@version,0) > 0  
BEGIN
	
	if exists (select * from Master..sysdatabases where Name = '<BANCO>') begin
	 use Hermes;

	 exec sp_dboption 'Hermes', 'trunc. log on chkpt' , 'on';
	 exec sp_dboption 'Hermes', 'select into/bulkcopy', 'on';
	 exec sp_dboption 'Hermes', 'ANSI null default'   , 'on';

	 --removido o autoshrink para não alterar o tamanho do banco de dados
	 exec sp_dboption 'Hermes', 'autoshrink'          , 'off';
	 exec sp_dboption 'Hermes', 'auto update statistics', 'off';

	 --trunca as informações do arquivo de log para reduzir o tamanho
	 if dbo.fnSQL_VersaoMaiorSQL() < 10  begin
 		exec('BACKUP LOG Hermes WITH TRUNCATE_ONLY');
    
		use master;
		if (select value from sysconfigures where config = 106) < 50000 begin
    		exec sp_configure 'min server memory',20;
    		exec sp_configure 'locks',50000;
    		reconfigure with override; 
		 end; 
	 end;	 

END;
ELSE 
BEGIN

	 

END;

Open in new window

1
 
Pawan KumarDatabase ExpertCommented:
please provide full code...Please provide the script also.
0
 
hidrauAuthor Commented:
For examplo, this code doesn't work in 2014, then I need to change it to another way. But for 2008 and 2005 it works fine.

if exists (select * from Master..sysdatabases where Name = '<BANCO>') begin
 use Hermes;

 exec sp_dboption 'Hermes', 'trunc. log on chkpt' , 'on';
 exec sp_dboption 'Hermes', 'select into/bulkcopy', 'on';
 exec sp_dboption 'Hermes', 'ANSI null default'   , 'on';

 --removido o autoshrink para não alterar o tamanho do banco de dados
 exec sp_dboption 'Hermes', 'autoshrink'          , 'off';
 exec sp_dboption 'Hermes', 'auto update statistics', 'off';

 --trunca as informações do arquivo de log para reduzir o tamanho
 if dbo.fnSQL_VersaoMaiorSQL() < 10  begin
 	exec('BACKUP LOG Hermes WITH TRUNCATE_ONLY');
    
    use master;
    if (select value from sysconfigures where config = 106) < 50000 begin
    	exec sp_configure 'min server memory',20;
    	exec sp_configure 'locks',50000;
    	reconfigure with override; 
     end; 
 end;
 use Hermes;
end;
go

Open in new window

0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
Pawan KumarDatabase ExpertCommented:
Please try like--

IF mssql_version = 2005 or mssql_version = 2008
BEGIN
	
	if exists (select * from Master..sysdatabases where Name = '<BANCO>') begin
	 use Hermes;

	 exec sp_dboption 'Hermes', 'trunc. log on chkpt' , 'on';
	 exec sp_dboption 'Hermes', 'select into/bulkcopy', 'on';
	 exec sp_dboption 'Hermes', 'ANSI null default'   , 'on';

	 --removido o autoshrink para não alterar o tamanho do banco de dados
	 exec sp_dboption 'Hermes', 'autoshrink'          , 'off';
	 exec sp_dboption 'Hermes', 'auto update statistics', 'off';

	 --trunca as informações do arquivo de log para reduzir o tamanho
	 if dbo.fnSQL_VersaoMaiorSQL() < 10  begin
 		exec('BACKUP LOG Hermes WITH TRUNCATE_ONLY');
    
		use master;
		if (select value from sysconfigures where config = 106) < 50000 begin
    		exec sp_configure 'min server memory',20;
    		exec sp_configure 'locks',50000;
    		reconfigure with override; 
		 end; 
	 end;
	 use Hermes;
	end;

END;
ELSE 
BEGIN

	 

END;

Open in new window

0
 
hidrauAuthor Commented:
Does this exist  "mssql_version" ?

Mensagem 207, Nível 16, Estado 1, Linha 1
Invalid column name 'mssql_version'.
0
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can use SERVERPROPERTY function:
IF SERVERPROPERTY('ProductVersion') > '9'
	BEGIN
		PRINT 'SQL Server 2005'
	END
ELSE IF SERVERPROPERTY('ProductVersion') > '12'
	BEGIN
		PRINT 'SQL Server 2014'
	END
ELSE IF SERVERPROPERTY('ProductVersion') > '11'
	BEGIN
		PRINT 'SQL Server 2014'
	END
ELSE IF SERVERPROPERTY('ProductVersion') > '10'
	BEGIN
		PRINT 'SQL Server 2008'
	END

Open in new window

2
 
hidrauAuthor Commented:
thanks friend for your help
0
 
hidrauAuthor Commented:
it was to explit the point and it didn't happen. :(

Sorry Pawn :((
0
 
Pawan KumarDatabase ExpertCommented:
No problem, you can ask the moderators to open the question for you and then you can re close the question.

There is a report question link at the top of the question. You need to click on that and then you can ask the mods to open this for you.
1
 
hidrauAuthor Commented:
thansk for the clue Pawan
0
 
hidrauAuthor Commented:
Thanks a lot for your help
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.