Month: November 2007

So suppose you've created a default constraint without specifying the name (on a SQL Server 2005 machine). You do know the table and column, of course – you just don't have the name of the default constraint. Now you want to find the name so that you can drop the constraint.

A nice query against the system catalog views can help. Something like:

declare @table_name nvarchar(256)
declare @col_name nvarchar(256)
set @table_name = N'Department'
set @col_name = N'ModifiedDate'

select t.name, c.name, d.name, d.definition
from sys.tables t
    join
    sys.default_constraints d
        on d.parent_object_id = t.object_id
    join
    sys.columns c
        on c.object_id = t.object_id
        and c.column_id = d.parent_column_id
where t.name = @table_name
and c.name = @col_name

–should do the trick. If you want to then create some dynamic SQL to do the remove, that's quite easy too.

Alternatively, if you'd rather use PowerShell, you could do something like:

[reflection.assembly]::LoadwithPartialName("Microsoft.SQLServer.SMO")
$sql = New-Object 'Microsoft.sqlserver.management.smo.server' 'localhost'
($sql.Databases["AdventureWorks"].Tables | where-object -filter {$_.Name -eq "Department"}).Columns["ModifiedDate"].DefaultConstraint.Drop()

This is using SMO to jump to the appropriate database, find the table called Department, grab the appropriate column out of it and drop the default constraint on it. I'm not sure why I couldn't just use $sql.Databases["AdventureWorks"].Tables["Department"].Columns["… I guess there's something I'm not quite getting there. Possibly something about schemas, because I can use this method if the table is in the dbo schema (possibly read 'default' there).

This is something that has bothered for me for a while. In SQL Server Reporting Services, you write a query against a data source, and that becomes the data set for the report. Brilliant. I like to write the query in Management Studio first, and then paste it across.

But this doesn't always work, and multi-value parameters is why. Multi-value parameters are those parameters where you've ticked the checkbox to say that you can select multiple options. Like in the image there to the right.

Now, in T-SQL, it is not considered valid to write:

WHERE mycol IN (@ListOfMyCols)

It's just not the way IN works – in this scenario, it would considered the IN list has a single value, and use that. Don't bother trying a comma-separated string, it will just see if mycol is that same comma-separated string.

But if you're using SSRS with parameters that can take multiple values, then this is exactly what you do. You have to write your query as:

SELECT        ProductSubcategoryID, Name
FROM            Production.ProductSubcategory
WHERE        ProductCategoryID IN (@Category)

And the system understands that @Category is a multi-value parameter and passes it to the server in an appropriate way.

At this point, I want to quickly advertise SQL Profiler. Make it your friend, so that you can find out how things are working against your database. Particularly with Reporting Services – it can be very good.

SQL Profiler tells me that when I use a single-value parameter in Reporting Services, sp_executesql is being used, passing parameters to a fixed string. Terrific – this is what I want.

But when I use multi-value parameters, I don't get this behaviour. Instead, it turns out that SSRS passed across a query with the parameters pre-inserted – it used dynamic SQL!

Now, it works fine – but I don't like this. It could easily bloat the plan cache, and I just feel like it's the wrong way to do it. I don't know a better way – perhaps something could be done through enhancing the table-valued parameters feature of SQL Server 2008 – but I'm not going to hold my breath on that – currently it would have to have a table type declared, and then do something like "WHERE mycol IN (select col1 from @tvp)" – but that would have other problems associated with it.

I'm not suggesting that you don't use multi-value parameters – but just understand what's going on with the queries.

UK SQL MVP Simon Sabin (and Harpendenonian) posted something around this around filters, and has recently posted more about query parameters, but I thought I'd write some more, as it's been something I've been meaning to blog about for a while.

Jim McLeod is a DBA, and a good friend of mine. And today, he is due congratulations, because he passed the exam 70-444 – Optimizing and Maintaining a DB Admin Solution in SQL 2005. Now he only needs to do the Infrastructure Design exam (70-443) to become MCITP:DBA.

Did he take one of the courses I teach, that Microsoft recommends to do before trying the exam? No.

Did he spend hours in study, looking for those small things that he would need? No.

Did he download one of those illegal copies of the exam? No, of course not. That would be cheating, and Jim's not like that.

So how did he pass? Easy. He's a DBA by trade, and good at his job, too! So he just booked in, sat the exam, and passed it easily. The certification wasn't trying to trip him up with tricky questions, it was just trying to find out if he really does have DBA skills. Jim does, so he passed!

Congratulations again, mate.

Here's the scenario. Your SQL Server Reporting Services (SSRS) report has a matrix, showing the sales of each salesperson per year. You put conditional formatting in place so that cells in the matrix that have sales over 1000000 are in a different colour – which is done by putting a formula in the property 'color', like =iif(me.value > 1000000, "Red", "Black")

But then you put subtotals into your matrix (right-clicking on the column/row field to get the option), and you find that all the subtotals are being coloured Red, because together, they add up to more than 1000000.

And this becomes a problem, because you can't put different formatting on the subtotal as you have on the rest of the inner cells of the matrix. That is, without having a slightly trickier formula. The function you want is INSCOPE.

First look up the name of the group, by right-clicking on the field that contains the row and choosing "Edit Group". That might tell us that the group is called 'matrix1_SalesPersonID', or something similar. You can rename it if you need to. (And yes, I know… in real life you'd be using the SalesPerson's name – this is only an example)

Now, change your formula so that there's a different criteria to set the formula if it's within the group, rather than outside the group (as is the situation for the subtotals).

=iif(inscope("matrix1_SalesPersonID"), iif(me.value > 1000000,"Red","Black"), iif(me.value > 50000000, "Red", "Black"))

Now, within the matrix proper, the fields will be Red if they are over 1M, but in the subtotals at the bottom, they will be Red only if they're over 50M.

To do the same for the columns, just look up the group name, and consider doing something like:

=iif(inscope("matrix1_SalesPersonID") and inscope("matrix1_Year"), …

So now we have a matrix which has different conditional formatting for the subtotals, compared to the ordinary values within the matrix. You'll notice in the image here that the subtotal of $32M isn't red, even though it would've been without the InScope function.

SQL Server CTP5 is now available for public download. Yesterday I wrote about Visual Studio 2008 being live and ready for download to MSDN Subscribers, and today there will be more activity on the Microsoft servers again.

This latest CTP includes lots of great stuff, including a lot enhancements around SSIS, SSAS and SSRS, Intellisense for T-SQL, the Resource Governor, Transparent Data Encryption, SQL Filestream, and GeoSpatial support. And that's not even half of it. 18 improvements listed at https://connect.microsoft.com/SQLServer/content/content.aspx?ContentID=5470. Whew! Get yourself into this – you might find all kinds of benefits for your business.

Good news for developers… Visual Studio 2008 is now available for download on the MSDN Subscriptions site. There's a lot to read about it, but from what I can tell, the book that Nick and Dave are putting together should be one of the top ones. Let's home it comes out soon.

Geoff Orr spoke at the Adelaide SQL Server User Group yesterday, and according to the feedback (and attendance), it was a very popular session. He discussed various options around SSIS configuration, and threw in some jokes as usual.

Of course, he may need to update the talk soon, as it seems the next CTP of SQL 2008 will have many new SSIS features. Keep your eye on connect.microsoft.com/sql for when it comes out. I'll certainly be checking them out, and making sure I highlight them when I run the SSIS courses (and this) next.

Some people asked me about what Grandpa achieved. This from Bath University News:

The University is saddened to announce the death on Saturday of Professor Martyn Farley, a member of University Court since 1983, representing the Royal Aeronautical Society.

Professor Farley contributed immensely to the University and was elected by Court to serve on the University of Bath Council from 2000 to 2006. He also served on the Ethics Committee and the Buildings Committee.

Professor Farley worked for nearly 40 years with Bristol/Rolls Royce Aero Engines in various roles, including Divisional Director, Head of Design and Chief Engineer.

Professor Farley was designated Emeritus Professor of the Royal Military College of Science, Shrivenham and Cranfield University in 1986. He was Professor and Head of Department of Management Sciences at the Royal Military College of Science from 1975 to 1984 and Vice-Chairman, School of Management and Mathematics, RMCS Faculty of Cranfield Institute of Technology, from 1984 to 1986.

He also acted in a variety of roles for institutions including Brunel, Bristol, Loughborough and Luton universities, NASA and US universities. He was a Freeman of the City of London and was elected International Engineer of 1984 by the US Institute for Advancement of Engineering.

A while back I was talking to Bronwen Zande, of Brisbane-based SoulSolutions and GeekGirlBlogs.

Anyway, she and her partner John are big fans of Virtual Earth. As it's well publicised that SQL Server 2008 will have new spatial types, including fancy ways of integrating with Virtual Earth, I asked her if they were planning a tour of the Australian SQL Server User Groups to demonstrate how this could work. I figure this is something that few SQL Server people will do much investigation into, but may well be asked about by developers who are keen to find out what's possible.

Lo and behold, I guess things are happening, because they're on their way! In Adelaide, this meeting will be on January 10th, our usual time-slot on the second Thursday of the month, and there are other events happening around the country too. All this despite the fact that the latest publicly available version (July still) of SQL Server 2008 doesn't have the spatial types in it. Hopefully by then something will be available for us to check out the demos. Keep your eye on https://connect.microsoft.com/sql for news on that.