Thursday, 8 September 2011

SQL 2008 : Schema Gotcha!

A colleague and I wasted a good deal of time trying to debug a problem today.
Why did the application work, but running the same SQL in Management Studio did not!

It turned out that the application logged in as 'appuser' and had a default schema 'schema1'.
When running inside Management Studio he was logged in as sa (it's a developer's local machine).
sa being a sysadmin, it has the default schema dbo. The old code (ported from sql 2000, no schema given in the query) therefore didn't know to look in 'schema1' for the objects and failed.

Therefore, always prefix object names with the owner (sql 2000) or the schema (2005+)
It will also save SQL trying to find a schema.

No comments: