Executing a stored proc that refers table of a default schema
Hi Guys I am currently working on sql 2005. I have a stored proc like this :- create proc reporting.sp_test as select * from test exec reporting.sp_test As you can see this is a stored proc belonging to a schema 'reporting' and referring to a table with default schema 'dbo'. On executing it I get a error message which says that - test does not exist. However when I tested this in sql 2008, it works fine. Is it a bug in sql 2005? On the hindsight I know sql 2005 is getting outdated and there would be no support from micrsoft from next month. But lets not go on that road, because client is ready to take the risk. Thanks
It seems the behaviour changed in 2008. It looks like in 2005 the default schema is the schema of the caller (in this case the SP) and in 2008 it's default dbo (in 2014 also) Make sure you always define a schema. It's also an improvement because MSSQL doesn't have to determine the default schema.