sp_dbcmptlevel using @dbname?

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Gerry Hickman - 14 Jul 2008 16:25 GMT

Hi,

I have this T-SQL which seems to work against SQL 2000 databases running on
SQL 2005.

ALTER DATABASE mydb SET SINGLE_USER;
EXEC sp_dbcmptlevel mydb, 90;
ALTER DATABASE mydb SET MULTI_USER;

My question is, how can I re-write this such that I can replace 'mydb' with
a variable such as @dbname?

I tried using EXEC() with 'dynamic SQL' but all three statements fail. The
ALTER statements give "Permission Denied" and the sp_dbcmptlevel says you
can only run it at the 'ad hoc' level.

Signature

Gerry Hickman
London (UK)

Reply to this Message

Linchi Shea - 14 Jul 2008 16:57 GMT

You can try the following:

declare @sql varchar(200)
declare @db sysname
set @db = 'test'

set @sql = 'ALTER DATABASE ' + @db + ' SET SINGLE_USER;'
EXEC (@sql)
EXEC sp_dbcmptlevel @db, 80;
set @sql = 'ALTER DATABASE ' + @db + ' SET MULTI_USER;'
EXEC (@sql)

If you look at the code of sp_dbcmptlevel, you'll see that it checks
@@nestlevel upfront.

Linchi

> Hi,
>
[quoted text clipped - 11 lines]
> ALTER statements give "Permission Denied" and the sp_dbcmptlevel says you
> can only run it at the 'ad hoc' level.

Reply to this Message

Gerry Hickman - 15 Jul 2008 09:20 GMT

Thanks Linchi,

It's now working! You pointed out you can pass the database name direct as
the first param to sp_dbcmptlevel, no need to build a string first, that was
my first mistake. The reason the ALTER statements were not working for me
was due to a typo in the database name I was using to build the strings.

> You can try the following:
>
[quoted text clipped - 31 lines]
>> ALTER statements give "Permission Denied" and the sp_dbcmptlevel says you
>> can only run it at the 'ad hoc' level.

Reply to this Message