Author: Josh

Categories
SQL Server

Bugs/Omissions in Visual Studio Team Edition for Database Professionals

 

I’m using Visual Studio Team Edition for Database Professionals and have spotted a couple of niggles with it.

1) If you rename a foreign key field, the schema compare fails to recognize that it needs to update the foreign key constraint. Instead it creates a new one and leaves the old one in place and unchanged, causing an error.

2) Extended properties are behaving strangely. It seems to be dumping them all into a file called “ScriptsIgnoredOnImport.sql”, appending it on to any entry it left in that file last time it ran. As far as I can tell you need to manually copy the extended property creation lines out of this file and into the individual table SQL scripts in the project. Irritating.

 

Categories
SQL Server

Calculated Measures DisplayFolder

In Business Intelligence Studio (AKA Visual Studio 2005), when editing a cube, there is a tab for creating calculations. This tab contains a handy ‘script organizer’ pane showing a list of the names of all your calculations, names sets, etc. Unfortunately if you select an item within the script organizer, the standard properties window does not show "Display Folder" and "Related Measure Group" properties. To get to these properties, there is a "calculation properties" icon near the top of the calculations tab.

 

Categories
SQL Server

SCOPE_IDENTITY()

A technical post for a change. We recently noticed a difference in SQL Server 2005 over 2000 around the use of the SCOPE_IDENTITY function. I’ve been used to "SELECT SCOPE_IDENTITY()" returning INT as its data type, as that was the largest data type that was allowed as an IDENTITY column. However, it now seems to be returning DECIMAL.

A quick investigation shows that DECIMAL can indeed be used as an IDENTITY column. There is a restriction that the scale must be zero, i.e. DECIMAL(n,0), so that the field only contains whole numbers.

Exciting? Maybe not. Interesting? Evidently enough for me to blog it.

Technorati tags: ,

Categories
Uncategorized

Caution! Hot Taps!

The office I’m working in has a notice above each sink:

"Caution! Beware of hot taps. The hot taps have been pasteurised and all water outlets will be very hot."

Surely it is the water that has been pasteurised, not the taps?

Categories
Personal

Polish Bathroom Fitter

We recently had our bathroom stripped bare and refitted. Pink wallpaper became white tiles, the acrylic bath with a hole became a whole enamel bath. That sort of thing.

The company we had in to do the fitting presented us with two faces: Ray, the South African boss (or mere line manager, but I suspect they’re not that huge an operation) and Adam (or ‘Adamski’ as Ray said at one point), a Polish all-rounder in the game of bathroom fitting.

Things were progressing really well, and a nine day job was scheduled to be finished in six and a half. Adam had been very hard working, an amiable and cheery chap, but a little difficult to communicate with. His English was languishing somewhere south of Peter and Jane books. Ray provided telephone and in-person support for the more difficult exchanges. 

On the morning of completion and pursuant to the advice of my father, I started the process of snagging in preparation for winding up the operation. One thing I wouldn’t have thought to do without the paternal advice was to check that the bath overflow worked. I filled the bath and kept the taps running. The overflow was working brilliantly; the water was hardly rising past it. With the test concluded, I headed down to the kitchen for breakfast.

As you will have predicted, that’s when I saw the drips from the ceiling. These were followed shortly afterwards by streams of water coming out from the window frames and pooling inside and outside the house.

There was a knock at the door: Adamski had arrived. I rushed to let him in and raced to think of how I could communicate the issue at hand. I hit on one of the few words he knew, "Problem!", and led him through to the kitchen. On seeing the water works he used one of the other words he knew, "Shit!", and raced upstairs to sort it out.

It seems that the overflow pipe had been loose and had simply detached itself from the bath. The water dribbled out the overflow, down the underside of the bath and into the floor, but it wasn’t a huge amount. The real problem was when I emptied the bath. The overflow pipe had flopped to the ground and was lying lower than the normal waste pipe did. The entire bath full of water had taken the easiest route out of the system and flowed out of the detached overflow pipe and onto the kitchen ceiling.

We were lucky that the flow took it into the wall, and it appears (several days after the event) that we’ve suffered no damage from the incident.

So now we have a new bathroom and an embarassed Pole from whom, to my thinking, we might be able to call in a favour in the future.

Categories
Computers and Internet

Microsoft Office Groove 2007 folder sync not supported for Vista x64

"Groove" is an offering from Microsoft to enable teams to collaborate by sharing documents and other items between colleagues within a company and customers/partners outside of the company. It provides a ‘workspace’ in which files can be shared. In a simpler mode, one can choose a folder in Windows Explorer and select to share it through Groove.

When using a workspace, there is some integration with SharePoint in that documents can be checked in and out of an accompanying document library, but SharePoint is not a requirement for Groove. Documents are available offline, unlike just using SharePoint, and synchronized when on line.

Anyway, the point of this post was just to highlight to people that if, like me, you went to the trouble of installing the 64 bit version of Vista, the folder synchronisation tool does not work. This feature, arguably the feature that gives the product a purpose in life, does not work for 64 bit OS’s. I hope this gets rectified soon.

In the mean time I am back to using "FolderShare" for, well, sharing folders. It seems to just work, no matter whether it’s Vista x64 or 32 bit Win XP. Microsoft own this tool too!

Categories
Travel

British Airways’ Token Gesture

You may recall the issues Julie and I had with BA losing our bags on our trip to Oslo over New Year’s Eve. The bags turned up 13 days after we left them with BA, with one of the two traveling back via Oslo and Milan. I complained to BA and detailed the cost of clothes and toiletries we’d had to buy out there. For the two of us it came to about £200. Obviously it’s great to have new clothes, but they were surplus to requirements and we would not have spent that money if we’d had our bags with us.

BA wrote back this week and informed us although they could not offer us compenstation there would be a cheque for £36.22 in the post. I’m left somewhat bemused. It’s a pretty specific figure that has no relation to our actual costs, that I can see.

So, do I write back? A BA employee at the airport said we’d get money back for everything we had to buy, but we didn’t get that in writing and I didn’t get her name. I can’t decide if it’s worth the effort.

(Update 20/3)

I wrote back and now they’re asking me to resend the receipts. They "hope I’ll travel with BA again". That depends on what they say in their next response!

(Update 25/5)

Last weekend we received a cheque for the remaining amount. So the lesson learned is never give up! The effort involved amounted to three letters, which is not much for almost £200 of compensation. Now I’m happy.

Categories
Computers and Internet

First impressions: VSTS 4 DB Pros

 

My notes on my first impressions of Visual Studio Team System for Database Professionals.

 

I installed VSTS for Database Professionals using the DVD image from MSDN. This also involved installing MSDN Library for Visual Studio; it seems the integrated help will only work with that version of the library and not the Jan 2006 one I had installed for integrating with Visual Studio 2003.

 

The first action I undertook was to use the wizard to create a new project. It asks for an instance of SQL Server and then creates a DB on that instance with a name of the form "<name-of-project>_DB_<some-guid>". This database instance is used by Visual Studio to perform validation of the model, and so I guess should not be tampered with!

 

The project creation wizard doesn’t prompt for a database to sync with, though it is possible to import schemas or sync with an existing database after the project creation wizard has completed.

 

When I add a table in the project in Visual Studio, it generates a CREATE TABLE script and a couple of dummy columns. Not a great design experience. I was expecting something more graphical. When I try and rename the columns, using the Schema View tab, this must be done via a ‘refactor’. When I later created foreign keys to a column, this feature (as expected) updated those foreign key scripts too. It also optionally creates a refactoring XML log under a hidden "Refactoring logs" directory in the project root. I cannot see where this is used yet.

 

If I want to do anything more complex than changing a column name then it means editing the SQL script for the CREATE table. This includes adding a column, changing a column’s data type, nullability, identity, etc. It doesn’t feel like you’ve got the full design capabilities of Management Studio in an offline mode. There also doesn’t seem to be any form of entity-relationship diagram capability, for those of us used to using Visio or Erwin to maintain our database schemas.

 

Choosing "Build" on solution explorer context menu for the project creates a script under a hidden "sql" folder in the project root. This script puts together all the individual scripts in the project to create a single deployment script. Individual scripts include tables, views, SPs, functions, post- and pre-build scripts, security scripts… the list goes on.

 

Choosing "Deploy" on the solution explorer context menu for the project results in a build followed by the deployment. The deployment creates a database named <name-of-project>. The end result is two databases of this name in the server.

 

At this point it seems easier to start using Management Studio to edit the database, at least for a lot of coarse grained tasks like creating tables from scratch and references between tables.

 

In order to synchronise these changes back into the project it is necessary to create a new "Schema Comparison". This compares a project with a database, or compares one database against another.

 

The default comparison assumes your "target", i.e. the thing you want to update to be the same as the "source", is a database. In my case I had made changes to the database named <name-of-project> and I wanted to reflect those changes in the project. There’s a handy button in the middle of the schema comparison window that switches source and destination around, though.

 

My changes to be compared and synchronised were trivial; added tables, renamed columns, added foreign keys. These were flagged as a list of update actions split down to the table level. A button in the toolbar allows all the changes to be applied to the target. Individual update actions can be skipped if necessary. It would be nice if the schema comparison refreshed to reflect the state of play after the updates had been applied, but it’s possible that on an enterprise level schema this would be a bit slow.

 

A nice feature of the schema comparison is that when an update action row is selected, "source object" and "target object" scripts are shown side by side with changes highlighted in various colours.

 

My investigations continue, but I though some might find these first impressions useful.

Categories
Environment

UK to tackle bogus carbon schemes

Following on from my previous post about carbon offset schemes, I discovered via the BBC that the government has decided to provide a voluntary standard for schemes. Their initial recommendation is to go for one of the following four schemes that meet their evolving standard:

The last one, Carbon Offsets Ltd., is difficult to find information on. It is in partnership with the Airport Operators Association, probably as a result of the government’s investigations, which seems to have been initiated by the AOA in the first place.

In related news (from last week), Tony Blair eventually capitulated to demands to carbon-offset his personal flights too.

 

Categories
Travel

Oslo for New Year’s Eve

I spent the last few days of 2006 in Oslo with a group of friends from London. For New Year’s Eve itself we went to the restaurant on the 37th floor of the Radisson Plaza Hotel. Despite fog and low cloud earlier in the day the view from the hotel at night was clear and stretched on for miles. We had a very good quality 7 course meal (with extra helpings of some courses thanks to a friendly and very accomodating waiter) and champagne-toasted the new year in. In Oslo at midnight fireworks are let off in so many back gardens and city parks and squares that they blanket the sky. Many were set off within metres of the hotel, and at our elevation we were afforded an eye level view of rockets exploding.
 
Julie and I could have done without Heathrow’s baggage handling mess, though. We had to get by on the contents of Julie’s handbag and our credit cards for the entire trip! We’ve been back two days and still have no idea when we can get the bags back. I’m glad it eventually made the news. Baggage handling staff claimed that ‘little managers’ had tried to ‘cover up’ the mess from ‘big managers’.
 
In all the trip was well worth it. I got to meet up with an old friend, Morten, and share his home made waffles and strawberry jam at his house. We took in some of the sights and many of the beers and wines and saw in the new year in style.
 
[Edit] We eventually received the bags back, 13 days after the outbound flight. Now it’s time for the hassle of claiming compensation…