Two ways to find / drop a default constraint without knowing its name

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.definitionfrom 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_idwhere t.name … Continue reading Two ways to find / drop a default constraint without knowing its name

How multi-value query parameters in SSRS are treated by SQL Server

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 … Continue reading How multi-value query parameters in SSRS are treated by SQL Server

How Jim McLeod passed 70-444

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 … Continue reading How Jim McLeod passed 70-444

How to format the subtotals of a Reporting Services matrix differently, using InScope

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 … Continue reading How to format the subtotals of a Reporting Services matrix differently, using InScope

It’s a big week for downloads

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.

Visual Studio 2008 is live!

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.

Orr-some time with Geoff

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.

Bath University’s description of my grandfather

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 … Continue reading Bath University’s description of my grandfather

SQL and Virtual Earth

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. … Continue reading SQL and Virtual Earth