Powershell script to query SQL table

I recently had cause to create a PS (Powershell) script in order to query a SQL table. This in itself was easy enough, the only thing that was bothering me was that once I had read the data from the table I needed to read the data from the extract and loop this through my script to continue another process.

The issue I had was exporting the data in such a way that I could import it easily into a PS script. After much searching I found some really helpful sites, but none which appeared to do exactly what I wanted, all were close though and so I put a few together to create one that performed exactly as I wanted. So I’m sharing it in case anyone has this problem again.

Please note I’m only a very average script writer, with limited knowledge in this area, however I can perform the basics, and do most of what I need to. You may always find better script writers out there (would be crazy not to mention Scripting Guy amongst others) and better methods of performing what I needed – if you do please share! Otherwise please feel free to use as follows:

#SQL connection details
$SQLServer = "server" #use Server\Instance for named SQL instances!
$SQLDBName = "dbname"
$SqlQuery = "SELECT * FROM table"
#location of extract file
$extractFile = @"
c:\scripts\csv\list.csv
"@
#create SQL connection and extract dataset to file
$SqlConnection = New-Object System.Data.SqlClient.SqlConnection
$SqlConnection.ConnectionString = "Server = $SQLServer; Database = $SQLDBName; Integrated Security = True"
$SqlCmd = New-Object System.Data.SqlClient.SqlCommand
$SqlCmd.CommandText = $SqlQuery
$SqlCmd.Connection = $SqlConnection
$SqlAdapter = New-Object System.Data.SqlClient.SqlDataAdapter
$SqlAdapter.SelectCommand = $SqlCmd
$DataSet = New-Object System.Data.DataSet
$SqlAdapter.Fill($DataSet)
$SqlConnection.Close()
clear
$DataSet.Tables[0] | Export-Csv -Force -Delimiter "," $extractFile

Once I’d completed this the data was extracted in the columns I wanted from the SQL table (my select statement was actually fairly more complex to account for codes and required texts; 0s!) I could loop my data through a foreach command.

Just replace the SQL Server name, database, Select statement and file locations with yours and you will be ready to go.

Happy days.

Posted in Script | Tagged , , , , | Leave a comment

Why bother with email?

Before I go too far I should point out that this is not a discussion about whether I believe email has had it’s day as a form of modern communication, but more about why people bother to have email addresses when they clearly don’t know much about them, namely businesses. Quite often small businesses. And when I say ‘don’t know much about them’, well sometimes I’m not sure they know they even have them.

It started last year when my wife and I were planning to get married. I like to say my wife and I, not because we’re married (which is great of course) but more because in this context it makes it sound like I had something to do with the planning of our wedding. I didn’t really. And that’s a good thing. My wife however was busy organising our venue, flowers, music, table decorations, other stuff…and because there were so many people/small companies to deal with, and they were about 250 miles from where we live, email would, and should have been one of the easiest forms of communications. Especially as the majority of the small companies in question had websites, and therefore contact pages with email.

My wife started become rather frustrated quite quickly though with the lack of response she received when emailing said small companies. She nagged me about it. It then became frustrating for me too. I tried to explain that most of these companies were based in Lincolnshire, which is where I grew up so I know it well. I also know that technologically Lincolnshire is generally lagging about 20 years behind the rest of the UK. Therefore it made sense that these small companies had set up websites (with help I imagine) and also email, but checked it about as regularly as they check the weather forecast for London.

My wife didn’t understand, but reverted back to good old fashioned phone calls and text messages. Just of late though I’ve required the services of a cycle shop, but before I pay out money I require advice on my cycle issue. I haven’t had much time of late and so have had to resort to looking at the local cycle shop’s website and emailing them to ask for advice. I have not been particularly successful. Of all 6 emails I sent out almost a week ago, I’ve had one response. The one response I did receive was a one-liner asking me to pop into the shop. Sometimes companies could be accused of overbearing customer service, this shop most certainly could not be accused of being overbearing, in fact they could not be accused of providing any customer service at all.

So they don’t read their emails either. It’s not just a Lincolnshire thing, I therefore apologise to all from Lincolnshire (including myself) for thinking them lacking in the email department, when clearly it’s a UK-wide thing. Could it even be a global issue.

It does raise a quandary though, why do people bother to put an email option on their website if they don’t bother to either check their email or respond. Has email had it’s day? I’d say not, not just yet, but don’t advertise it if you’re not going to provide that service. There are 6 cycle shops I’ve emailed in this area, none of which are going to get my business. Fortunately I still have a long list of cycle shops to go…

Posted in General Nonsense | Tagged , , , | Leave a comment

It’s been ages!

I haven’t updated my blog for quite some time, which I know must be terribly upsetting for some of you. I have been terribly busy though (which is of course no excuse for the terrible feeling of neglect I’ve caused you), I shall try to summarize some of my latest tales into one tasty entry.

My job has been crazy, but fun all at the same time. I’ve been learning new skills, or at least attempting to learn new skills. It will take time I’m sure, but I am finding with age comes a slower mind and I can no longer pick things up like I used to, fortunately though I keep my mind fairly active so it’s still alive to the idea of learning many new skills and developing accordingly.

I’ve also been writing on another site called Hubpages.com which has been great fun. I’ve only written a few hubs so far as time is always of the essence, but then one of the hubs I wrote was voted Hub of the Day. I received an accolade for this and have to say I was chuffed to bits!

I’ve also been quite quiet in general, since quitting smoking I’ve not been feeling all that well. I’ve written about that as well though, my journey without cigarettes has reached over 3 weeks now and is going well, if you have time you can read about it here. I shall be looking forward to some New Year resolutions to regain my fitness. It’s important for me to get back up and running as much as possible, to get a grip on  my health as well. Quitting smoking is one thing, not gaining weight and getting fit again is just the next step!

But I’m on holiday now, having a great time over Christmas and definitely really looking forward to new year. We have a Casino night for New Years Eve to enjoy and I’m reckoning that it’s going to be a lot of fun!

At some point I’ll do some work on the bathroom walls, but currently I’m enjoying watching some television and playing on the Wii. Oh, and of course we’re planning our wedding for next August. I should have really mentioned that near the top of the list in case my other half reads this post as of course my dear, the wedding is the most important thing to me at the minute.

As always though there are things to do, so I hope all is well for everyone out there and they are enjoying life as much as I am.

Posted in General Nonsense | 2 Comments

It’s all go go go!

I wanted to write about a quantum physics experiment that I love very much, I first read about it years ago but only saw it demonstrated on tv this year. Many thanks to Horizon for that. Unfortunately though I’m in the process of moving house and have therefore packed all things I don’t necessarily require and have therefore packed all my books away. Including all my physics books, so in short (doesn’t feel that short does it?) I don’t have the technical documents available to feel confident to write about it. You’ll be pleased to know.

Instead I shall write about something I do know. Stress. It’s reckoned that moving house is one of the most stressful things in life. Marriage and divorce being up there as well. Although I’d consider my occupation to be fairly high pressure in the scheme of things. So I’m not going to get married just yet, there’s the cost which can’t quite be afforded at the moment, but there’s also my health to think about.

Stress can hit us all in different ways. I like to think I handle stress pretty well, don’t we all? I’ve been struggling though, so what can I do about it? I could start working out again, great stress relief everyone claims, but it’s hard to work out when you’re feeling down. Eating healthily and keeping hydrated is another, this doesn’t bother me so much though. I don’t have a sweet tooth so that’s not a problem.

What else can I do then? I can try to ignore the stress and just enjoy life. That would be the perfect thing to do I believe. But I have a family to care for and worry about, so it’s not quite so easy to just ignore the issues that may or may not rear their ugly head in my path through life. I do try to enjoy myself when I can though. In fact, I may have a go on my PS3 this evening. Shoot some people on COD, great stress relief. How long does it last for though? Till Monday? I need something more permanent.

As you can probably tell this is quite a disorganized ramble. This is what stress is doing to me, it’s all around me all the time and affecting most parts of my life. I’m normally so chilled it’s hard for some people to believe I’m suffering with stress.

Oh well, I guess I’ll crack on with my gaming this evening for an hour. Maybe have a glass of wine. Just chill out for a while. Hope I may win the lottery (of course money doesn’t make one happy but it can help reduce the stress). If anyone has any ideas to share on how to get myself back on track please comment.

Posted in General Nonsense | Tagged , | 3 Comments

Sunny days are here again.

So here it is, the sun is back and shining and everyone is happy. Including me. I love the sun. It’s about this time I like to get out and about and really enjoy myself. Normally. but that was before I started to feel a little older. I’m feeling I’ve been run a little ragged of late and so just sitting in the garden is about all I want to do. Especially after a tiring swimming session (I have been doing some exercise – it’s not complete laziness on my part). Oh, and I was stung by something today at the pool. It’s not an open air pool, but they did have the door open to let air in. Something flew in and sat in my hair, waiting for me to run my fingers through my hair so it could sting me. It was a dastardly plan by the flying bug in question and it worked a treat. Damn it.

The sun caught me out last night though, it’s not the summer yet as I was quickly reminded. I went out on my bike but forgot the sun still goes down quite early (compared to the summer) and was stuck on my way home without lights on my bike. Risky perhaps, but not terribly, and neither was it particularly exciting. But I did enjoy being back on my bike, even if just for a short while.

Exciting news for the week mind. They have new spinning bikes in the gym! How marvellous. I spent half an hour on one the other day. I really enjoyed it. However, I didn’t really enjoy the next couple of days where my thighs felt like someone had replaced the muscle with inflexible metal which would charge up with kinetic energy before occasionally releasing it with some gusto into the soft areas of flesh surrounding it. Generally every time I took a step. Or stood up. Or sat down again. I think I need to tone down my workout somewhat before my next visit. At least till I’ve built up a mild level of fitness rather than fooling myself into thinking I’m still as fit as I was before I started sitting around in the garden just enjoying the sunshine.

The major problem with the sun is that I never feel like I do enough with it. Well, I don’t mean actually doing things with the sun of course, but just general things whilst the sun it out and I’m outside. A twitter friend has gone to a forest for walkabouts today. When I heard this I was quite jealous, I love forests, I could wander round them for hours. Days even. Hell, I could live in a forest. At least until I had to come home for dinner. But as jealous as I could be I realised this feeling was dwarfed by a feeling I get quite a lot. One of slight stupidity, but also of complete ignorance. Where are the forests around here? There must be some somewhere. When I grew up in the countryside I had lots of forests, fields, streams (great for catching sticklebacks and scooping up some frog spawn) and other assorted outdoor areas to run around in. But I’ve moved away from all that and suddenly realise that I don’t have it anymore. There could be one just down the road from me and I wouldn’t realise it. How does that happen? I can blame it on being busy I guess, but I am just making excuses for having lost touch with some of the things I love in life. I think that’s a definite.

Right, screw this sun, I’m going to sit inside on the sofa and google for forests.

Posted in General Nonsense | Tagged , | Leave a comment

Ouchy Ouch Ouch

I’ve been disappointing, I’ve not been mountain biking, I’ve not even been to the gym. Due to some health issues I’ve been working rather hard at trying to get better rather than trying to improve my fitness.

Unfortunately I can’t take the risk of mountain biking at the minute, my legacy with my riding partner is that I’ve crashed out at every location we’ve been riding. We rode in Pangbourne and I hit a fence post. This fence post stopped me from crashing into barbed wire though, so although it damaged my shoulder, I was quite thankful for it.

I recall once in south Wales I was shocked to find myself scared of a corner, the path narrowed to inches wide with a sheer drop at the side. I had to get off my bike to get around it. This upset me as I suddenly feared something which I don’t normally do, was this the onset of age and starting to take care of myself? I pondered over this for the next few weeks till I could go back again and ride that same corner, this time without stopping or slowing, but to use my fear and ride that damn corner. I did! I was so happy that I did I took my eye off the path for a second which was all it took for me to lose my rear on a tree root and career into a tree. There goes my shoulder again.

Later that day was quite a surprise to my friend who came round a corner to find my bike strewn across the path, all he could see of me was my legs poking up out of a bush. My legs still peddling as it all happened so fast I hadn’t quite grasped the realisation that I was currently upside down in a bush instead of riding my bike.

The next day my aforementioned friend came round a corner to find me on my back in a stream. I’d never high-sided a mountain bike before and didn’t ever contemplate it might be possible to, but a combination of loose gravel and over-confidence and I was leaving my bike behind again.

I might wonder why I shouldn’t let my friend ride out front to slow me down so I don’t have so many accidents. But I don’t. This doesn’t work, I tried it in Aston Hill with quite disastrous consequences. He rode to slow into a corner and I had to break too sharply. I ended up crashing out on a steep hill with my leg trapped between frame and handlebar, sliding down the slope and quite unable to get up. My friend had to stop, climb back up and rescue me. I ride in front.

I had a bad day at Aston Hill that day. I took the wrong turn and ended up on the Black route. I bounced my way down it quite successfully but wasn’t prepared for it and neither was my bike. If I’m going to ride a tough, bumpy and bouncy downhill section then I’ll lower my seat quite a lot so it doesn’t continuously whack me in the backside on the way down. As I mentioned I wasn’t prepared for this route, and neither was my poor bottom which had been tenderised on my trip down the hill. A quick drop off a bridge an hour later into some nettles pretty much finished me off. But it can’t finish you off as you still have to finish the course and get back to the car where I was hoping my friend would be patiently waiting for me. Which he was, although slightly aghast at the cuts, bruises, nettle stings and slightly odd walk I appeared to have adopted in his absence.

These are just a few of the incidents I’ve had, my shoulder has taken the brunt of my crashes over the last few years and now requires some attention but I tend to loathe going to the doctors since they ‘missed’ ruptured ligaments in my knee a few years back, instead putting it down to a twist with some nasty swelling. Twice.

So I’ll go to a doctors, soon, I have a short list of things I need to see a doctor for. I’ve been adding them onto my list as they occur which I’m sure they’ll appreciate. But tomorrow things are looking up as I’m going to tackle the gym again. Bring it on!

MTB South Wales

Wheee!

Posted in Attempting to mountain bike | Tagged , | 1 Comment

So where am I now?

Well, my actual location is unimportant, the London area will suffice. I’m asking more as to my current drive towards my goal. My goal being one of absolute fitness in an attempt to not kill myself when I take on my biggest challenge yet.

I’ve been going to the gym, that’s good isn’t it? Working on cardio, core strength and general all round fitness. We now have a cross-trainer at home which I’ve used…erm…well okay, so I haven’t really used it yet. But I’m not going to the gym today so I’m going to force myself onto the cross-trainer whilst I watch a little tv tonight, once I’ve finished everything I need to do for the day. Which is a  lot. This is a problem, I’m not feeling completely motivated, partly due to the fact I’m always tired. I don’t think the weather helps much, but I’m not going to use that as an excuse. My main excuse is going to be that I’m a fat lazy b’stard who’s having trouble getting off the sofa in the evenings except for the odd trip back to the bottle of wine for another glass. I keep the wine in the kitchen, this means I have to walk through the house to get more, I’m sure that counts as exercise.

But I haven’t been out on my bike yet, I need to do some maintenance work on it before I do. New brake pads are required, this is a must given the death defying speeds I reach in that search for the illustrious adrenaline fix required to keep me functioning. A new inner tube for my rear tyre wouldn’t go amiss, the constant pumping does get irritating after a while. And to add insult to injury my left front shock has started to spew some of it’s contents out, I’m assuming new seals are required but I’ve stopped myself from looking at it for fear it may cost me money. Money, the precious metal I’m slightly short of at the minute.

In a nutshell I’m not feeling confident, but at the same time I’m sure this lack of confidence will dissipate once I really get going and start to feel better about myself. Especially once I don’t have to pick my stomach up prior to picking myself up off the sofa. Okay, so maybe it’s not quite that bad, but it feels it. I suddenly have an extra weight hanging off me which I’ve never had before. Maybe that’s tiring me out too? Is it simply that age is catching up with me though? Only time will tell…haha, if only other people found me as amusing as I do.

Anyway, so far, I believe if there was a stewards inquiry it would be decided that I have to work harder. Starting next week. Which is the same thing my boss always tells me, although he isn’t talking about mountain biking and he certainly isn’t talking about next week.

Posted in Attempting to mountain bike | Tagged , | 2 Comments

And this is where it all started…

So I started mountain biking a few years back. I do love it, but it’s as much the adrenaline pumping that keeps me going back for more as much as anything else. I can’t help but rise to a challenge. Which is why I entered a race last year. It would have been my first race, but not any old race, a 24 hour endurance race.

Unfortunately I crashed my car a month or so before the race and couldn’t take part. I was a little gutted to say the least, but I’m level headed and what’s meant to happen is meant to happen. It didn’t happen last year so I was hoping to do it this year, but I moved house last summer, gained weight, lost fitness and am generally way too unfit to make a serious challenge this year. And by serious challenge I’m not considering a serious challenge in the race, but it’s a serious enough challenge for me to take on the race and complete it.

So this is it, next year is the big year. I’m going for it again. And this time I’m hoping to take a few of you on my travels, through my trials and tribulations of training, through my attempt to lose the weight I’ve put on mainly around my stomach area – this could be the hardest challenge since I moved in with my girlfriend who is not only amazing and supports me in all my crazy adventures but is also a fine cook, pandering to my palate in wonderous and delightful ways, and through my rise back to a general level of fitness which satisfies me.

So far I’ve not done much about this, before Xmas I did start back at the gym which is a beginning, I’m still smoking, I still need a new bike and haven’t been out on my current one for about 6 months or more. Now although the race won’t be till approx. next May I’m already slightly worried at my lack of training, the fact I’m still smoking and also when I attempted to perform a plank exercise last week I could feel my body bowing in the middle from the sheer lack of muscle and weight of my stomach.

Enough of my complaining about my body though, it’s about time I did something about it. Starting tomorrow. Maybe.

Posted in Attempting to mountain bike | Tagged , | 1 Comment