The Developer DBA toolkit

Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:

  1. It saves a lot of time because it means that you don’t always have to remote on to the server to view debugging information or execute local commands. e.g. Is XYZ service running? Who’s logged on to the box? Run that SSIS package.
  2. You can view results from a different security context which is often the key for successfully debugging connectivity issues.

I always keep a script just for the purpose which I consider to be an essential part of any database developer/DBA toolkit. This is a selection of tasks that crop up again and again and will often save you a lot of time. Especially if you use xp_cmdshell.

This post list a selection of tasks that come up again and again together with the necessary command lines to speed up the process!
Continue reading “The Developer DBA toolkit”

Isn’t it ironic

It’s quite ironic that being an ETL and database developer by trade, it has actually been easier to upgrade this website manually, literally copying and pasting each of the blog entries.

SSIS which is the integration tool I have on my current machine, has no easy way to insert data into a MySQL database. It can be done, but it means setting up a foreach container and mapping recordset parameters to an Execute SQL task. Far too much effort for a one off upgrade.

Google Buzz – Web 2.0 Narcosis

I love the idea of Web 2.0 – I jumped on the social networking bandwagon at a fairly early stage getting involved with AOL’s attempt to compete with myspace with its AIM pages. Its great that people with shared interests can join a network and interact with each other in a way that couldn’t be done before.

And I currently maintain profiles at LinkedIn, flickr, facebook & twitter, youtube, picasa, pprune.org and SSC. These profiles I can handle quite nicely along with my own hobby sites and various shopping websites that I use. I have always argued that privacy concerns can be managed Continue reading “Google Buzz – Web 2.0 Narcosis”

A practical use of a numbers table.

Many of you will have heard the mantra that loops are bad, set based is good. But how do you get around them?

The fact of the matter is that there are very few circumstances where a loop is the only way to achieve your objective. A look through the SQL forums will show you many a technique to turn iterative row by row processing into single transaction set based processing. I’m refering to hierarchical query techniques (see nested set theory) and of course the subject of this post, the numbers table.

Hauppauge Wireless Media MVP - Watch PC based media on your TV set!

Continue reading “A practical use of a numbers table.”

Dynamic table loading in SSIS (Part 2)

I talked in a previous post about the possibility of using the format command and bulk insert task inside of a foreach loop in order to load all your tables using a single package… I don’t much like the method as it means you have to run each table load in series, and you’re not taking advantage of SSIS (high speed dataflow task, parallelism and eliminating staging with a single pass transformation).

SQL Server Developer Edition 2005 Win32/X64/IA64 English CD/DVD

Continue reading “Dynamic table loading in SSIS (Part 2)”

Excel – a read-only database?

If you are trying to execute some Jet SQL DDL like CREATE TABLE or DROP TABLE against an Excel workbook and you get this message:

Executing the query “DROP TABLE [Sheet1];” failed with the following error: “Cannot modify the design of table ‘Sheet1’. It is in a read-only database.”.

How to Do Everything with Microsoft Office Access 2003 (How to Do Everything)

Continue reading “Excel – a read-only database?”