'sys.sp_dbcmptlevel' can only be executed at the ad hoc level

hi

i want to change the Compatibility level of the database(currently in use),autocommit is ON

as

EXEC sp_dbcmptlevel DM, 90 (OK in SQL-server 2005, no error)

Now when execute this command in my application(build using Centura) throws error as

Microsoft SQL Server:15432[Microsoft][ODBC SQL Server Driver][SQL Server]Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

I check the docs, what i find this Error comes only if u try to execute this in other procedure or uncommited Transation.

there is no such thing i already checked that then

What this error mean?

any hint?

Gurpreet S. Gill

I don't know what Centura is or how it works. You are most probably using some construct that results in the use of sp_executesql to run the statement from the client like:

exec sp_executesql N'EXEC sp_dbcmptlevel DM, 90';

This will happen in ADO.NET for example if you use a parameterized statement. You need to use direct execution mode of operation. So check your documentation on how to run SQL statements in direct execution mode. You can also trace the calls from your application to see the command it is sending and that should give you a clue.

|||

I don't think that's it, Umachandar. I think it's just what he said - he wrote a proc to exec sp_dbcmptlevel and it throws the error if it's inside another proc. Run this and you'll see what I mean:

CREATE PROC dbo.testing (@.DbName SYSNAME) AS

EXEC sys.sp_dbcmptlevel @.dbname=@.DbName, @.new_cmptlevel=90

GO

EXEC testing Adventureworks

Msg 15432, Level 16, State 1, Procedure sp_dbcmptlevel, Line 28

Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

If you try to execute it using EXEC() or sp_executesql, it still throws the same error:

CREATE PROC dbo.testing (@.DbName SYSNAME) AS

DECLARE @.DynamicSql NVARCHAR(255)

SET @.DynamicSql = 'EXEC sys.sp_dbcmptlevel @.dbname=' + @.DbName + ', @.new_cmptlevel=90'

EXEC (@.DynamicSql)

GO

EXEC testing Adventureworks

|||

The problem is this part of code of sp_dbcmptlevel:

if (@.@.nestlevel > 1)

begin

raiserror(15432,-1,-1,'sys.sp_dbcmptlevel')

return (1)

end

And BOL says: "

When @.@.NESTLEVEL is executed within a Transact-SQL string, the value returned is 1 + the current nesting level. When @.@.NESTLEVEL is executed dynamically by using sp_executesql the value returned is 2 + the current nesting level.

"

How it is possible to bypass this check? Why does Microsoft this check?