ieatpenguin

July 8, 2010

MSSQL – How To Output To A File

Filed under: SQL — Russell @ 8:15 am

There is a very, very simple one liner within MySQL to dump the results of a query into a text file. Nothing so simple exists within MSSQL, but there are fairly easy workarounds. Using BCP, one can use the xp_cmdshell to pass the results into the desired file.

use DatabaseName
go

declare @FileName varchar(50)
declare @bcpCommand varchar(2000)

set @FileName = REPLACE(‘c:\temp\bcp\postcodes_’+CONVERT(char(8),GETDATE(),1)+’.csv’,'/’,'-’)

set @bcpCommand = ‘bcp “select left(postcode,4), count(*) from DatabaseName..TableName where customersequence = 0 group by left(postcode,4) order by count(*) desc” queryout “‘
set @bcpCommand = @bcpCommand + @FileName + ‘” -U username -P password -c’

EXEC master..xp_cmdshell @bcpCommand

This dumps a load of postcode information, based on usage, into a csv file in c:\temp\bcp. What the query is doesn’t really matter, just the bcp commands to provide us with the results in a file of our choosing (.csv in this case).

The @FileName command simply appends the current date to the file in order to keep track of queries.

July 7, 2010

Into outfile

Filed under: SQL,Windows — Russell @ 2:29 pm

Why oh why oh why is there nothing this simple within MSSQL? Sigh.

February 27, 2010

Mounting a network drive in Linux (Ubuntu)

Filed under: Linux,Software — Russell @ 12:54 pm

This is a very simple thing to do, it isn’t however as simple as one might think if you are coming from a Windows background, and are used to mapping a network drive from the Tools menu. As a note this is a guide for Ubuntu, although I have it working fine on both Ubuntu and Fedora, use the appropriate package manager/command line for Fed and the rest is the same.

First, we need to make sure that samba is installed:

sudo apt-get install smbfs

Next, we need to make a directory to mount the drive too. As an example, I’ve just reinstalled my Ubuntu (and Fedora) distribution, and so want to map the music drive on my server. I chose /media/ as the logical place to stick my network drives:

sudo mkdir /media/music

Next we need to tell the file system table where the drives are, and where to mount them. We also need to include our login credentials (will cover this later).

gksudo gedit /etc/fstab

Scroll to the bottom of the file and add the following:

#Mounting Network Drives
//SERVER/SHARE-NAME /MOUNT-POINT smbfs credentials=/credentials-file-location

To make the above make a bit more sense, here is my configuration:

//192.168.1.50/Music /media/music smbfs credentials=/home/russell/credentials.smbcredentials
//192.168.1.50/Videos /media/videos smbfs credentials=/home/russell/credentials.smbcredentials
//192.168.1.50/Software /media/software smbfs credentials=/home/russell/credentials.smbcredentials

What this will do is to check within the credentials file (more on this at the bottom) your username and password for your server (I am running a Windows Home Server as an example).

Next, we need to make the filesystem mount the drive, which we do simply with:

sudo mount -a

Finally, we need to make that credentials file. Simply navigate to your chosen directory (I stuck it in my /home/russell directory for ease), create a new file with the following information:

username=username
password=password

And save it with the same filename you gave the /fstab/. Thats it.

February 12, 2010

Rsync and Bash

Filed under: Linux,Software — Russell @ 10:02 am

Have had an interesting morning delving into Bash, on MSN with Jasper, which is not something I get to do often enough.

The upshot was a nice and fairly sophisticated rsync script to backup some files, which I won’t paste here as it’s Jaspers baby.

However, I did think up a [much] simpler script to enable a quick and dirty backup of any particular folder to any particular media.

#!/bin/bash
sudo rsync -av –progress –delete –log-file=/dir/$(date +%Y%m%d)_rsync.log /dir /media/dirBackup

You can of course exclude certain files from the backup with:

–exclude “/dir/.jpg”

Naturally, no one wants to type it out everytime and so lets make it into an executable script:

sudo chmod +x /path/rsync-backup.sh

So you now have an executable script you can call whenever you want, or you can of course create a cron job and have it run automatically.


Here’s a slightly more sophisticated backup script that revolves on a weekly basis, and then clears out any backups that are older then this.

#!/bin/sh
# directory to backup
BDIR=/home/$USER

# excludes file
EXCLUDES=$HOME/cron/excludes

# name of the backup machine
BSERVER=server

# password on the backup server
export RSYNC_PASSWORD=

# lets get down to it
BACKUPDIR=`date +%A`
OPTS=”–force –ignore-errors –delete-excluded –exclude-from=$EXCLUDES
–delete –backup –backup-dir=/$BACKUPDIR -a”

export PATH=$PATH:/bin:/usr/bin:/usr/local/bin

# the following line clears the last weeks incremental directory
[ -d $HOME/emptydir ] || mkdir $HOME/emptydir
rsync –delete -a $HOME/emptydir/ $BSERVER::$USER/$BACKUPDIR/
rmdir $HOME/emptydir

# now the actual transfer
rsync $OPTS $BDIR $BSERVER::$USER/current

October 21, 2009

Windows Home Server

Filed under: Software,Windows — Russell @ 9:58 am

Well I finally got round to installing this on my custom built server, and I’ll tell you something, I quite like it. It’s easy to set up, easy to configure and easy to use. No worrying about what raid to use, just automatic duplication if desired. And automatic backups. Just whack in a few drives and away she goes.

December 11, 2008

Microsoft Haikus

Filed under: Random,Software,Windows — Russell @ 6:03 pm

Not mine, but amused me enough to post.

The Web site you seek

Cannot be located, but

Countless more exist.

*

Chaos reigns within.

Reflect, repent, and reboot.

Order shall return.

*

Windows NT crashed.

I am the Blue Screen of Death.

No one hears your screams.

*

Stay the patient course.

Of little worth is your ire.

The network is down.

*

A crash reduces

Your expensive computer

To a simple stone.

*

Serious error.

All shortcuts have disappeared.

Screen. Mind. Both are blank.

November 18, 2008

Defragging SQL indexes

Filed under: Software,Windows — Russell @ 11:06 am

Occasionally it will be necessary to defrag table indeces if you run SQL server. A useful little script.

USE dbname –Enter the name of the database you want to reindex

DECLARE @TblName varchar(255)
Declare @Indexname varchar(50)

DECLARE TableCursor CURSOR FOR
SELECT table_name FROM information_schema.tables
WHERE table_type = ‘base table’

OPEN TableCursor

FETCH NEXT FROM TableCursor INTO @TblName
WHILE @@FETCH_STATUS = 0
BEGIN

DECLARE INdexCursor CURSOR FOR
SELECT     i.name AS IndexName
FROM         sysobjects o, sysindexes i
WHERE   (o.id = i.id and o.name = @tblName) AND (i.status = 18450 OR   i.status = 2097152)

OPEN IndexCursor

FETCH NEXT FROM INdexCursor INTO @IndexName
WHILE @@FETCH_STATUS = 0
BEGIN

DBCC INDEXDEFRAG (dbname, @tblname, @Indexname)

FETCH NEXT FROM INDexcursor INTO @Indexname
END

CLOSE IndexCursor

DEALLOCATE IndexCursor

FETCH NEXT FROM TableCursor INTO @TblName
END

CLOSE TableCursor

DEALLOCATE TableCursor

October 9, 2008

A long read, but worth it.

Filed under: Linux,Random,Software — Russell @ 10:49 am

Happy 17th birthday Linux.

In the Beginning was the Command Line by Neal Stephenson.

October 8, 2008

Mozy Online – Free, secure storage

Filed under: OSX,Software,Windows — Russell @ 1:02 pm

Mozy online is a free storage depository, that uses a lightweight program to sync files on your computer.

It’s easy to configure and runs pretty much by itself, and comes with 2GB. If you fancy using it, use my referral code and we both get an extra 256MB of storage.

October 7, 2008

Disabling MSN & Live Messenger

Filed under: Software,Windows — Russell @ 2:00 pm

There are quite a few guides on how to disable this viral little program. Sadly blocking it’s default port rarely works, because doing so causes the program to switch to port 80, which for most would mean blocking http traffic too.

Several ways to do this on the internet involve editing the registry, this is something that doesn’t appear to work any more.

The only way I’ve found that works is to edit the computers group policy (start > run > gpedit.msc). Under Administrative Templates and Windows components, you’ll find Windows Messenger. Simply set “Do not allow windows messenger to be run” to enabled.
Thats it.

Update: This doesn’t work for Live Messenger, one method I have found that uninstalls WLM completely is to run a command prompt (as administrator), and simply type: msiexec /x {B1403D7D-C725-4858-AACC-7E5FA2D72859}.

Older Posts »

Powered by WordPress