B R Dalby Consulting

BCP – Collation woes

Spent a long time trying to get data from one instance of SQL Server to another with BCP. Couple of things I learnt along the way:

1. SQL_Latin1 _General_CP1_CI_AS v. Latin1 _General_CP1_CI_AS

May as well be Latin v. Russian as far as BCP is concerned. Was unable to import data from a Latin1 _General_CP1_CI_AS column into a SQL_Latin1 _General_CP1_CI_AS column. Banged my head against the wall for a good while on this, until eventually just manually set the collation on each column in the target database to match the source.

2. Identify columns

Because my identity values had a few gaps in in one table, the master-detail relationships became jumbled up, and the data was no longer consistent. Worked around this by inserting some dummy rows in the BCP files so that there was a row for every identity column.

Since I had deleted all of the rows in the target table after a failed attempt at importing, also needed to reset the seed. Which is straightforward:

DBCC CHECKIDENT (TableName, reseed, 0)
This means that when you use BCP to import, the first value will be 1, and so on.

Embedding Wordpress

Embedded Wordpress in a site for someone today, with different categories feeding different pages. Pretty simple to do – the instructions here are more or less correct, although the example code doesn’t quite work with the latest version of WordPress:
http://www.shooflydesign.org/buzz/past/embedding_wordpress.html

LARGE() function and rows with same rank

A finesse you can apply with the LARGE() function is to consider the case where you have rows with the same value, e.g:
A B 1 James 3 Fred 2 Ian 1 Graham 3 Nigel

The LARGE(A1:A5,1) function will simply tell you that 3 is the largest value. But you may actually want the top three rows, e.g. to return the set (Fred, Nigel, Ian)

The way to achieve this is to add some unique key value to each ranking value so that they become distinct. In the example above, we could create a new column with value as follows:
=A1+ROW()/1000000

This will then give us:
A B C 1 James 1.000001 3 Fred 3.000002 2 Ian 2.000003 1 Graham 1.000004 3 Nigel 3.000005

If we run the LARGE() function against Column C, we now have our unique values, which we can then feed into a VLOOKUP to return the person’s name.

ADODB Connection Object

Was banging my head against the wall a bit tonight trying to close my ADODB.Connection object down cleanly. My mistake was writing something like the following:

If Not objConnection Is Nothing Then objConnection.Close

It seemed that objConnection was both not nothing, and throwing an error about already being closed.

Solution was to check the State property first, so now I have:

If objConnection.State <> adoStateClosed Then objConnection.Close

New Excel Tricks

1. LARGE()

Used this to find the top 5 of 200 values. Never know you could do this before!

2. SUM(Sheet1:Sheet10!A1)

I vaguely knew you could sum across worksheets before, but cool to rediscover this.

Ubuntu Server on VirtualBox

Just got an Ubuntu LAMP server running on VirtualBox. Bought the disk image from TradeBit, which turned out to be a good move, as the chap selling the image was incredibly helpful. Talked me through various tweaks to get me up and running which I would basically have never found (No PAE support? Resetting DHCP client?)

But now, I have a little Ubuntu server running on my Windows machine! I managed to set up port forwarding under my own stream, and also installed a Gnome GUI layer, Firefox and Webmin. I can browse to it from my Windows host, and am now all set to start a Wordpress site development exercise without needing to be online.

I think this is the first time I’ve had an ‘aha’ moment with Linux. I’ve had a go through the years, but never managed to get the OS running, for one reason or another. Particularly enjoyed the GUI package manager that gives me a whole universe of softare to choose from and install in an apparently reliable way.

Here’s the VDI image, in case you are interested:
http://www.tradebit.com/filedetail.php/5027855-virtualbox–ubuntu-8-10-server-lamp

Modelling inheritance in a database

Am currently figuring out how to model different payment types for a client in a database to store their sales data. There are a lot of common attributes, and we may well need to sum transactions across type.

Found the following two pages useful:
How To: Model Inheritance in Databases
Object Relational Mapping Strategies

DevExpress DXGrid for WPF and Visual Studio Express

Turns out that the DevExpress installer does not integrate DXGrid with Express editions of Visual Studio. Solution is:

1.Manually add references for following assemblies:
DevExpress.Data.v9.2.dll DevExpress.Wpf.Core.v9.2.dll DevExpress.Wpf.Editors.v9.2.dll DevExpress.Wpf.Grid.v9.2.dll

2. Manually add namespace to XAML:
xmlns:dxg="http://schemas.devexpress.com/winfx/2008/xaml/grid"

You can then add your element as usual.

DevExpress Support also say that you can manually add the following assemblies to the Toolbox to achieve designer support, but that didn’t work for me:
C:\Program Files\DevExpress 2009.2\Components\Sources\DevExpress.DLL\DevExpress.Wpf.Editors.v9.2.dll C:\Program Files\DevExpress 2009.2\Components\Sources\DevExpress.DLL\DevExpress.Wpf.Grid.v9.2.dll

Tip: Shared Assembly Attribute File

When you on a solution with several assmblies in .NET, you’ll find yourself maintaining multiple copies of AssemlyInfo.cs, each of which contain a lot of common attributes such as:
AssemblyCompany AssemblyProduct AssemblyCopyright AssemblyTrademark AssemblyCulture

You may also wish to make AssemblyVersion uniform accross your build.

A useful technique in this situation is the following:
1. Copy the common attributes out into a new file, called something like ‘GlobalAssemblyInfo.cs’
2. Delete these common attributes from the original AssemblyInfo.cs files
3. Save your GlobalAssemblyInfo.cs file somewhere useful, e.g. in the same folder as your solution file
4. In each project, add GlobalAssmblyInfo.cs through the IDE as a linked file, rather than a straight addition.

Calling HyperTerminal from the command line

It turns out there is a subtlety in the syntax for HyperTerminal 5.1:

>hypertrm /t 127.0.0.1:21

Will open a HyperTerminal session on address 127.0.0.1, port 21

>hypertrm /t127.0.0.1:21

Will try and open 127.0.0.1.ht and complain when it is not present.

	ReportSurvey.xlt Perform a cost benefit analysis on your reports
	AddinFramework.xla Useful code for new addins
	DesignGuide.pdf Ideas for creating better spreadsheets
	AptitudeAndAppetite.pdf Model for assigning and managing tasks

B R DALBY CONSULTING

Information Systems Consulting for Growing Businesses