Fellow developers are often surprised at how much I use command line and xp_cmdshell. The reason for this is two fold:
- 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.
- 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
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”
Well it has taken a long time to finally move off of WordPress 2.0, but we have finally done it. Welcome to the new look Protean IT!
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.
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”
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.
Continue reading “A practical use of a numbers table.”
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).
Continue reading “Dynamic table loading in SSIS (Part 2)”
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.”.
Continue reading “Excel – a read-only database?”