Rob Farley

Ok, so real database developers don't use the graphical "Design Query in Editor"... yeah, I know. Sure, there's the odd time when you're typing a query and you don't have an Object Explorer (eg, in SSIS) and a moment of weakness sees you hit "Build Query" to save some typing, but in general I encourage people to write their queries in Management Studio SSMS and then copy them into the SSIS dialog. I was showing someone some of the frustrations I have with the graphical editor, and came across a real beauty - repeated predicates.

I logged a bug at the Connect site, where I wrote the rest of the details. You should vote for this - there may be a time when you click "Build Query" to avoid some typing, and you don't want to look like an idiot for repeating the same line multiple times.

https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=352874

I feel like I keep finding reasons to use SQL Server 2008 Management Studio with SQL 2005 instances, and here's another - the COPY_ONLY option now appears on the Backup dialog box (others include the DROP & CREATE scripting option, multi-server queries and the Select Top 1000 Rows menu entry).

It's not a new option (it just wasn't part of the dialog box), but there's an option when backing up a database called COPY_ONLY. It goes in the WITH section. It means that the backup you're taking won't be included in your ordinary backup/restore sequence. Let's paint a scenario.

Your system takes a full database backup on a Sunday morning. Monday, Wednesday and Friday you do differential backups. Everything's great, each of the differential backups only looks at changes since Sunday, when the last full backup was taken.

Except that one Thursday, one of the developers wants to refresh the test environment with a recent copy of the production system. Instead of finding Sunday's backup and using that, they make a new backup, and restore that onto test. Everything works, and that new backup gets deleted.

Which means that Friday's differential backup is based on the Thursday backup instead of Sunday. When a disaster occurs on Saturday, you're in a bit of strife.

If the developer had used the COPY_ONLY option, then Friday's differential would still've been based on Sunday's backup. That's the whole point of COPY_ONLY - it doesn't reset all the flags that indicate which data should be included in the differential.

Like I said earlier, it's not new - it's was introduced in SQL 2005. I don't know why it didn't appear in the SQL 2005 dialog box - it didn't even turn up in a service-pack. Since so many people learn about features through the user-interface, it really could've helped educate users about this useful option.

The hairy Irishman (his description - I just call him Sacha) is presenting to the Adelaide SQL Server User Group today, talking about improvements in SQL Server 2008 Integration Services. Right now he's talking about the No Match output of the Lookup Component, and I'm wondering why I had always just accepted that in SQL 2005, rows that didn't match a lookup would get pumped out to the error output.

For those of you who are saying "Sorry, what?", let me explain...

If you have a data flow in Integration Services which is missing a particular piece of information, and you need to get that information from another source, you are likely to do a Lookup transformation, which can perform a query to find the values to be inserted into each row in the flow. Of course if the data is just in two tables, you'd just do a join in your original source query, but as soon as you're talking about files, you don't have that luxury in quite the same way. Sure you could populate a table and then read it back, but a Lookup may end up being much quicker.

Now, in SQL Server 2005, if a row couldn't find the looked up value, it would be considered an error, along with truncation errors and errors in connection to the lookup source. I just accepted this as okay before, but the more I come across the No Match output (which can be used as well as the error output), the more I like it. So I can much more easily separate the rows that have caused errors from the rows that just couldn't be found in the lookup source.

Regular readers of my blog will remember my post about how to get PowerShell for SQL into your regular PowerShell window. Except that if you've just installed SQL Server 2008 RC0, you may have noticed it doesn't work any more.

Never fear, they've just changed the name a bit. They've put 100 on the end of both. So just change your console file to list the two snapins as SqlServerProviderSnapin100 and SqlServerCmdletSnapin100. Calling PowerShell with the -PSConsoleFile option still works as before of course.

On a lighter note, they've fixed the case sensitivity. I can now "cd databases" just fine, without it needing me to use a capital D.

Now, of course you can do this by connecting your mobile to your PC and just finding the file - but sometimes you don't have your laptop with you, right?

Today I came across a utility which lets me do this - it's PocketPlus from Spb. Definitely worth checking out I think. Spb also have a very nice Mobile Shell, which seems to rival PointUI (which I also think is very good). PointUI wins out for me at the moment though, because I'm having to use my old Windows Mobile 2003 device. Also, it seems to have been written by Aussies, which can't be bad.

This is an oldie, but a goodie. I was talking to Dave Gardiner about a question he had recently, the question of how to dynamically sort the results of a T-SQL query.

He was thinking of something like this:

--Not so useful - only works in a stored procedure
IF (@order = 'Column1')
    SELECT * from Table1 ORDER BY Column1
ELSE IF (@order = 'Column2')
    SELECT * from Table1 ORDER BY Column2

And one of the suggestions in his comments was to use the CASE statement:

--Not bad, but only works if the types are compatible
SELECT *
FROM Table1
ORDER BY
   CASE WHEN @order = 'Column1' THEN Column1 ELSE Column2 END

This isn't bad, but if Column1 and Column2 are different types (well, incompatible types), you get an error when the ELSE clause is used.

A better solution is to remember that CASE gives NULL if there nothing is satisfied:

SELECT *
FROM Table1
ORDER BY
   CASE WHEN @order = 'Column1' THEN Column1 END,
   CASE WHEN @order = 'Column2' THEN Column2 END

This way, it's either ordered by "Column1, NULL" or by "NULL, Column2". Both times, it's the order we want, and we're doing it in a single query.

SQL Server gives you Fuzzy Lookups and Fuzzy Grouping, but only in SQL Server Integration Services. It's not even on the list of SSIS enhancements for SQL Server 2008.

This week at the MVP Summit, I was having a discussion about this with Jamie Thomson, and we had a chat with one of the T-SQL guys to suggest it. The response came back with "Log in on connect", which I did just now. https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=338664

Please check it out and provide comments, votes, validations. All this will help persuade Microsoft to implement this useful feature. And tell your friends too! The more it receives, the more likely it will be implemented.

I'm at the MVP Summit this week - an event at which Microsoft communicates (talking and listening) with MVPs. I'm having a great time. I'm seeing people I haven't seen in ages - Aussies I rarely see (like Nick Randolph), former Aussies (like Chuck Sterling), people I talk to regularly on IM (like Simon Sabin), and people I see almost never (like Dejan Sarka). I'm also meeting a lot of people I've never met before, like Allen White, Tony Rogerson and Desmond Tutu and the Dalai Lama.

Oh, alright, I didn't really meet the Dalai Lama (he walked past, but we didn't talk), but I did meet Archbishop Desmond Tutu. He was in the hotel lift this morning as I was on my way down to breakfast. He said he's an Arsenal fan and was very sad that we lost to Liverpool recently. I got a photo on my phone, and the quality isn't great - but I guess it's a nice memento.

From a technology perspective, the excitement is building around various new products. Summit is always good that way, but I can't write more for NDA reasons.

It's nice to give Microsoft feedback and get the product changed!

I first mentioned this in a post last month and logged a connect.microsoft.com entry. Microsoft checked it out, invesigtaed, and have now put a new property into SQL Server 2008 to resolve it! They write:

Thanks a lot for your feedback. We are taking a look at this issue.
Posted by Microsoft on 2/27/2008 at 8:54 AM

Greetings Rob,

First, let me thank you for your feedback on SQL Server; we really appreciate your willingness to spend time and write up the issue as you have, and to send it to us.

As you've described, the Defaults query that is run when you expand this property is just looking for defaults you've created via CREATE DEFAULT, not the more typical (imo) default constraints on table columns. It does by enumerating the SQL Server Management Object's (SMO) Database.Defaults collection, which in turn uses SQL as you've seen through the profiler. This however does not allow you to choose individual column’s DEFAULT constraints to be copied. In fact, the current implementation of the Transfer Objects Task doesn’t allow many kinds of column constraints to be copied.

For SQL Server 2008, we’ve just added a new property to the task, CopyAllDRIObjects, which corresponds to SMO’s “DriAll” property. This will cause all data referential integrity objects, including constraints, on objects you’ve selected, to be included when copying. So for example if you’ve selected three tables to be copied and they each have columns with DEFAULT (or other types) of constraints, and you enable CopyAllDRIObjects, this will cause their constraints to be copied as well to the destination. DRI objects for objects not selected, i.e. constraints on columns in tables you have not selected, will not be copied.

Thanks again for your post to connect; we look forward to hearing from you in the future. Take care!

SSIS Team

Posted by Microsoft on 3/6/2008 at 5:47 PM

I think the key words here are "on objects you've selected" - so that you're not trying to transfer things you don't want.

The fact that they took any kind of action is great, and definitely shows that it's worth jumping onto the connect.microsoft.com/sql site and putting in feedback about SQL Server. If you identify something that should be changed (and it's easy enough!), then you could have a very quick fix!

A little while back someone asked me how to make a PivotTable in Excel, showing the Min, Max and Avg of a field.

So I recorded a movie. Now, thanks to the wonder of Silverlight, it should be here for your viewing pleasure. No sound, so you can keep listening to whatever's in your ear already.

The morning after the Adelaide launch of the 2008 products, I got to enjoy breakfast with quite a handful other Adelaide community leaders, and few of the Microsoft DPE team. Conversation was remarkably varied, and one of the things that came up was Community Credit and the Second Life .Net User Group.  I've never done the Second Life thing, and I'm not sure I want to. It's an interesting concept though - the idea of attending a user group within a game. I guess it's a cross between an online community and a face-to-face one. Perhaps it's onlineface-to-onlineface? It's not my thing, but it might suit people who can't do face-to-face for one reason or another. Is there a Second Life SQL User Group?

This is really quite cool, especially since it's a tool feature, and one that will help SQL Server 2005 installs.

Suppose you have many servers, all registered in Management Studio, in the Registered Servers window. Now you can right-click on the Local Server Group, and pick the "New Query" option. Write a query, and it gives you a result set including the results from all the servers, as if you'd done a UNION ALL using linked servers.

So now if you want to do something like

select name, database_id from master.sys.databases;

on a query against multiple servers, you get three columns. Server Name, plus the columns from the query.

Sick of how slow backups can run, even though you're using compression from some tool like Hyperbac?

Greg Linwood's had a good idea to help. A large amount of the data in any database system is the data stored in indexes (copies of the data in tables, just ordered differently) to help your queries run faster, so if you can tell the system to backup the database without that data, then it should work a whole lot faster.

Of course, when you restore a smaller backup, it should a lot quicker - but you won't have the indexes in place yet. But you still have the option of letting them rebuild, so you ought to be able to get your system back up quicker (although potentially doing table-scans for a while).

If you like this idea, let Microsoft know, at https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=331220

Installing SQL Server 2008 gives you SQLPS.exe, an application you can run which is a PowerShell including the PSDrives for SQL Server (and compatible with SQL Server 2005 as well!).

But how do you make this work with your current copy of PowerShell? Well, I just worked this out. Darren Gosbell was lamenting with me about the fact that it's not a SnapIn for PowerShell, but it turns out it is. We ran:

Get-PSSnapin -reg

...and got the following results:

Name        : SqlServerCmdletSnapin
PSVersion   : 1.0
Description : This is a PowerShell snap-in that includes various SQL Server cmdlets.

Name        : SqlServerProviderSnapin
PSVersion   : 1.0
Description : SQL Server Provider

So then I ran:

Add-PSSnapin SqlServerProviderSnapin
Add-PSSnapin SqlServerCmdletSnapin

And now I have the full SQLPS functionality in my usual PowerShell window. Lovely. And by putting this into a PowerShell console file, I can have it load up automagically for me. SqlServerProviderSnapin gives me the SQL: and SQLPolicy: drives, whereas SqlServerCmdletSnapin gives me Invoke-Sqlcmd for running queries.

<?xml version="1.0" encoding="utf-8"?>
<PSConsoleFile ConsoleSchemaVersion="1.0">
  <PSVersion>1.0</PSVersion>
  <PSSnapIns>
    <PSSnapIn Name="SqlServerProviderSnapin" />
    <PSSnapIn Name="SqlServerCmdletSnapin" />
  </PSSnapIns>
</PSConsoleFile>

I'm still not convinced about the merits of using PowerShell to manage SQL Server, but as more Windows Admins adopt PowerShell as their preferred scripting language, I think it's worth having it as an option.

So I was trying to get the ScriptingOptions happening, and this is the only way I've found so far. It's ugly, and if you have a better way of doing this, please post a comment for me.

[reflection.assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo")
$so = New-Object Microsoft.SqlServer.Management.Smo.ScriptingOptions
$so.DriAllConstraints = $true
(get-item Sales.Store).Script($so)

And this gives me the Sales.Store table with all the default constraints thrown in. Much better. Ugly way of doing it, but at least it works.

So I'm at the Heroes launch in Melbourne today. I'm going to hang out at the Experts booth, so if you're here and have a question about SQL Server 2008, why not come over?

How often do you wonder what something's actually doing under the hood (in the way that it talks to SQL Server)? It's a trick I've written about before - when working out how SSRS did multivalue parameters. This time, it's Integration Services' turn.

SSIS has this Transfer SQL Server Object task, designed to be able to transfer objects like tables from one database to another. But Ali got in touch saying that it wasn't transferring the default constraints for his table - that thing which means that the current date, 0 or 'dbo' gets populated in the field when you don't give it another value to use.

I've never had much cause to try out this task, so I started playing. Unfortunately, I'm still not that keen. I'm looking at the SQL 2005 version, not the 2008. Hopefully it's better - if I have the time I'll try it and post about it later.

First I pulled up the Edit window, to see what I could do. I went straight to the DefaultsList property, to see what I could move across explicitly. Nothing in there for the AdventureWorks database. Odd - sys.objects tells me there are default constraints. Perhaps you need to have the parent object selected for transfer first - still no luck. SQL Profiler to the rescue, it turns out that the query that SSIS is using to list the Defaults is:

SELECT
obj.name AS [Name],
SCHEMA_NAME(obj.schema_id) AS [Schema]
FROM
sys.objects AS obj
WHERE
(obj.type=N'D' and not(obj.parent_object_id > 0))
ORDER BY
[Schema] ASC,[Name] ASC

It's looking in the right place for the defaults, but the thing that's upsetting is that it's not listing the defaults that are created as part of a column definition. To make a default which is listed here, you must create the default first and then bind it to a table's column afterwards - not the way that most people do their default constraints.

I could reproduce Ali's problem easily enough - when I created a table with a default, and then transferred it from one database to another, the default constraint was missing. I think it must call the SMO Script() method without the ScriptingOptions set - when I did this (using PowerShell) I found that without parameters, the script didn't include default constraints.

What I found even more frustrating is that when I tried to transfer an object that wasn't in the default schema (I tried the Sales.Store table), SSIS simply couldn't find it, and gave me an error. Profiler showed that sure enough, it was only for objects in the default schema, despite the fact that I had selected the object from SSIS' list in the first place! Go figure. I do like SSIS, but I think this is one task that may be worth avoiding. Hopefully they'll fix it up later.

You can easily roll your own of course... by querying the system views yourself, like I did for indexes a while back. Use FOR XML PATH('') to do your string concatenation.

The sixth CTP of SQL Server 2008, made available this past week at http://connect.microsoft.com/sql provides a PowerShell provider for SQL Server, which is backwards compatible with SQL Server 2005 as well. I showed it a little at the User Group in Melbourne a few days ago, and some people seemed to like it.

Basically, you can now open up PowerShell, and change directory to the PowerShell drive "SQL:". Then change directories through the instances, databases, tables, and so on. At any point, try something like "dir | gm" (gm is Get-Member, dir is an alias for Get-ChildItem), to find out what properties and methods are available on the objects in that folder.

Whilst T-SQL will probably remain the preferred environment for many, this scripting seems to be a step up from using SMO through PowerShell. However, it seems to be just a wrapper for SMO - when I tried to find out information about the Script() method on tables, it turns out to take a parameter of type Microsoft.SqlServer.Management.Smo.ScriptingOptions - I found this a little disappointing, and I haven't figured out the best way of getting help on methods that are on these objects either.

It's definitely a good start though - should be a very nice feature of SQL Server 2008.

Collation is a wonderful thing. It's what makes sure that Greek people can see their data in a Greek order, and Germans can see theirs in a German order. It helps us determine whether data should be case sensitive, or whether those little dots and circles that Swedes use really matter.

But if you have a database that has different collation settings to the SQL instance it's on, it can be a pain.

Suppose you have an instance of SQL Server, and you decide to restore a database on it. It may be an upgrade, it may be part of a consolidation exercise. Regardless of the reason, it turns out that the instance's default collation is not the same as the instance that the database was on previously. This will affect the tempdb database.

So your application is ticking along nicely on the new server, until such time as the application needs to create a temporary table. This gets created in tempdb of course. Like most T-SQL code, the application doesn't specify the collation for this temporary table, and the columns pick up tempdb's default collation. Ah. Now as soon as you try to perform a join between this temporary table and a table in your application's database, you get a collation error:

Msg 468, Level 16, State 9, Line 1
Cannot resolve the collation conflict between ... in the equal to operation.

And you find yourself needing to change code, or change the SQL installation, or something to be able to resolve this.

But you just want the error to go away. You want it to just pick a collation, maybe the one from the column on the left of the comparison, maybe the one from the right. So long as the error goes away, you may not be all that interested. Chances are it's just the difference between a Windows collation and a SQL collation, and the differences aren't significant enough for you to care (I know, there are plenty of cases where you really do care - in which case you probably don't have so much frustration with this as the rest of us).

Right now, there is no way of making that error go away. But I'm suggesting to Microsoft that there be a database-level option (or an instance-level option, or both - I don't mind how it gets implemented), that will detect a collation error in a query and handle it in an appropriate way. After all, a collation error is detected before the query is executed, so changing the way it's handled should be easy enough.

If you think this is worth voting for, then we might be able to see it implemented, and collation errors may become a thing of the past. They shouldn't be ignored completely - I just don't want to be the victim of coding that doesn't specify the collation.

Vote at: http://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=324910

SQL Server 2008 has spatial types which are very useful for all kinds of applications. I thought it would be nice to produce one of those charts like you get in maps, telling you the distance between cities.

So I start by populating the data. This uses the geography data type, which understands about the spherical nature of the world. The data is populated using POINTS, using data which I found around the internet.

create table dbo.Cities
(id int identity(1,1) primary key,
name char(3) not null,
fullname varchar(100) not null,
location geography);

insert into dbo.cities values
('ADL','Adelaide', 'POINT(-34.92 138.58)'),
('MEL','Melbourne', 'POINT(-37.78 144.97)'),
('SYD','Sydney', 'POINT(-34 151)'),
('PER','Perth', 'POINT(-31.95 115.87)'),
('BRI','Brisbane', 'POINT(-27.48 153.13)'),
('CAN','Canberra', 'POINT(-35.3 149.12)'),
('HOB','Hobart', 'POINT(-42.87 147.32)')
;

Now that this is populated, a simple query will produce the results that can be put into a report. STDistance is a method which applies to one geography value, accepting another as a parameter.

select c1.name city1, c2.name city2, c1.location.STDistance(c2.location) / 1000 distance
from dbo.Cities c1
    join dbo.Cities c2
    on c2.id <= c1.id
;

Now, I can make a simple matrix in SSRS, using =IIF(Fields!city1.Value = Fields!city2.Value, Fields!city1.Value, Sum(Fields!distance.Value)) as the value in the centre of the matrix, with city1 and city2 on the rows and columns. This formula means that through the main diagonal of the matrix, the city names will be displayed. Hiding the header rows and columns, we quite quickly achieve something that looks like the image here. Of course, if you're doing this for a proper application instead of just a proof-of-concept, you should take the time to format it more nicely. Colours, borders, that type of thing. But you get the idea.

I'm not sure how useful this is to your own applications - but it's so easy to query this kind of spatial data, I encourage you to start considering it to be a useful part of your toolset.

This week, a tour of SQL Server User Groups in Australia starts, covering the spatial features in SQL Server 2008. The list of the meetings are at http://www.sqlserver.org.au, and includes all seven groups.