Karl’s Blog

10 Nov 17 SQL Script to export all data in a database as INSERT statements

This script will export all data from a Microsoft SQL Server database as a script of INSERT statements.



SET @SQL = null

', '') + 'SELECT ''' + t.TABLE_SCHEMA + ''' AS TABLE_SCHEMA, ''' + t.TABLE_NAME + ''' AS TABLE_NAME, COUNT(*) AS records FROM [' + t.TABLE_SCHEMA + '].[' + t.table_name + ']'
FROM information_schema.tables as t
WHERE t.table_type = 'BASE TABLE'
  AND t.table_name NOT IN ('dtproperties', 'DB_VERSION', 'SCHEMA_UPDATER_VERSIONING')

CREATE TABLE #tmp_tables (

' + @SQL + '
) t
WHERE records > 0')

DECLARE tables_cursor cursor 

OPEN tables_cursor

CREATE TABLE #tmp_statement (


DECLARE @tableSchema VARCHAR(50)
  , @tableName VARCHAR(50)
  , @identityColumn VARCHAR(50)

FETCH NEXT FROM tables_cursor
INTO @tableSchema, @tableName



  SET @selectSQL = NULL

  SELECT @SQL = COALESCE(@SQL + ', ', '') + '[' + c.COLUMN_NAME +']'
    , @selectSQL = COALESCE(@selectSQL + ', ', ' + ''SELECT ') + 
        WHEN c.DATA_TYPE IN ('char', 'varchar', 'text', 'nchar', 'nvarchar', 'ntext') THEN ''' + COALESCE('''''''' + REPLACE([' + c.COLUMN_NAME +'], '''''''', '''''''''''') + '''''''', ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('bigint', 'numeric', 'bit', 'smallint', 'decimal', 'smallmoney', 'int', 'tinyint', 'money') THEN ''' + COALESCE(CONVERT(VARCHAR, [' + c.COLUMN_NAME +']), ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('float', 'real') THEN ''' + COALESCE(CONVERT(VARCHAR(50), [' + c.COLUMN_NAME +'], 3), ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('date', 'datetimeoffset', 'datetime2', 'smalldatetime', 'datetime', 'time') THEN ''' + COALESCE('''''''' + CONVERT(VARCHAR(36), [' + c.COLUMN_NAME +']) + '''''''', ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('uniqueidentifier') THEN ''' + COALESCE('''''''' + CONVERT(VARCHAR(36), [' + c.COLUMN_NAME +']) + '''''''', ''NULL'') + '''
        WHEN c.DATA_TYPE IN ('binary', 'varbinary', 'image') THEN ''' + COALESCE(CONVERT(VARCHAR, [' + c.COLUMN_NAME +']), ''NULL'') + '''
        ELSE 'ERROR'
  FROM information_schema.columns c
  WHERE c.table_name = @tableName

  INSERT INTO #tmp_statement ([TABLE_NAME], [STATEMENT])
  SELECT @tableName, 'INSERT INTO [' + @tableSchema + '].[' + @tableName + '] (' + @SQL + ')

'', '''')' + @selectSQL + ''' FROM [' + @tableName +']

  SET [statement] = [statement] + @SQL
  FROM #tmp_statement s
  WHERE s.TABLE_NAME = ''' + @tableName + '''


  SET @identityColumn = NULL
  SELECT @identityColumn = c.COLUMN_NAME FROM information_schema.columns c WHERE TABLE_SCHEMA = @tableSchema AND TABLE_NAME = @tableName AND COLUMNPROPERTY(object_id('[' + TABLE_SCHEMA + '].[' + TABLE_NAME + ']'), COLUMN_NAME, 'IsIdentity') = 1

  IF (@identityColumn IS NOT NULL)
    PRINT 'SET IDENTITY_INSERT [' + @tableSchema + '].[' + @tableName + '] ON'

  SELECT @SQL = [STATEMENT] FROM #tmp_statement WHERE TABLE_NAME = @tableName

  IF (@identityColumn IS NOT NULL)
    PRINT 'SET IDENTITY_INSERT [' + @tableSchema + '].[' + @tableName + '] OFF'
    PRINT 'SELECT @maxId = MAX([' + @identityColumn + ']) FROM [' + @tableSchema + '].[' + @tableName + ']'
    PRINT 'DBCC CHECKIDENT ([' + @tableName + '], RESEED, @maxId)'

  PRINT ''

  FETCH NEXT FROM tables_cursor
  INTO @tableSchema, @tableName

CLOSE tables_cursor
DEALLOCATE tables_cursor

DROP TABLE #tmp_statement
DROP TABLE #tmp_tables


09 May 12 How to Create a RAM Disk in Mac OSX

This is a useful example of how to create a RAM disk in Mac OSX. The number 1165430 represents the number of sectors to allocate; each sector contains 512 bytes. The example below will create a RAM disk named ramdisk with 569.06 MB of space.

diskutil erasevolume HFS+ "ramdisk" `hdiutil attach -nomount ram://1165430`

25 Apr 12 How to Hard Reset Samsung 10.1 Tablet

I have the developer version of the tablet given to the attendees of GoogleIO 2011, if you have a comercial version of the tablet these instructions probably do not apply. The model number I’m working with is GT-P7510.

In order to reset the table you will need to download the android sdk; be sure to include the optional platform tools package. The platform package will install the fastboot utility we will use later on.

Step 1 – Place the Tablet into fastboot mode

  1. Power off the tablet
  2. Hold down the power and volume down (left) buttons
  3. When the USB and Download icons appear, choose the USB icon. Volume down button will toogle selection and the volume up will activate the mode.

Your tablet will now be in the fastboot mode.

Step 2 – Factory Reset

  1. Open a terminal
  2. Change directory to android sdk installation folder/platform-tools
  3. Execute fastboot -w

Should only take a few seconds and you’re done.

Tags: , , ,

10 Apr 12 How to reindex every table with SQL Server

A useful script I’ve used to reindex every table in an SQL Server database.

EXEC sp_MSforeachtable 'DBCC DBREINDEX (''?'', '' '', 80)'
EXEC sp_updatestats

03 Feb 11 CRTC Approved Internet Usage Based Billing in Canada

A friend of mine wrote on his facebook page “I don’t understand what is so fundamentally appalling about usage based billing that the Canadian Government would step in to overturn the CRTC decision.”

This got me to thinking about the issue because I am disgusted with the CRTC’s decision but I don’t think there is anything fundamentally wrong with usage based billing.  I mean it works for other things like electricity, natural gas, water and pretty much anything you can by on the free market.  So what is so appalling?

For me I think my objection is with the CRTC directly.  This is another example of the CRTC using its power to protect Canadian markets and make them more lucrative for the select companies capable of operating within our borders with little regard for how this impacts each Canadian.  The recent sale of wireless spectrum in Canada was supposed to increase competition and result in lower cost of wireless communication in Canada.  Today Canadians are still paying too much, compare your plan with that of the average American or European.  Now with this decision are we to believe the average Canadian will pay less for internet.

The normal forces that are meant to control price relies on the conflict between supply and demand.  In a free and open market supply gets created by existing suppliers creating more product or new entrants.  The high infrastructure costs and ownership requirements effectively closes the Canadian market to new entrants.  Since price is inversely proportional to supply and new entrants are highly unlikely it is in the best interest of the large communications companies to slowly introduce supply at a pace that does not cause civil unrest.  Choice is even further limited because the major providers have colluded and geographically divided the country.

The demand side of the equation is simple, it is going to increase.  I’ve been using internet since it was first made available in my neighborhood, if memory serves this was 1995.  I know my own personal experience has been of increase internet use year over year to the point now where I personally consider it a part of my critical infrastructure; like heat, water, electricity, etc.  I have also witnessed increased dependence on it in my job, at education facilities and in my personal life.  I believe my personal experience is consistent with the global trend and I hope it continues into the future.

What does this mean.  Moderate increases to supply + increasing demand = higher price.

One could argue that the conditions that I’m complaining about exist within other industries like electricity and natural gas distribution in Canada.  I would agree but I believe there is one critical difference, the price is regulated both in terms of the per unit cost of the resource and the administrative costs that may be added to the bill.  With a regulated price there is one way for a company to fullfil its desire to make more money, get more customers.  The factors that would drive customers to one provider over the other would be quality things like: uptime, bandwidth, quality of service, etc.  So with a regulated price we should see internet providers that are more reliable, provide better service and like electricity providers the companies would continue to earn revenue.

The solutions I’ve considered are:

  1. Create a government regulatory body to regulate (supply and price) the industry like is done for Electricity, Gas, and the like.  Could expand the responsibilities of the CRTC but I think the organization is relatively ineffective at protecting the consumers interest.
  2. Create a publicly funded communications corporation to provide the minimum standard communication infrastructure.  It would be up to private industry to invent compelling reasons for individuals to use there service.
  3. Disband the CRTC and open our markets to global investment

06 Dec 08 Home Theatre PC – Part 2 The Software


Part 1 of this article discusses the hardware selection in my home theatre PC.  This article will focus on the software that enables the system to watch, pause, record live TV and to steam video over a wireless-n network served from UPnP media server. The major software components of interest are the operating system and media center.

The Operating System

I have a tendency to avoid commercial software as I have found that the freely available open source solutions tend to be as good and are more responsive to their deficiencies.  However, for this build I have gone against my natural instinct and am using Microsoft Windows XP Media Center Edition.  The primary reason for this was I was having driver problems with my wireless-n card in the alternate OS choices.

The installation and configuration of Microsoft Windows XP Media Center Edition was straight forward.  The installation process was the same as any windows OS installation and configuring the Media center utilize the TV Tuner, IR Reciever, IR Blaster was straight forward.  After completing the on screen instructions I had a current guide and was able to pause, rewind, and watch live TV.

Media Centre

I haven’t found an all in one software solution that does everything I wanted in my HTPC, so I’ve selected Microsoft Windows XP Media Center Edition and XBMC (XBox Media Center).  The installation of XBox Media Center was a little less obvious.  After downloading the windows edition and attempting to run the installation programs and error dialog popped up indicating that the installation program couldn’t locate MSVCR71.dll.  I was able to resolve this by downloading the dll and copying it to the windows\system folder.  In general I don’t recommend downloading random files form the internet and installing them into your system folder but as yet I haven’t detected any adverse effects.

I’ve read in the XBMC forums that they are aware of this issue and didn’t fix it for the atlantis release because they were not sure what the legal ramifications were to shipping the dll.  They indicated that Microsoft has reorganized its c libraries, responsible for providing some of the basic c functions like printf.  I hope that the folks at XBMC can resolve this issue, initially I almost abandoned the software because of the faulty installation program.

After working through the installation process, I was able to stream high quality video over my wireless-n network.  The video format I was streaming was an avi container with xvid compressed video and AC3 5.1 sound.  The initial buffering was a little longer then I was expecting but reasonable and the video streamed reasonably well.  The one issue I have with the system is whenever its in an erroneous state: slow network, unable to find an audio device the video plays in ultra high speed.  During normal video playback you can adjust the AV sync in real time and the list of supported codecs is sufficient for my needs.

Once XBox Media Center starts I’m able to navigate the menus with the Media Center Remote but haven’t figured out how to start XBMC using just the remote.  Most of the articles I’ve read online seem to disable the existing support for Windows Media Center.


An alternative configuration I was considering and would work equally well was a Linux OS with MythTV and XBox Media CenterXBMC is available for Ubuntu and I was able to get it to install on Gentoo as well.  For Gentoo you can use the information in the bug to create a custom ebuild, until the ebuild is added to portage.  The reason I abandoned the Linux configurations was because the wireless network drivers were not establishing a reliable network connection and I felt the time associated with getting lirc, sound and the graphics card working correctly was not worth it.

The Media Server

My media server is a Gentoo machine running MythTV and TwonkyVision UPnP MediaServer, you don’t need both and as a media server they both have similar features.  MythTV does have more features but these are only important if you plan on using the myth frontend.  Should MythTV add UPnP support to the MythVideo plugin then I would have selected this as the all in one software solution for the HTPC and the media server.  I could have used a network share and used MythTV as is but I’ve had problems with the resilience of watching video from a network share.


For right now the ease of installation of Windows Media Center and features available with XBMC allowed me buld the system with features I needed.  I’ll keep an eye on MythTV as the runner up I think there is a lot of potential there.  Windows Media Center also had the advantage of having free guide information, MythTV gets its guide information from Schedules Direct a service that you have to pay 20 dollars a year for.

Tags: , , , , , , , , , , , , , , , , , , , , ,

25 Nov 08 Home Theatre PC – Part 1 The Hardware


This article was written to document my attempt at creating a home theatre PC (HTPC).  I’ll be reviewing the equipment, software and alternative configurations I considered while designing the system.  I’ve decided to break up this article into a series of three articles: Part One describes the hardware I selected, Part 2 describes the software, Part 3 Alternative Systems.

To drive the selection process for my system I used the following list of requirements:

  1. Aesthetically pleasing and have the appearance of standard audio video equipment
  2. Stream  DVD quality videos from a UPnP media server
  3. Watch, pause and rewind rewind live television
  4. PVR functions, schedule recordings
  5. Cost that was similar to a PVR set top box

The Case

The case selection was primarily driven by the appearance of the system and I wanted a case that included an infrared reciever and remote.  The THERMALTAKE Mozart SX (VC7001SNS) had all of the things I was looking and was reasonably priced, unfortunatly there were a few issues with this case I wasn’t expecting.  The placement of the PCI ports on the included riser card were too close to the motherboard and the IR reciever is not reliable and causes erroneous behaviour in Microsoft WIndows Media Center Edition.

The riser card provides little space between the motherboard and the first slot, I was barely able to insert the D-Link DWA-542 Rangebooster N Desktop Adapter.  The network adapter is compact and there is still only enough space for a ribbon cable to pass between the card and PCI slots on the motherboard.  I’m hoping that heat doesn’t become an issue.

The case has an integrated digital display that include hot keys, infrared receiver and remote control.  I found the performance of the remote control and infrared receiver to be abysmal.  The computer would only respond to commands from the remote sporadically and causing undesirable behaviour with other applications.  The computer would start beeping as though the internal key buffer had filled up and would not stop beeping until the IMON service was shut down.  At one point I was in a dialog and the computer was writing the letter ‘B’ repeatedly into a prompt where I was to enter configuration information.  Additionally, the IR system caused Microsoft media center to flicker and degraded its response to mouse movements and clicks; again stopping the IMON service was the solution.

I strongly recommend not installing the supplied software.  Removing the software had the consequence that all the feature associated with the media labs kit will be unavailable.  Upgrading the supplied software to the latest available version had no benifit.  I still like the case but if I were making the choice over again I would save my 70 dollars and buy the version without the Media Labs kit, THERMALTAKE Media Labs SX (VC7000SNS).

TV Tuner

The next component of the system I selected was the Hauppauge WinTV-PVR 150 Media Center Kit, this kit came with a TV tuner card, IR receiver, IR blaster and a Microsoft Media Center certified remote control.  The TV tuner card will be used in combination with a set top box, used to decode the encrypted signal from my cable provider.  The standard cable portion of the signal could be decoded by the TV tuner card directly but the digital signal requires the use of a set top box.  I was able to use the provided IR card to allow the HTPC to control the set top box to enable the system to watch live TV, pause, rewind and record both the standard cable and digital channels.  Installation and setup was a breeze.


The motherboard Gigabyte GA-73PVM-S2H was selected because of its large number of features.  The HDMI video, optical sound, nvidia video card, and Gigabit Ethernet port are the components that interested me while designing this system.

Everything Else

The remainder of the system was selected on the basis of cost, I wanted to build a system with a cost around 800 dollars.

  • SPI 400 watt power supply with 120 mm fan
  • Corsair XMS2 2GB DDR2-800 CL5
  • Intel Pentium Dual Core E2160 1MB
  • Samsung SH-S223F 22x DVD-RW OEM

I didn’t require a large hard drive because I didn’t intend to house a lot of media on the system.  Any media that I was going to keep for any length of time would be housed on a server and made available through a UPnP  media server.

Overall I’m happy with the setup I’ve selected, even with dissappointing performance of the Media Labs VFD the system is working as expected.  In the next article I describe the software I’ve selected.

Tags: , , , , , , , , , , , , , , , , , , , , , , , , ,

16 Aug 08 Globalfest 2008

GlobalFest 2008, Opening night of the festival and fireworks competition. This was an excellent opportunity for me to practice my fireworks photography.

My camera is a Canon EOS Digital Rebel and the lens I used was a Canon EF 28-300mm 1:3.5-5.6 L IS USM

The photos in this article were shot with an aperture setting between f/9.0 to f/12.6 and an ISO setting of 100. Unfortunatly my camera did not properly record the exposure but I was shooting between 1 second and 4 seconds with the majority of the shots at 2.5 seconds.

I have not done any post processing on these photo, they are as shot.

Tags: , , , , , , , ,