sys.sp_dbcmptlevel problem - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

sys.sp_dbcmptlevel problem

Author

Topic

gsgill76
Posting Yak Master

137 Posts

Posted - 2006-10-20 : 01:13:36

CREATE PROC dbo.testing (@DbName varchar(10)) AS
DECLARE @DynamicSql NVARCHAR(255)
SET @DynamicSql = 'EXEC sys.sp_dbcmptlevel @dbname=' + @DbName + ', @new_cmptlevel=90'
EXEC (@DynamicSql)
GO

EXEC testing Adventureworks
OUTPUT:
'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

I try this but same error

CREATE PROC dbo.testing (@DbName SYSNAME) AS
EXEC sys.sp_dbcmptlevel @dbname=@DbName, @new_cmptlevel=90
GO

EXEC testing Adventureworks
OUTPUT:
'sys.sp_dbcmptlevel' can only be executed at the ad hoc level.

I know why this error come
"The procedure 'sys.sp_dbcmptlevel' cannot be executed within a transaction"

but dont know how to solve this.

Regards,
Thanks.
Gurpreet S. Gill

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources