CREATE BLOG dbo.SqlServer: Stored procedure 'sys.sp_dbcmptlevel' can only be executed at the ad hoc level

Thursday, August 31, 2006

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

I ran into a weird bit today while trying to write a generic "Update SQL Server 2000 database to SQL Server 2005" script. What I was trying to do was to set all of the database options and settings to the "normal" SQL Server 2005 when you restore a database. Basically the process would be (1) restore SQL Server 2000 database onto SQL Server 2005 server, and (2) run a stored procedure to ALTER DATABASE .. SET <Property> ON|OFF|ETC. You know - SET ANSI_NULLS ON - that sort of thing...

Anyway, I ran into a problem with sp_dbcmptlevel - one that I'd never seen before (because I'd never tried to execute it from within another context). Read this from BOL:

Each sp_dbcmptlevel call must be submitted individually. sp_dbcmptlevel cannot be called from within other contexts, such as a:

Stored procedure.

Transact-SQL string executed with the EXEC(string) syntax.

Batch of Transact-SQL statements.

Well sonuva... There goes my idea for having a generic DBA_UpdateSqlServer2000DatabaseSettings proc.

I read through the rest of the docs on sp_dbcmptlevel and I can understand the logic now. I won't bore myself with reposting all of BOL but feel free to read up on for yourself.

Damn. I really wanted a single stored procedure to do everything... Anyway, here's one script to upgrade your sql server 2000 databases to 2005. I posted it into the LearnSqlServer.com SQL Server Code Samples repository this morning...

Technorati Tags: Scott Whigham, TSQL, SQL Server, SQL Server 2005, SQL Server 2000, Upgrade SQL Server, SQL Server Upgrade