Regenerating DBML Designer when your database changes

Here’s how to regenerate your DBML code in Visual Studio 2010 when one of your database objects changes:
1. Remove the table
2. Close the DBML designer and save
3. Re-open the DBML designer
4. Re-add the object by dragging from Server Explorer
5. Close the DBML designer and save

In finding this solution, I did find some wailing and gnashing of teeth on-line, with people expecting the DBML code to have some kind of refresh feature for automatically doing this when the database changed.

I must admit I don’t share their chagrin. I am:
* Pathetically grateful for anything that saves me not having to write a hundred lines of property let/get statements
* Impressed by the way projects that reference these objects automatically re-test for compilation as soon as you save the DBML code

Fix SQL Server Express memory growth

Here’s a simple batch script to restart SQL Server Express – I find memory used grows to at least 300 MB over time, although I do tend to leave Management Studio open for a lot of that period.

REM Simple batch script to restart SQL Express
REM BRD: 20110928
REM http://www.brdalby.co.uk/
net stop MSSQL$SQLEXPRESS
net start MSSQL$SQLEXPRESS
pause

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.