Prev: Slide database image
Next: Forget any DBMS database?
From: Roy Goldhammer on 12 Jul 2010 08:41
Hello there

I change some of my procedures as schema

It works fine.

but when i saw the definition of these procedures in sys.sql_modules the
create statement still goes to DBO.

why?


From: Erland Sommarskog on 12 Jul 2010 16:43
Roy Goldhammer (royg(a)yahoo.com) writes:
> I change some of my procedures as schema

With ALTER SCHEMA?

> but when i saw the definition of these procedures in sys.sql_modules the
> create statement still goes to DBO.

You would need to use ALTER PROCEDURE to fix that part, if it is an issue
for you.

The same thing if you rename a procedure - the stored text is left
unchanged.

--
Erland Sommarskog, SQL Server MVP, esquel(a)sommarskog.se

Links for SQL Server Books Online:
SQL 2008: http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005: http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx
SQL 2000: http://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

From: John Bell on 12 Jul 2010 16:46
On Mon, 12 Jul 2010 15:41:11 +0300, "Roy Goldhammer" <royg(a)yahoo.com>
wrote:

>Hello there
>
>I change some of my procedures as schema
>
>It works fine.
>
>but when i saw the definition of these procedures in sys.sql_modules the
>create statement still goes to DBO.
>
>why?
>

This is similar behaviour to sp_rename and there is a comment in BOL
for it. If you actually scripted the procedure it would be correct.
but it means that OBJECT_DEFINITION() will be incorrect.

it's logged as a outstanding bug
http://connect.microsoft.com/SQLServer/feedback/details/432565/renaming-a-stored-procedure-will-not-change-definition-in-sql-modules

John
 | 
Pages: 1
Prev: Slide database image
Next: Forget any DBMS database?