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.

--
Gerry Hickman
London (UK)

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

"Gerry Hickman" wrote:

> 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.
>
> --
> Gerry Hickman
> London (UK)
>
>

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.

"Linchi Shea" <LinchiShea@discussions.microsoft.com> wrote in message
news:0421B58A-B839-417D-A631-F342B8F521CF@microsoft.com...
> 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
>
> "Gerry Hickman" wrote:
>
>> 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.
>>
>> --
>> Gerry Hickman
>> London (UK)
>>
>>