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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
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
10 Tips to Protect Your Business from Ransomware

Did you know that ransomware is the most widespread, destructive malware in the world today? It accounts for 39% of all security breaches, with ransomware gangsters projected to make $11.5B in profits from online extortion by 2019.

hidrauAuthor Commented:
Does this exist  "mssql_version" ?

Mensagem 207, Nível 16, Estado 1, Linha 1
Invalid column name 'mssql_version'.
0
Pawan KumarDatabase 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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Vitor MontalvãoMSSQL 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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.