Witaj na Zine.net online Zaloguj się | Rejestracja | Pomoc

[EN] SQL Server 2008 - a change that breaks my heart

I am really surprised with the change I discovered last week in SQL Server 2008. Let’s analyze the code below:

USE master
GO

IF OBJECT_ID(N'sp_FindObject') IS NOT NULL
  DROP PROC dbo.sp_FindObject
GO

CREATE PROC dbo.sp_FindObject 
@object sysname
AS
SET NOCOUNT ON
SELECT * FROM sys.all_objects WHERE name = @object
GO

IF EXISTS (SELECT * FROM sys.databases WHERE name = N'Test')
  DROP DATABASE Test
GO

CREATE DATABASE Test
GO

USE Test
GO

CREATE TABLE dbo.Table1 (col1 int)
GO

EXEC sp_FindObject Table1

This piece of code illustrates my approach to writing utilities – I create my own stored procedures in the master database named with sp_ prefix that can be executed in any database context and returns the appropriate result for its database objects (I use it together with custom keyboard shortcuts in SSMS). In this case the sp_FindObject utility sould return all information about Table1 from sys.all_objects catalog view in Test database. And it does (at least in SQL Server 2005 build 9.00.3282), but… not in SQL Server 2008 (build 10.00.1763 – CU1). In SQL Server 2008 the procedure’s code executes in a context of the master database (and thus it does not return any row (unless you have Table1 object in master database)! What a breaking change… Of course, you can say there are some workarounds – use sp_executesql (I’m not sure if it helps) or old fashioned dbo.sys… compatibility views (not recommended by Microsoft). But both seem to be a bit worse than my foavourite way to implement utils.

PS. Try to put DB_NAME() function in the select list. It appears that the database context of the procedure execution is appropriate (in this case – Test database)! So what the hell…? I think it’s time for my next Connect item :-)

EDIT: Sebastian Waksmundzki has made some research and it seems marking the util as a system object with sp_MS_MarkSystemObject undocummented stored procedure works like a charm here :-) Ugly trick I must say...

Opublikowane 10 listopada 2008 22:36 przez brejk

Komentarze:

# re: [EN] SQL Server 2008 - a change that breaks my heart

10 listopada 2008 23:32 by qmiswax

I did check on SQL 2005 9.00.3073.00 and your sp does not return any result. Well it looks like  we have version depended functionality :)

# re: [EN] SQL Server 2008 - a change that breaks my heart

11 listopada 2008 00:03 by brejk

It seems marking sp_FindObject as a system object would do the thing :-)

# re: [EN] SQL Server 2008 - a change that breaks my heart

11 listopada 2008 00:07 by brejk

Thanks Sebastian for this research. Anyway, this is ugly trick :P

# re: [EN] SQL Server 2008 - a change that breaks my heart

11 listopada 2008 00:29 by Sebastian aka qmiswax

Well I've just found confirmation of that "charm :)"  Please have a look here  (Kimberly's blog)

http://www.sqlskills.com/blogs/kimberly/post/sp_helpindex2-to-show-included-columns-(20052b)-and-filtered-indexes-(2008)-which-are-not-shown-by-sp_helpindex.aspx .  

"I used the undoc'ed sp_MS_marksystemobject so that I could still create the sp_ in master but then execute it in all other databases. It's frustrating that this behavior (with sp_ named objects) no longers works in 2005/2008 but at least the sp_MS_marksystemobject still sets the behavior so that we can create this one proc in master but use it in all other databases"

So it looks like old trick with sp prefix is not longer supported but .... :) workaround exits

# re: [EN] SQL Server 2008 - a change that breaks my heart

11 listopada 2008 04:13 by theos

and you can read about this problem and sp_MS_marksystemobject procedure in Inside SQL Server 2005: TSQL Programming (pages: 276->281 of polish edition).

sp_MS_marksystemobject is not only undocummentd but not supported too ;/// (do not use it on your production env).

# re: [EN] SQL Server 2008 - a change that breaks my heart

11 listopada 2008 11:35 by brejk

@theos: Hopefully, I'm using my utils only in dev env :-) Thus sp_MS_marksystemobject is ok for me (but of course that does not change my feeling this is a terrible technique).

# re: [EN] SQL Server 2008 - a change that breaks my heart

11 listopada 2008 11:47 by brejk

UPDATE: I've been playing with sp_executesql and it seems that queries to system catalogs executed by this procedure inside the utility run in the appropriate database context even without use of sp_MS_marksystemobject.

Komentarze anonimowe wyłączone

About brejk

MVP, MCT, SQL Server geek