# Wednesday, 08 October 2008

A while back, I wrote about using sp_ procedures in master to create a toolbox, which works fine for me... until recently I hit a strange problem. I developed a procedure that would read the definition of a table and would create a change_log table and trigger to populate that table. On my dev-environment (Windows Vista x64, SQL Server 2005 Dev Ed x64 SP2) this worked like I expected. But when I tested the procedure on a different server, it didn't!?! A very little sample that touches the essence of the problem I witnessed;

use master
go
if
object_id('sp_test','p') is not
null
drop procedure sp_test
go
create
procedure sp_test
as
select
db_name() + '.' + table_schema + '.' + table_name
from information_schema.
tables
order by table_schema, table_name
go
exec
sp_test
go
use
adventureworks
go
exec
sp_test
go
use
master
go
drop
procedure sp_test
go

Now on my dev machine, this once listed all tables in master and once all tables in AdventureWorks, as intended. But on other servers, it returned the tables from master on both executions of sp_test (the db_name function is executed correctly in both cases; once master, once AdventureWorks). For some reason, when referencing objects, the stored procedure stayed to master. I was puzzled, but before crying BUG (out loud) I tried the newsgroups and a prompt reply from SQL Server MVP Roy Harvey pointed me to the undocumented stored procedure sp_MS_marksystemobject. Simply execute;

exec sp_MS_marksystemobject sp_test

and you won't suffer from the inconsistent behavior I witnessed.

Technorati tags: ,
Wednesday, 08 October 2008 17:51:28 (W. Europe Daylight Time, UTC+02:00)

Just checked the Prometric site and the status for my 70-432 (71-432) and 70-448 (71-448) changed from tested to passed ;-).

Technorati tags: , ,
Wednesday, 08 October 2008 10:55:23 (W. Europe Daylight Time, UTC+02:00)