SQL Server 2005 AdventureWorks install

Following the Visual Studio / SQL Server 2005 / Biztalk launch event, the promised DVDs containing Visual Studio and SQL Server 2005 arrived this morning! This looks like a very clever piece of marketing by Microsoft to send these out just before Christmas, when people are likely to have a few days to install and try these out.

When I installed them, as usual selecting the "install everything" option and then tried to go into SQL Server Management Studio, the AdventureWorks sample database was not there.

I couldn't see this on the DVD, but found this for download at this location on the Microsoft site where it can be downloaded from and installed. You can then attach it using Management Studio.

Blogger Error 550 - Permission Denied

I got the error message "Error 550 - Permission Denied" when I tried to update and republish this blog last to fix some problems with archived files. The error occurred with the atom.xml file, which is the last thing that is published if you republish your entire blog.

I found this was because the "Site Feed" settings had a directory path. This had somehow been set to a / character, where it should have been set to nothing at all to publish to the root directory of my site. Changing it to nothing at all fixed the problem.

I also found a post in Tim's Journal here that explained some other causes of this error.

Is it memorable?

Kathy Sierra writes about the importance of increasing the emotional content of what you do to make it memorable and reduce the amount of time needed to learn things.

We hear a lot about techniques to make software development take less time and produce results that are easier to maintain. Much has been written about patterns and practices of development. However, if you have some code that will need to be maintained, you should try and make it mean something to the person who is going to have to maintain it. Put in comments relating your code to real world events that arouse feelings in people and don’t be afraid to be different or give functions surprising or funny names. Try and make the person who is going to maintain your code have fun, especially if the system you are working on isn't "fun" of itself – they will do a better job if they are enjoying themselves than if you write everything in a dull academically correct way.

Technorati Tags: Patterns

The new Methodology

Martin Fowler has updated his paper “The New Methodology” about Agile Methods. I think key to this is his comment that programmers are responsible professionals and not “plug compatible programming units” and are the people best able to decide how to do their technical work.

He concludes by saying a decision to adopt an agile method should be down to the people, and if they aren’t interested in working in this way, the results aren’t likely to be great.

For an agile method to be successful you need to make people happy, and recognise that people are different and have different approaches to their work, but still need to communicate freely without feeling intimidated. The method you adopt should depend on the people on your project more than the technical nature of the project.

Technorati Tags: Agile

Passed Microsoft Exam 70-315

I've just passed the "Developing and Implementing Web Applications with Microsoft Visual C# .NET and Microsoft Visual Studio .NET" exam (70-315) with a score of 962. This means I've now only got 3 exams left to get MCSD status.

I've used about 60% of the syllabus in my work, and I found my experience helped me quickly rule out some possible answers that did not look "right". To learn the rest, I read the book by Amit Kalani. This comprehensively covered all the main areas in the exam, and provided a good range of practice questions and tips for things to look out for.

I'm not sure quite how much of the exam content I'll now use that I didn't use before - maybe I'll use DataGrids more. Also, in many cases I found myself learning things that I could have easily looked up on Google in 30 seconds. However, I think the main benefit of the exam to me is that it will help me communicate with other developers if everyone uses the same sort of language when they talk about the technology.

Technorati Tags: Microsoft, 70-315, Web

Test Driven Development with SQL Server Databases Example

In a previous post I suggested a method of using Test Driven Development with SQL Server stored procedures. Someone suggested some examples would be helpful.

Example Scenario

You have a database with a customers table. This table (TDDCUSTOMERS) contains the fields CUSTOMERID and NAME.

You write a stored procedure to add a customer. You are worried that someone might want to add more fields to the TDDCUSTOMERS table, and this will break the stored procedure.

Setting up

Create the table as follows:

use northwind
go
create table dbo.TDDCUSTOMERS 
(
 CUSTOMERID int identity(1,1),
 CUSTOMERNAME varchar(50)
)
go

Write your test

Then think about how you are going to test your Stored Procedure to add a customer - a simple test is to check if there is one more row in your database if a customer has been added and write your test:

use northwind
go
create procedure dbo.TDDTESTADDCUSTOMER
 (@c integer output)
as
begin transaction
declare @c1 as integer
declare @c2 as integer

select @c1=count(*) from dbo.tddcustomers 
exec dbo.TDDADDCUSTOMER 'Richard'
select @c2=count(*)  from dbo.tddcustomers

if ((@c1 + 1) = @c2)
begin

 rollback transaction
 set @c = 1
end
else
begin

 rollback transaction
 set @c = 0
end

Make your test fail

Then write a stored procedure to do nothing:

use northwind
go
create procedure dbo.TDDADDCUSTOMER(@name 
varchar(50))
as
print ''

Run your test

declare @c as integer
exec TDDTESTADDCUSTOMER @c output
print @c

This should output 0 as the SP has failed.

Make your test work

You will then have to make your test work:

alter procedure dbo.TDDADDCUSTOMER(@name 
varchar(50))
as
insert into dbo.tddcustomers(customername) 
values(@name)

Now run the test again

declare @c as integer
exec TDDTESTADDCUSTOMER @c output
print @c

Note this returns 1. If you type select * from TDDCUSTOMERS this will not return any records, as the transaction was rolled back, so your database will not be corrupted.

Make a change to the database schema

Now try changing your schema to add an address field (with a not null constraint) to the TDDCUSTOMERS table

drop table tddcustomers
go
create table dbo.TDDCUSTOMERS 
(
 CUSTOMERID int identity(1,1),
 CUSTOMERNAME varchar(50),
 ADDRESS varchar(50) not null
)
go

Run the test again

declare @c as integer
exec TDDTESTADDCUSTOMER @c output
print @c

This will return 0, as a customer cannot be added using the stored procedure (as the address field cannot be NULL and there is no default). Your test is easy to run, and can be run often, so you will detect that adding an ADDRESS field will break the stored procedure. You then know to rewrite the stored procedure to put in a default address, so your application continues to work.

Technorati Tags: SQL, TDD, Database

SQL Server 2000 - Inserting dates in UK format

I've been moving a database to a new server. The ASP code that inserted dates passed a string in UK format (day-month-year) to the server.

This worked on the old server, but the new server was inserting and selecting on dates in month-day-year format, so the day and the month were interchanged.

It was difficult to find out how to change this. I found some pages suggesting using SET DATEFORMAT DMY, but this only worked for the current connection to the database. Eventually, I discovered that the date format was obtained from the language settings for the user the ASP code connected as.

I found this user using Enterprise Management / Security / Logins, and converted the settings from "English" to "British English".



Right clicking on properties gives:



I then reset the database and this worked without changing any of the ASP pages.

The following post describes how I swapped the dates that were incorrectly inserted.

Obviously it will be better in the long run to change the ASP code to use a language neutral format such as YYYYMMDD, to avoid this happening in future.

Technorati Tags: ASP, .net, SQL, Database, Language

Swap day and month in SQL Server

I had an SQL Server database with days and months inserted the wrong way round, due to the code that was inserting them using European format (where the day is written before the month) and the database assuming it was in US format (month before the day).

If anyone's interested, here's a user defined function to swap the day and month around, leaving the year and time unchanged.

CREATE FUNCTION dbo.MonthDaySwap (@dt datetime)  
RETURNS datetime AS  
BEGIN 
return  convert(datetime,
 convert(varchar(50),day(@dt))+'/'+
 convert(varchar(50),month(@dt))+'/'+
 convert(varchar(50),year(@dt))+' '+
 CONVERT(varchar(12), @dt, 114)
)
END

You can use this in an update query as follows:

update dbo.Orders
set orderdate=dbo.MonthDaySwap(orderdate)
where orderid=5