SQL Server Performance Forum – Threads Archive

SQL 2005 compatibillity level question

Hi,<br /><br />We are using SQL 2005 sp2 but all user databases are set to compatibility level to 80. <br />Is it possible to set a different SQL compatibility level on a specfic stored procedure or function. For example, if I want to create a stored procedure using a SQL 2005 feature, can I set that stored procedure to use the compatibility level 90 even the database is set to 80? <br /><br />The feature we want to use is the assembly and we create a function to call that assmebly. The error is on the key word ‘External’ as shown here below.<br /><br />/****** Object: UserDefinedFunction [dbo].[fn_ABC] Script Date: 05/29/2007 15:12:33 ******/<br />CREATE FUNCTION [dbo].[fn_ABC](@origString [nvarchar](4000), @key [nvarchar](204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ />)<br />RETURNS [nvarchar](204<img src=’/community/emoticons/emotion-11.gif’ alt=’8)’ /> WITH EXECUTE AS CALLER<br />AS EXTERNAL NAME [asm_ABC].[ABC.ABC].[Decrypt]<br /><br /><br /><br />wingman

---

Compatibility mode is based on database not base on object….so you can’t do set the stored procedure to different cmpt level.. I believe creating the assembly is based on master database cmpt level not the user db…
I think you can create the assembly but you can’t be used in user db context…
MohammedU.
Moderator
SQL-Server-Performance.com All postings are provided “AS IS” with no warranties for accuracy.

---

Thank for the info. Yes, before I sent my previous email, I have tried to set the master db to the compt level 90 but it still doesn’t work. Sometimes it is quite confusing. Some new features in SQL 2005 would work in the compt level 80 but some don’t like the function I listed in the email. Besides the master db cmpt level, do you have any other explanation?

---

If that function originates from Master and need to perform tasks on user database with 80 then it will not work as features of assembly in SQL 2005 need to have 90 for master & corresponding user databases too. Satya SKJ
Microsoft SQL Server MVP
Writer, Contributing Editor & Moderator
http://www.SQL-Server-Performance.Com
This posting is provided AS IS with no rights for the sake of knowledge sharing. Knowledge is of two kinds. We know a subject ourselves or we know where we can find information on it.

---

]]>