how to write a generic script without explicitly writing the database name when it is a mandatory parameter

Question

I need to run a script like

EXEC sp_dbcmptlevel AdventureWorks, 100;
GO

Now I'd like to have a generic version that uses the database I am connected to, is it possible in T-SQL?

Something like this, but this doesn't work:

EXEC sp_dbcmptlevel DBNAME(), 100;
GO

It doesn't work of course because a string is returned, I'd like to have the database referece returned... Is it possible?

Note Of course I can do it from a client application and using a parameter, like (Delphi example)

EXEC sp_dbcmptlevel :CurrentDatabase, 100;
GO

and replace it at runtime, but I'd like to have T-SQL only code.

retired · Accepted Answer · 2010-07-13 14:49:33Z

3

You can use the EXEC approach.

declare @x nvarchar(2000)
set @x = 'alter database [' + DB_NAME() + '] set compatibility_level = 100;'
exec (@x)

BOL 2008 declares that sp_dbcmptlevel is deprecated, and to use ALTER DATABASE instead. I don't know whether 2005 supports this or not.

answered Jul 13, 2010 at 14:49

retired

6,8902 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

5 Comments

UnDiUdin Over a year ago

Thanks a lot, a GREAT ANSWER FULL OF INFORMATION. It solved my problem and made me avoid the use of a deprecated feature.

Greg Woods Over a year ago

Doesn't work on SQL2005. Just use the above EXEC approach with sp_dbcmptlevel instead.

Greg Woods Over a year ago

Tried to use the above EXEC approach with sp_dbcmptlevel instead, and got: Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level :(

retired Over a year ago

Hi @Greg Woods - have you tried (replace [at] with @ symbol): declare [at]currentDbName sysname; set [at]currentDbName = db_name(); exec sys.sp_dbcmptlevel [at]dbname = [at]currentDbName, [at]new_cmptlevel = 80;

Greg Woods Over a year ago

Thanks for making me look at this again, I'd missed the 'exec'. Doh!

Gabriele Petrioli · Accepted Answer · 2010-07-13 14:49:46Z

1

You can try

DECLARE @dbname AS nvarchar(500)
set @dbname = db_name()
exec sp_dbcmptlevel @dbname, 100;

Your server must of'course support the compatibility level you try to set ..

You should also have a look at this http://msdn.microsoft.com/en-us/library/ms178653.aspx

answered Jul 13, 2010 at 14:49

Gabriele Petrioli

197k35 gold badges275 silver badges331 bronze badges

2 Comments

Greg Woods Over a year ago

Sorry, doesn't work. Incorrect syntax near 'sp_dbcmptlevel'.

Greg Woods Over a year ago

My mistake, I'd missed out the 'exec' at the start of the line

Collectives™ on Stack Overflow

how to write a generic script without explicitly writing the database name when it is a mandatory parameter

2 Answers 2

5 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related