Default Schema within storded procedure

I'm migrating a dotnetnuke website from SQL Server 2000 to SQL Server 2005 and have run into a problem with one of the stored procedures.

The database objects seem to have upgraded successfully to use the db schema identifer from the dbowner identifier. However I am having a problem with a particular stored procedure trying to execute another stored procedure.

When the following procedure is called, it seems that the db engine has forgotten the schema context and therefore can't find the called procedure. Has anyone come across this before and is there a workaround other than modifing every SP that uses EXEC?

ALTER PROCEDURE [myschema].[dnn_Forum_StatisticsGet]

(

@.ModuleID int,

@.UpdateWindow int = 12,

@.TabId int

)

...

BEGIN

EXEC dnn_Forum_AA_StatisticsSiteUpdate 0, 0, @.ModuleID, @.TabId

END

...

What about full qualifying the procedure call or using the EXECUTE AS OWNER predicate ?

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

Jens K. Suessmeyer wrote:

What about full qualifying the procedure call or using the EXECUTE AS OWNER predicate ?

Thanks for responding Jens. I've considered this approach but it is a last option. It would require modification of a number of procedures and I can't predict where the problem might occur. Ideally I need a solution where the behaviour of the EXEC statement defaults to what you have suggested.

Rgds

Colm

|||

Yep, this is the same question as someone asked me during the last session about Best Practices and Performance Tuning. I would not use implicit naming or schemas, do everything explicitly. Although this is just a bit more work and testing to do for you, you will be later on the safe side.

Jens K. Suessmeyer

http://www.sqlserver2005.de

|||

The problem is that we are including components from 3rd parties that are expecting the default behaviour of the EXEC statement to default the schema correctly according to the invoking user. From what I can see this is not happening in SQL2005. In SQL 2000 the default was the dbo. Is this a bug in SQL 2005 or normal behaviour?

Rgds

Colm