Simons SQL Blog

If you intend using the new custom reports feature of Management Studio and develop a set of reports that you can navigate between then be aware of a  feature where by if you refresh a drill through report the some of the parameters are not maintained.

For a full explanation have a look at the bug submission which includes a couple of sample reports.

https://connect.microsoft.com/feedback/viewfeedback.aspx?FeedbackID=240934&wa=wsignin1.0&siteid=68

I remember coming across this last year when looking at TFS and was shocked.

I thought I heard it was going to be added in an service pack but can't find anything to support it.

If you feel that Keyword expansion is needed then please vote on the suggestion at connect

"Vote here Support for TFS keyword expansion"

One of the great aspects of Data Dude is that it uses SQLCmd to run the scripts. This means that it can use the variable substitution. One gotcha however is that Data Dude tries to be clever and validate your code before the substiution. This means something like.

if exists (select 1 from $(databasename).sys.objects where name = 'blah') doesn't validate because $ is not valid for a object name.

There is an easy workaround. Put [] around your object so the example above becomes

if exists (select 1 from [$(databasename)].sys.objects where name = 'blah')

If you encounter this

Could not obtain information about Windows NT group/user <MyDomain>/<MyName>, error code 0x5.

When you try and CREATE an ASSEMBLY, you might think well I'm not connected as that user. The user it sqawks about is the database owner.

I run on a laptop and often find myself not connected to the domain (although I can still log in with cached domain credentials). When I create a database the database automatically becomes owened by my domain account so when I then try and create an assembly it sqawks because it can't verify the domain account that owns the database.

The simple way around this is to create a sql user account and change the database owner to that user.

In SQL 2005 there is a new feature by which you can extend a result set with the results of another resultset. You might think thats nothing new because thats exactly what a join is, and with the combination of derived tables you have full control, and you can use table valued functions.

However there is one gotcha. If your table valued function takes a parameter(s), that parameter(s) has to be fixed for the query. It can't take the value from a column in another resultset as you do with a JOIN clause.

select *
from myCustomers
join dbo.getCustomersForOrders (myCustomers.customerid)

This is not possible with a JOIN.

Whats new in SQL 2005 is that a new clause CROSS APPLY has been introduced that does exactly what you would expect from the code above.

This will join the customers with the rowset returned from the function for each different customerid.

So what does this mean for dmvs. Well many dmvs are table valued functions and so can be used in this way i.e to get query plans for a plan you can use sys.dm_exec_query_plan(null) however if you try to get index stats for a table an try and use sys.dm_db_index_physical_stats.You will get an error.

This is because some of the dmvs are written using a new mechanism that allows them to be used like table valued functions, i.e. in cross apply. However others are built using an older mechanism that doesn't allow this as shown above.

So whats the solution, well as table valued functions are allowed why not wrap the dmv in a table valued function. The code is cumbersome because you have to define the table the function returns but it works

create function dm_db_index_physical_stats_tvf
(
@db_id int
,@object_id int
,@index_id int
,@partition_number int
,@mode int
)
returns @results TABLE (
[database_id] [smallint] NULL,
[object_id] [int] NULL,
[index_id] [int] NULL,
[partition_number] [int] NULL,
[index_type_desc] [nvarchar](60) NULL,
[alloc_unit_type_desc] [nvarchar](60) NULL,
[index_depth] [tinyint] NULL,
[index_level] [tinyint] NULL,
[avg_fragmentation_in_percent] [float] NULL,
[fragment_count] [bigint] NULL,
[avg_fragment_size_in_pages] [float] NULL,
[page_count] [bigint] NULL,
[avg_page_space_used_in_percent] [float] NULL,
[record_count] [bigint] NULL,
[ghost_record_count] [bigint] NULL,
[version_ghost_record_count] [bigint] NULL,
[min_record_size_in_bytes] [int] NULL,
[max_record_size_in_bytes] [int] NULL,
[avg_record_size_in_bytes] [float] NULL,
[forwarded_record_count] [bigint] NULL
)
begin
insert into @results
select *
from sys.dm_db_index_physical_stats (@db_id, @object_id, @index_id ,@partition_number ,@mode )
return
end

This is a pure plagurism of Jaspers taskpad report. I have reformated the report to look like the taskpad in EM as close as possible.

You can download the taskpad view custom report here

If you've never been to a developer day then your missing a great day.

The day is split into a number of tracks with 5 sessions in each track. All the sessions are given by non-microsoft people and are generally based on real life experience, which makes the content great.

I'll be speaking this time on asynchronous processing in SQL Server.

To see the rest of the agenda go here http://www.developerday.co.uk/ddd/agendaddd4.asp

Do you want the permission view that is avialble in Enterprise Manager for roles well you need to download Jaspers custom report which can be obtained from Jaspers blog custom-ssms-reports-in-sp2-database-permissions.aspx  You might also be interested in Enterprise manager task pad view and you can find a report that is similar to the task pad pane in EM here

This is based on the custom reports that are now available in SP2 of SQL 2005

Do you want the taskpad view in Management Studio then you need to download Jaspers custom report which can be obtained from Jaspers blog Enterprise manager task pad view and you can find a report that is similar to the permission pane in EM here custom-ssms-reports-in-sp2-database-permissions.aspx

This is based on the custom reports that are now available in SP2 of SQL 2005

On of the many things on discussion at pass was why Jasper aka SQLDBATips wasn't blogging.

Well it seems peer presure has won over Jasper is now blogging on sqlblogcast.com

So if you have ever used